December 26, 2008

Parse a comma-delimited string

This T-SQL script parses a comma-delimited string and inserts  the values into a table.  It is a good function candidate too!  
DECLARE @MyString varchar (100), @Delimiter char (1), @NewValuevarchar(30), @Pos int, @NextPos int, @Last char(1)
SET @MyString = 'ca,fl,md,'
SET @Delimiter = ','
SET @NewValue = ''
SET @Last = RIGHT(@MyString, 1)
-- create temp table to hold split values
CREATE TABLE #myTable([CurrValue] varchar(100))
  -- process trailing comma
IF @Last <> @Delimiter
 SET @MyString = @MyString + @Delimiter
  -- get position of first comma
SET @Pos = CHARINDEX(@Delimiter, @MyString)
SET @NextPos = 1
  -- split on comma & insert into table
WHILE (@pos <> 0)
SET @NewValue = SUBSTRING(@MyString, 1, @Pos - 1) INSERT
INTO #myTable (
SET @MyString = SUBSTRING(@MyString, @pos + 1, LEN(@MyString))
SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter, @MyString)
  -- display results
  -- cleanup

December 22, 2008

convert columnar result set to comma-delimited string

T-SQL for comma-delimited string:
DECLARE @RepRegion Varchar(500)
SELECT @RepRegion = COALESCE(@RepRegion + ',' , '') + [RepState]
FROM [dbo].[Reps]
WHERE [TerritoryID] = '12345'
AND [RepState] IN (SELECT DISTINCT [RepState]                             
FROM [dbo].[Reps]                             
WHERE [TerritoryID] = '12345' )
PRINT @RepRegion

November 8, 2008

T-SQL string function: replicate

So you need to generate a custom code...

Since the REPLICATE string function repeats a string value a specified number of times, it can be used with an identity column to build a custom code like this:

