Madhivanan

SQL Server 2008 features that work only in Enterprise Edition

Madhivanan — Tue, 03 Nov 2009 12:44:00 GMT

One of the new features available in SQL Server 2008 is compressed backup which takes backup of a database with mimimum possible size. The syntax is backup database db_name to disk = 'backup_path' with init , compression But this works only in...(read more)

Script out Procedures to seperate files

Madhivanan — Mon, 26 Oct 2009 09:34:00 GMT

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

Finding out problematic data in Bulk Insert data truncation error

Madhivanan — Mon, 19 Oct 2009 10:07:00 GMT

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

Removing tags from a string

Madhivanan — Mon, 12 Oct 2009 13:28:00 GMT

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

Maximum length of each column

Madhivanan — Thu, 01 Oct 2009 09:31:00 GMT

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

Dropping all temp tables

Madhivanan — Fri, 18 Sep 2009 14:49:00 GMT

In SQL Forum one of the posters asked a question about dropping all the temporary tables There are many ways to do this One of the ways to to run the following code declare @sql varchar ( 8000 ) set @sql = '' select @sql = @sql + ' drop table...(read more)

Cursor statuses

Madhivanan — Tue, 08 Sep 2009 14:50:00 GMT

Here are the queries that findout the cursors based on their statuses 1 Find out the cursors that are allocated but not opened or closed --Method 1 select name from sys.dm_exec_cursors ( 0 ) where is_open = 0 --Method 2 select cur . cursor_name from sys...(read more)

Generate Fibonacci series - No loop, no recursion

Madhivanan — Tue, 01 Sep 2009 13:21:00 GMT

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive function Peso posted the code using the Common table expression Here are my methods that involve no loop or no recursion 1 Use Quirky update technique...(read more)

How to Generate Script along with Data?

Madhivanan — Fri, 21 Aug 2009 11:16:00 GMT

One of the new features available in SQL Server 2008 is scripting the tables along with data (in the form of INSERT statements). Here are the useful links that can help you generating script with data SQL Server 2008 http://www.kodyaz.com/articles/sql...(read more)

How to display detailed data below each header data?

Madhivanan — Thu, 06 Aug 2009 13:18:00 GMT

One of my friends asked me if it possible to generate Detailed data for each Header data without using a Cursor or while loop. (The output is something like the one usually seen via reporting tool). He wanted the result to be output to text file Consider...(read more)

Regular expression

Madhivanan — Mon, 03 Aug 2009 09:53:00 GMT

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided Consider the following set of data this is test place located at no 123456 test foo 35234 bar...(read more)

Command Prompt shortcuts to open SSMS

Madhivanan — Mon, 20 Jul 2009 12:46:00 GMT

Here are the Command Prompt shortcuts to open Enterprise Manager/Management Studio SQL Server 2000 isqlw SQL Server 2005 sqlwb SQL Server 2008 ssms...(read more)

CTE in a View

Madhivanan — Sat, 11 Jul 2009 10:51:00 GMT

It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible create view numbers as with numbers ( n ) as ( select 1 as n union all select n + 1 from numbers where n < 10000 ) select n from numbers option...(read more)

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)