Madhivanan

Export to Excel - data length exceeds 255 characters

Madhivanan — Mon, 22 Jun 2009 13:08:00 GMT

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...(read more)

Quirky update in sql server

Madhivanan — Wed, 10 Jun 2009 09:18:00 GMT

When you use variables in update statement and update the columns with the values of variables which keep on changing for each row , you can simulate many things that are done using Loop or a Cursor declare @temp table ( product_name varchar ( 100 ),...(read more)

Removing unwanted characters

Madhivanan — Mon, 11 May 2009 12:49:00 GMT

Sometimes when you import data from other system , you may want to clean up data by 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...(read more)

How to find size of backup files in a folder?

Madhivanan — Thu, 07 May 2009 10:04:00 GMT

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...(read more)

Import from Text file - Single column to multiple columns

Madhivanan — Tue, 05 May 2009 09:37:00 GMT

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...(read more)

Tables with no Primary and Foreign keys

Madhivanan — Fri, 10 Apr 2009 14:35:00 GMT

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...(read more)

Last Backup/Restore date of database

Madhivanan — Fri, 03 Apr 2009 10:33:00 GMT

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...(read more)

Table level Backup/Restore

Madhivanan — Thu, 02 Apr 2009 14:43:00 GMT

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?...(read more)

Filling random passwords - Set based approach

Madhivanan — Fri, 27 Mar 2009 14:13:00 GMT

In this post , I showed a way to generate 8 characters random password If you have table with data and add a column for password and want to fill that column with random passwords , you can use this approach. I have seen the usage of a function that generates...(read more)

Behavior Changes to Database Engine Features in SQL Server 2008

Madhivanan — Sat, 07 Mar 2009 10:17:00 GMT

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

Which columns uniquely identify a row?

Madhivanan — Fri, 06 Mar 2009 09:19:00 GMT

Without seeing the table structure,If you are interested in knowing which columns uniquely identify a row in a table, you can use this system procedure EXEC sp_special_columns table_name...(read more)

Beware of the Usage of SET ROWCOUNT

Madhivanan — Wed, 04 Mar 2009 08:08:00 GMT

This is used to set the number of rows affected for DML Declare @test table ( number int ) set rowcount 1 insert into @test select 11 union all select 10 set rowcount 0 select number from @test The above inserts only one row to the table variable @test...(read more)

SQL_VARIANT datatype and behaviour changes

Madhivanan — Fri, 20 Feb 2009 09:26:00 GMT

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 declare @test table ( variant_column sql_variant ) insert into @test select 'test' union...(read more)

Find Alphanumerals only

Madhivanan — Mon, 16 Feb 2009 13:52:00 GMT

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...(read more)

Compatibility Level of a database

Madhivanan — Mon, 05 Jan 2009 12:45:00 GMT

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 There are many methods to know the Compatibility Level of a database using queries...(read more)