[ Yes, well, the example is a bit trite but you get my point.
Actually...this string function could be FUN if you're into ascii art... ]
Syntax: REPLICATE ( string_expression ,integer_expression )
Arguments: string_expression - the string to replicate integer_expression - the number of times the string will be replicated
Returns: The Replicate function returns a sting expression.

November 6, 2008

SSIS/DotNetNuke module namespace error

While working on one of my side projects the other day, Visual Studio threw an ambiguous namespace error during a DotNetNuke website rebuild.
I had been developing a new DotNetNuke module (see previous posting) to serve as a graphical user interface (GUI) for our marketing department. The module enables them to run SQL Server Agent jobs that invoke SSIS packages on demand.
I'm posting the error and its solution here with hopes that it will assist someone.
'ZipEntry' is ambiguous in the namespace 'ICSharpCode.SharpZipLib.Zip'
Solution: Delete the ICSharpCode.SharpZipLib.dll and retain SharpZipLib.dll
Discussion: The error was thrown because a third party module had compiled the SharpZipLib source code into their assembly (ICSharpCode.SharpZipLib.dll) and placed it in the \DesktopModules\ThirdPartyModule\Bin directory.
This caused an ambiguous namespace error to be thrown because the DotNetNuke framework compiles the SharpZipLib source code as SharpZipLib.dll which is located in DotNetNuke's root \Bin folder.
Root cause: There is more than one assembly with the same namespace. Deleting the duplicate assembly and rebuilding the website resolves the problem.
Error thrown: Error: File Manager is currently unavailable. DotNetNuke.Services.Exceptions.ModuleLoadException: C:\DotNetNuke\Admin\Files\FileManager.ascx.vb(780): error BC30560: 'ZipEntry' is ambiguous in the namespace 'ICSharpCode.SharpZipLib.Zip'.

October 14, 2008

Date and time styles

The CONVERT function provides a variety of useful Date and Time styles. Here is the syntax for the CONVERT function syntax:
CONVERT (data_type[(length)],expression [, style])

Here's the T-SQL for date style conversions that I use on a regular basis along with the corresponding query results from running the batch at the bottom:
If the date resides in a char(8) column in MMDDYYYY format, you can do this to convert it to the datetime datatype: 
declare @tempDate char(22), @newDate datetime 
select @tempDate = substring([FileDate],5,4) + '-' + substring([FileDate],1,2) + '-' + substring([FileDate],3,2) + ' ' + '00:00:00.000' FROM [dbo].[YourTableName] 
select @newDate = (select (cast((@tempDate) as datetime))) 

October 4, 2008


sp_MSforeachdb is another useful undocumented Stored Procedure provided by Microsoft.

Although it comes bundled with SQL Server, it is not documented in Books Online.  

The  sp_MSforeachdb   stored procedure resides in the master database, belongs to the sys schema and is used to process T-SQL commands against each database in the server.

Here's a T-SQL example to change the DB owner in all databases to "sa":

exec master..sp_MSForeachdb 'if "?" in ("master","model","msdb","tempdb","distribution") return use ?
print "Database = "+db_name()
EXEC sp_changedbowner "sa"'

sp_MSforeachdb syntax:
exec @RETURN_VALUE=sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand

sp_MSforeachdb options:  
  • @RETURN_VALUE - return value set by sp_MSforeachdb
  • @command1 - the first command that is processed against all databases. nvarchar(2000)
  • @replacechar - the character that becomes replaced by the database name. The default is ‘?’
  • @command2 - a second command that can also be processed against all database. nvarchar(2000)
  • @command3 - a third command that can also be processed against all databases. nvarchar(2000)
  • @precommand - parameter for a command to run before all database processing begins. nvarchar(2000)
  • @postcommand - parameter for a command to run after all database processing is completed. nvarchar(2000)
Execute this T-SQL code to analyze how sp_MSforeachdb works:
sp_helptext ’sp_MSforeachdb’       

September 26, 2008

Changing logical file names

MODIFY FILE    (NAME = 'RestoredDBname' , NEWNAME =  'TheDBName')
MODIFY FILE    (NAME = 'RestoredDBname_Log' , NEWNAME =  'TheDBName_Log')

September 8, 2008


There are several undocumented Stored Procedures provided by Microsoft that I have found very useful. Although they come with SQL Server, they are not documented in Books Online.  
The  sp_MSforeachtable  stored procedure resides in the master database, belongs to the sys schema and is used to process T-SQL commands against each table in a database.  

T-SQL example:
USE myDatabase GO
EXEC sp_MSforeachtable @command1='Update Statistics ?'
sp_MSforeachtable syntax:
  exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
sp_MSforeachtable options:
  • @RETURN_VALUE - return value set by sp_MSforeachtable
  • @command1 - the first command that is processed against all tables. nvarchar(2000)
  • @replacechar - the character that becomes replaced by the table name. The default is '?'
  • @command2 - a second command that can also be processed against all tables. nvarchar(2000)
  • @command3 - a third command that can also be processed against all tables. nvarchar(2000)
  • @whereand - parameter for selecting rows in the sysobjects table. nvarchar(2000)
  • @precommand - parameter for a command to run before all table processing begins. nvarchar(2000)
  • @postcommand - parameter for a command to run after all table processing is completed. nvarchar(2000)
  Execute this T-SQL code to analyze how sp_MSforeachtable works:
  sp_helptext 'sp_MSforeachtable'

September 1, 2008

Replication with identity columns

As a preliminary task for replication, I needed to produce a list of tables with identity columns.
Here's my script:
select * from sys.tables t, sys.columns c
where t.object_id = c.object_id
and c.is_identity = 1 order by
Replication Considerations
I wrote the following query to check whether the "not for replication" parameter had been set. 
I used the object_name function to obtain the table name and additional information about the last value used in the identity column and whether or not it was being used for replication:
select distinct object_name(object_id) as [table], last_value as [value], is_not_for_replication as [replication]
from sys.identity_columns
order by 1
"Not For Replication" parameter
The important thing to remember is that the "not for replication" parameter should be turned on for the identity columns.
This causes the replication agent to replicate the row to the Subscriber with the identity value from the Publisher intact and the identity value will not be reseeded when the row is inserted in the Subscriber table.
Fortunately, SQL Server 2005 has many new helpful system procedures including sp_identitycolumnforreplication which sets the replication parameter.
I used it in the following script along with the sp_msforeachtable system procedure to quickly iterate through all of the tables in a database.
EXEC sp_msforeachtable @command1 = ' declare @int int set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'
sp_identitycolumnforreplication is an undocumented stored procedure that is not included in SQL Server 2005 Books Online. It is executed by system processes and the value is set in both the publisher and subscriber when you configure a database for replication.

August 31, 2008

SSRS data sources

SQL Server Reporting Services
I recently needed to quickly produce a list of Reporting Services data sources. 
Since Reporting Services uses SQL Server to store report metadata, I wrote the following T-SQL code to retrieve the information I needed.

SELECT Distinct C.Name as 'Data Sources'
 FROM [REPORTSERVER].[dbo].[DataSource] AS DS
 ON DSL.[ItemID] = C.[ItemID]
 ON C.[PolicyID] = SD.[PolicyID]
 AND SD.AuthType = 3)
 ON DS.[Link] = DSL.[ItemID]

