Madhivanan

Export to Excel - data length exceeds 255 characters

If you use Data Transaformation Services to send data to EXCEL, you may get error about the size of the cell if length of data exceeds 255 characters. This KB from microsoft explains how to solve it

http://support.microsoft.com/default.aspx?scid=kb;en-us;281517

Quirky update in sql server

declare @temp table
(
product_name varchar(100),
Value int,
sequence int
)

insert into @temp values('product1' , 25 , 0)
insert into @temp values('product2' , 25 , 0)
insert into @temp values('product3' , 100 , 0)
insert into @temp values('product4' , 100 , 0)
insert into @temp values('product5' , 100 , 0)
insert into @temp values('product6' , 25 , 0)
insert into @temp values('product7' , 25 , 0)
insert into @temp values('product8' , 26 , 0)

declare @value int,@sequence int, @runningtotal int,@product_name varchar(100)

set @sequence=0

update @temp
set @sequence=sequence =@sequence+1
select * from @temp

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence else @sequence+1 end,@value=value

select * from @temp

set @sequence=0

update @temp
set sequence=@sequence ,
@sequence=case when @value=value then @sequence+1 else 1 end,@value=value

select * from @temp

set @sequence=0

update @temp
set @sequence=sequence =@sequence+value
select * from @temp

declare @value int,@sequence varchar(100), @product_name varchar(100)

declare @temp table
(
product_name varchar(100),
Value int,
sequence varchar(100)
)

set @product_name=''

select value,max(sequence) as sequence from @temp
group by value

The term "Quirky update" is referred in these articles
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Removing unwanted characters

You can use Replace function. Suppose you want to remove the character ^ from the string

declare @str varchar(100)
set @str='test^ string'
select @str=replace(@str,'^','')

But what if you have lot of characters to be removed?
You can have a seperate table that has the set of characters to be removed and use a function

create table #data (data varchar(100))

insert #data
select 'tes^@&t %stri)-n!g' data union all
select '))aaer***********)' union all
select '&^&&hsdf()' union all
select 'jj&wk' union all
select ')hw*pa' union all
select 'No&@#$53*24,Mai()$n R--!oad'

create table clean_chars (char_id int identity(1,1),chars char(1))

create function clean_data
(
@data varchar(100)
)
returns varchar(1000)

        while @char_id is not null
        begin
                select @clean_data=replace(@clean_data,chars,'') from clean_chars where char_id=@char_id
                select @char_id= min(char_id) from clean_chars where char_id>@char_id
        end

return @clean_data

select data,dbo.clean_data(d.data) as clean_data from #data as d

How to find size of backup files in a folder?

I was asked to findout total size of all the backup files in the folder
As I didnt have access to view the folder of the server, I used this.
I hope it may be helpful

create table #t(files varchar(1000))

Insert into #t
EXEC master..xp_cmdshell 'dir e:\backup'

select
        substring(size_files,charindex(' ',size_files)+1 ,len(size_files))as backup_file,
        cast(substring(size_files,1,charindex(' ',size_files)-1) as money)/1024/1024 as file_size_MB
from
(
        select files,ltrim(substring(files,patindex('%[ AP ]M%',files)+2,len(files))) as size_files 
        from #t where files like '%bak'
) as t

drop table #t

Import from Text file - Single column to multiple columns

One of the users asked this question in the SQL forum

I have data in the text as follows

AccountNo: 00234543
AccountName: Kickser
City: Chicago
Country:USA
AccountNo: 00234543
AccountName: Annis
City: Seatle
Country:USA
AccountNo: 12234456
AccountName: pargenezzer
City: NYC
Country:USA
...
....
.....
AccountNo: 12233477
AccountName: PILIP
City: Edison
Country:USA

How do I store data to SQL Server table that has columns AccountNo,AccountName,City which is normalised?

Here is one of the methods that does it

create table test_data(data varchar(1000),value varchar(1000))

BULK INSERT test_data FROM 'file_path'  WITH
(
FIELDTERMINATOR =':',
ROWTERMINATOR ='\n'
)

alter table test_Data add id int identity(1,1)

declare @count int,@sql varchar(8000),@columns varchar(1000),@i int

select @count=count(distinct data) from test_Data
select @sql='', @i=1

while @i<=@count
Begin
        select @sql=@sql+'select id,value as value'+cast(@i as varchar(10))+' from test_Data where id%'+cast(@count as 
        varchar(2))+'='+cast(case when @i=@count then 0 else @i end as varchar(2))+') as t'+cast(@i as varchar(2))+
        case when @i>1 then ' on t1.id+'+cast(@i-1 as varchar(2))+'='+'t'+cast(@i as varchar(2))+'.id' else '' end+
        case when @i<>@count then ' inner join (' else '' end,
        @columns=coalesce(@columns+',','')+'t'+cast(@i as varchar(2))+'.value'+cast(@i as varchar(10))

        select @i=@i+1

('+@sql

exec(@sql)

Now the result can be copied to another normalised table

Tables with no Primary and Foreign keys

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity.

What if you want to know list of tables that have no primary and foreign keys?

Here is the query

select
        table_name
from 
        INFORMATION_SCHEMA.TABLES
where 
        objectproperty(object_id(table_name),'TableHasForeignKey')=0 AND 
        objectproperty(object_id(table_name),'TableHasPrimaryKey')=0 AND 
        table_type='BASE TABLE'
order by 
        table_name

Last Backup/Restore date of database

If you want to know the last Backup/Restore date of database, use the following

--Last Backup date of database

--Method 1

In Management Studio,Expand database,Right Click on the database and click properties
Refer Last Batabase Backup which is availalbe under General

--Method 2

select database_name,max(backup_finish_date) as backup_finish_date from msdb..backupset
where database_name='your_database'
group by database_name

select destination_database_name,max(restore_date) as restore_date from msdb..restorehistory
where destination_database_name='your_database'
group by destination_database_name

Table level Backup/Restore

It may be very useful if we have feature to take backup or restore a table

I have posted my suggestion here

Table level Backup/Restore

What do you think of it?

Filling random passwords - Set based approach

set @password=''

select @password=@password+char(n) from
(
        select top 10000 t1.number as n from master..spt_values t1 cross join master..spt_values as t2 
        where t1.type='p' and t1.number between 48 and 122
        order by newid()
) as t

set @number=0

declare @t table(i int, pwd varchar(8))

insert into @t(i)
select top 100 number from master..spt_values
where type='p' and number>0

update @t
set pwd=substring(@password,@number*8,8),@number=@number+1

select * from @t

Behavior Changes to Database Engine Features in SQL Server 2008

Updated Point on BOL is available at http://msdn.microsoft.com/en-us/library/ms143359.aspx

Which columns uniquely identify a row?

EXEC sp_special_columns table_name

Beware of the Usage of SET ROWCOUNT

This is used to set the number of rows affected for DML

SQL_VARIANT datatype and behaviour changes

When you use sql_variant datatype in sql server 2000, and add data to a table
using union all, it will work though datatype of data differs from each other

But in sql server 2005, it will throw error for datatype mismatch

declare @test table(variant_column sql_variant)
insert into @test
select cast('test' as sql_variant) union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

Find Alphanumerals only

Sometimes when you import data from other system, you may need to clean the data by removing unwanted data. Consider you import data where you want to keep only those which are purely alphanumerals. Here is a way to find out

declare @table table(data varchar(20))

Insert into @table
select '23498' as data union all
select 'tetst' union all
select 'kj&98' union all
select 'ad912'

Compatibility Level of a database

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions

--Method 1
EXEC sp_dbcmptlevel your_database_name