October 2009 - Posts

Script out Procedures to seperate files

In this post script-out-procedures-and-functions-part-2, I showed how to script out the procedures in a single file

Here is another way to do the same but different file for each procedure. The file name will be the name of the procedure.

declare @sps table(proc_name varchar(100),texts varchar(8000))

insert into @sps
select name,'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')

create table scripts(sps varchar(8000))

declare @texts varchar(1000)
declare @file_name varchar(100)
declare @sql varchar(1000)

select
@texts=min(texts)from @sps

while @texts>''
Begin
        select
@file_name=proc_name from @sps where texts=@texts
        EXEC('insert into scripts(sps) EXEC '+ @texts) insert into scripts(sps) select 'GO'
        select @texts=min(texts)from @sps where texts>@texts 
        set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c' 
        EXEC master..xp_cmdshell @sql
        truncate table scripts
End

drop table scripts

 

Posted by Madhivanan with 3 comment(s)

Finding out problematic data in Bulk Insert data truncation error

Suppose you want to import data from a text file to a table, one of the options is to use Bulk Insert command
It allows to import data to the table, though one/more columns don't have enough size to get the maximum length of data from text file.It throws errors about data truncation with line numbers.

What if you want to find out the rows from the text file that are not imported

 Here is a way

Consider the following example

--Create a testing table

create table bulk_test
(
test_no     int,
test_name varchar(10)
)

GO

--The text file has the following data

id,    name
23,    test
8,      kjhasd
87,    sdf
23,    testing column
99,    ujh
45,    this is for testing only


--Bulk import query

BULK INSERT bulk_test
FROM 'e:\test.txt'
WITH
(
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
firstrow=2
)

--Error that is thrown

Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 5, column 2 (test_name
).
Msg 4863, Level 16, State 1,
Line 2
Bulk load data conversion error (truncation) for row 7, column 2 (test_name).

--Finding out the data that causes the error

select
        t1
.*
from 
        openrowset('Microsoft.Jet.OLEDB.4.0','text;HDR=YES;FMT=FixedLength;Database=e:\', test#txt) as t1 
        left join bulk_test as t2 on t1.id=t2.test_no and t1.name=t2.test_name
where t2.test_no is null

Removing tags from a string

In http://ask.sqlteam.com, a question was asked about removing the comments from the HTML template

The comments have the following pattern

{/*}comment{*/}

The comments can be nested too

The task is to remove all the comments

Here is my solution

declare @string varchar(8000) ,@data varchar(8000)

select @string = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'

select @string=replace(replace(@string, '{/*}','~{/*}'),'{*/}','~{*/}')

declare @t table(id int identity(1,1),data varchar(8000))

insert into @t(data)
select
 
        substring(@string, n, charindex('~', @string + '~', n ) - n
)
from

(
 
        select number as n from master..spt_values where type='p'

)
numbers
where
 
        substring( '~' + @string, n, 1 ) = '~'


--Method 1

set @string='' select @string =@string+data from
(
    select data from @t where id=

    union all
    select
substring(data,5,len(data)) from
@t 
    where (data like '{*/}%' and id%2=1
)
)
as t

select @string

--Method 2

select @string =''

select
        @string
=@string +substring(t2.data,5,len(t2.data))
from 
        @t
as t1 inner join @t as t2 on t1.id+1=t2.id
where 
        t2
.data like '{*/}%' and t2.id%2=1 and t2.data<>'{*/}'

select @string

Maximum length of each column

 

How do I find out the maximum length of each column in a table?

Here is the answer

declare @sql varchar(8000), @table sysname

select @table='your_table', @sql=''

select
        @sql
=@sql+'select '''+@table+''' as table_name,'''+column_name+''' as column_name, 
                                max(len('
+column_name+')) as column_length from '+@table+' union all '
from 
       
information_schema.columns where table_name=''+@table+''

set @sql=left(@sql,len(@sql)-9)

exec(@sql)

Posted by Madhivanan with 1 comment(s)