August 22, 2008

Replace Cursor with While Loop

The dreaded cursor . . .

Experiencing performance degradation due to cursor processing? Use a WHILE loop instead. Hint: remember to do benchmark testing to verify you'll see a performance gain!!! Here's some T-SQL code to get you started:

@RowCnt int,
@MaxRows int
SET @rowcnt = 1

CREATE TABLE #whileTable
   (RowNum            int IDENTITY (1,1) Primary key,
    DemographicID     int,
    DemographicTypeID int,
    Title             varchar(50),
    DataType          char(1)   )

insert into #whileTable
(DemographicID,Title, DataType, DemographicTypeID)
Select DemographicID,Title=Cast(DemographicID as varchar(12))
       , DataType, DemographicTypeID
From dbo.Demographic
Order by DemographicID
/* DELETE your cursor declaration */
Declare democursor cursor for
    Select DemographicID, Cast(DemographicID as varchar(12))
            , DataType, DemographicTypeID
          From Demographic
    Order by DemographicID

/* REPLACE it with WHILE loop syntax and get the total number of rows to process */

select  @MaxRows=count(*) from #whileTable
WHILE exists    (Select Rownum, DemographicID, DemographicID
                               , DataType, DemographicTypeID
                   from #whileTable
                  where Rownum <= @MaxRows
                    and RowNum = @RowCnt

/* DELETE your open/fetch CURSOR & variable assignments */
open democursor
Fetch democursor into @DemographicID, @Title, @DataType, @DemoType
While (@@Fetch_Status = 0)

/* REPLACE it with the WHILE LOOP */

BEGIN – Begin WHILE loop
                 select @DemographicID=DemographicID, @Title=DemographicID
                        , @DataType=DataType, @DemoType=DemographicTypeID
                   from #whileTable
                  where Rownum <= @MaxRows
                    and RowNum = @RowCnt
– Additional query logic can go here
/* DELETE your last Cursor Fetch  */
Fetch democursor into @DemographicID, @Title, @DataType, @DemoType

/* REPLACE it - increment the WHILE loop counter */

 select @RowCnt = @RowCnt + 1 – increment counter for WHILE loop

 /* DELETE your close/deallocate cursor statements */
End — of While (@@Fetch_Status = 0) Begin
Close democursor
Deallocate democursor

 /* REPLACE it by ending your WHILE loop */


DROP TABLE #whileTable

August 20, 2008

Index fragmentation

Indexes become fragmented during insert, update, and delete transactions - and fragmented indexes can cause performance degradation.
  Internal fragmentation
If the index pages are not filled to the current fill factor level, you're dealing with internal fragmentation.
  External fragmentation
When the physical order of the the index pages doesn't match the logical order, you're dealing with external fragmentation.
  T-SQL code to check fragmentation and the size of the indexes on a table
 use dotnetnuke
SELECT * FROM sys.dm_db_index_physical_stats     (db_id(), object_id('tabs'), 1, NULL, 'DETAILED'go

OR use this code:
  Correcting Internal fragmentation
To correct index fragmentation, execute the ALTER INDEX T-SQL statement.

Reorganize when external fragmentation levels are between 10 percent and 15 percent.

ALTER INDEX ALL ON DotNetNuke.dbo.tabs

 If external fragmentation is greater than 15 percent, use the ALTER INDEX...Rebuild T-SQL statement.