Thursday 10 February 2011

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

Wednesday 9 February 2011

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.