SQL Server Blogs

When you look at the sys.servers catalog view, you will see list of linked servers defined on the server plus one additional row for the server itself. You can distinguish the local server from linked servers by column server_id, which in case of local server has value of 0. select server_id , name ...

Now-a-days lot of questions are asked in the forum about extracting particular text from a string Here I give some examples on how to do it --Example 1 (Extracting tags and other informations) declare @s varchar ( 100 ) declare @temp table ( id int identity ( 1 , 1 ), data varchar ( 100 )) set @s = '567<a>data<fg>ua<li>test<iu>welcome'...

As you know, semicolon is used as a statement terminator in almost all RDBMSs. However the usage of semicolon is optional in SQL Server except a few cases. Here are atleast the three cases where usage of semicolon is a must 1 At the start of the Common Table Expression (If any statement preceeds it)...

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 Enterprise Edition of the SQL Server 2008 and not...

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...

I worked yesterday on a script that is supposed to run on DEV database after it has been restored from production. This script adjusts configuration data and security in the database so developers can work safely on production data without compromising security policies. The original script has a long...

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...

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...

This post was to be about behavior of SSIS when they insert rows to tables with triggers on them. We had an issue with one of our packs that began to fail 'all of a sudden'. But when I began writing this post and building test SSIS project and database objects, I came across something even more...

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...