SQL Server - Template Script for Looping Throught Table Without Using Cursor

Below is a single SQL Script I put together to use as a template whenever I need to loop through an SQL Table to then perform operations per row on certain data I can do it using this script.

The template is simple to understand it basically uses a temp table to copy into it the data you are interested in and then loop through it. The template then shows how to use the value you get from each row by just printing it to the screen. Of course you can replace that with any other code you want to.

-- Created 08/04/2010 by Matt Harrison
-- Template for Looping through table rows without using a cursor
------------------------------------------------------------------

DECLARE @RowCnt int
DECLARE @MaxRows int
SET @RowCnt = 1

DECLARE @TempTableName Table (rownum int IDENTITY (1, 1) PRIMARY KEY NOT NULL , Value_Name1 nvarchar(10))

-- Populate the TempTable
INSERT INTO @TempTableName (Value_Name1) (SELECT Value_Name1 FROM DatabaseTableName)
SET @MaxRows = (SELECT count(*) FROM @TempTableName)

-- Foreach Row in TempTableName
WHILE @RowCnt <= @MaxRows
BEGIN

 DECLARE @Value_Name nvarchar(10)
 SET @Value_Name = (SELECT Value_Name1 FROM @TempTableName WHERE rownum = @RowCnt)
 
 PRINT 'Row Value_Name ' + @Value_Name
 
 -- Next Row
 SET @RowCnt = @RowCnt + 1
END

Reset Service Broker Quene in SQL Server

I find from time to time when using a Service Broker Queue in SQL Server it just stops and messages just sit in the queue. I have found that the script below forces something to get reset that springs the queue back into life. It's ages since I found this script so can't even remember why it works, but it usually does.

ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'Password';

Where "Password" can be set to any suitable password.

Reseed an SQL Table Identity Column

If you get an unexpected error like this...

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'Table_Name'.

You look at your table and the primary key and realise that the primary key that was complianing was a indentity column then you think well isn't that a bit odd. The point of an identity column is that you insert new rows in a database and it generates a primarykey value for you, usually a unqiue integer value.

Sometimes though for some mystic unknown reason (one that I can't be bothered to find out the real cause for) the identity value that is created is invalid. So whats the solution?

DBCC CHECKIDENT

You can use this SQL command to check what the current status of an identity column on a table is. If you run the below command, it will tell you the current identify value, i.e. the next value that would be generated as a unquire identity value, and it will tell you the current column value, ie. the current highest unqiue identity value used in your database.

DBCC CHECKIDENT ("Table Name", NORESEED);

Now if it returns something like this then you may have a problem.


Checking identity information: current identity value '10', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If the current column value is higher than the current identity value then the problem may be that you are trying to insert a new row and the identity value being inserted automatically is '10' when the rows have already been created for identity values up to '17'.

To fix this just run this command to reset the identity value.

DBCC CHECKIDENT ("Table Name", RESEED);

Then run the previous command again and you should see this result.

Checking identity information: current identity value '17', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sorted.

Drop Constraint in MS SQL that has an unknown name

Now when I say unknown name I catually mean I know constraint I want to drop, however the name of the constraint was automatically generated by SQL Server and that name contains a random number e.g.

"DF__user__15502E78"

Well I could just write a script like this to get rid of it:

alter table [user]
drop constraint [DF__user__15502E78]

Fine that works, but what if I have created multiple version of that DB on different servers, each time SQL Server creates that constraint for me automatically it will contain a different random number in the name. So now when I run the script I worte above on a different server it fails.

So I wrote this little script below, that will search out the constraint and drop it for me. Now you will have to tweak this to get it to work for you as you may have more constraints as I ad in my DB, so you will need to alter the WHERE clause a bit to just get back the constraint name you are looking for.

-- Because this constraint is dyncamically created by SQL Server
-- the name of the constraint contains a random number
-- so the name will be different from DB to DB.
-- So the only way to drop the constraint is to use the script below.
DECLARE @constraint varchar(100)
SET @constraint = (SELECT OBJECT_NAME(OBJECT_ID) --AS NameofConstraint,
            --SCHEMA_NAME(schema_id) AS SchemaName,
            --OBJECT_NAME(parent_object_id) AS TableName,
            --type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(parent_object_id) = 'user'
    AND type_desc <> 'PRIMARY_KEY_CONSTRAINT')

print @constraint

declare @sqlscript varchar(200)

set @sqlscript = 'alter table [user] drop constraint ' + @constraint

print @sqlscript

exec(@sqlscript)

If you look at the select clause in the script above that is what i'm using to return the name of the constraint, you can uncomment the values in that select statement and just run and tweak that to get it just right for you and then insert it back into the rest of the script and your all sorted.

SQL Server Collation

This error sucks the first time you see it because you may have no idea what it means:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Well you can do a search to find the solution but basically if your doing a join between two tables from two seperate databases then this might happen. One has the wrong collation. You can Check like this:

use DB1
print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

use DB2
print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

If they are different then on your join just add this to the end of the code to convert the collation.

JOIN
Table T ON T.ID = P.ID COLLATE Latin1_General_CI_AS