Execute Stored Procedure when SQL Server starts

The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution – i.e it runs every time SQL Server service is started.

Here’s how to execute a Stored Procedure when SQL Server starts

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'true'

To disable the stored procedure again

EXEC sp_procoption @ProcName = 'usersp_CleanTables',
@OptionName = 'startup',
@OptionValue = 'false'

Note: For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the ‘master’ database and cannot contain INPUT or OUTPUT parameters.

Feedback:

SKIP and TAKE ‘N’ number of records in a SQL Server Select Query

How do you SKIP and TAKE ‘n’ number of records from a resultset? I have done this previously using LINQ as demonstrated over here Implementing Paging in a Generic List using LINQ

But how do you skip and take ‘n’ number of records in SQL Server 2005/2008? Here’s how:

DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT ProductID, CategoryGroupID
FROM
(
SELECT tbl.*, ROW_NUMBER() OVER (ORDER BY ProductID) rownum
FROM @TT as tbl
) seq
 WHERE seq.rownum BETWEEN 6 AND 10

As you can see in the query above, we are skipping the first 5 records on an ordered resultset and taking the next 5 records.

OUTPUT

Feedback:

Find the Missing Identity Numbers in SQL Server 2005/2008

We as Database Developers often come across tables that have identity columns. Many a times, there are missing rows in that table, which have probably been deleted during a database operation. Have you wondered how to find the missing rows? Well here’s a query written by Markwill that helps you find the missing identity numbers in a table. I found this query extremely useful and thought of sharing it with all of you.

Let’s create some sample data first. For demonstration purposes, please assume that this table has an identity column

-- Create Sample Table
DECLARE @TT table
( ID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 1, 'AA')
INSERT INTO @TT VALUES ( 2, 1, 'AA');
INSERT INTO @TT VALUES ( 3, 1, 'AA');
INSERT INTO @TT VALUES ( 4, 2, 'BB');
INSERT INTO @TT VALUES ( 5, 2, 'BB');
INSERT INTO @TT VALUES ( 6, 2, 'BB');
INSERT INTO @TT VALUES ( 7, 2, 'BB');
INSERT INTO @TT VALUES ( 8, 3, 'CC');
INSERT INTO @TT VALUES ( 9, 3, 'CC');
INSERT INTO @TT VALUES ( 10, 3, 'CC');
INSERT INTO @TT VALUES ( 11, 3, 'CC');
INSERT INTO @TT VALUES ( 12, 3, 'CC');
INSERT INTO @TT VALUES ( 13, 4, 'DD');

We will now go ahead and delete a few rows from this table using the following query

DELETE @TT WHERE ID IN(3,6,11);

SELECT ID FROM @TT

On selecting the records, we will see the missing identity numbers as shown below:

As you can observe from the screenshot above, ID 3, 6 and 11 are missing.

So how do we programmatically determine missing numbers? Here’s the query to detect missing identity numbers

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

OUTPUT

Feedback:

Find the Next and Previous Records in a SQL Server Table

I was solving a strange requirement a few days ago – well not strange for my client though. The client had a Product table. They wanted a query which would accept a Product ID and display the Next and Previous Products of that Product ID listed in that table or a result set. Now I may be wrong, but queries like this are not really solved in T-SQL; rather a front end UI like the GridView is capable of navigating through the records.

However if you intend doing it in T-SQL, then here’s how we can retrieve the Next and Previous Records of a given record

-- Create Sample Table
DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT
  (SELECT MAX(ProductID)
        FROM @TT Prod1
        WHERE     Prod1.ProductID <  Prod2.ProductID ) as PreviousProductID,
  (SELECT MIN(ProductID)
        FROM @TT Prod1
        WHERE     Prod1.ProductID >  Prod2.ProductID ) as NextProductID
FROM @TT  Prod2
WHERE  ProductID = 744

OUTPUT

Feedback:

Recompiling Stored Procedures in SQL Server – All, Few or One at a time

Amongst other uses, recompiling your stored procedures can be useful especially when you want to ensure that you haven't deleted/renamed any columns/tables.

The following queries shown below recompile all stored procedures the next time they are run.

Recompiling all the Stored Procedures in a Database

-- Recompile all Stored Procedures and Triggers on a Database
USE AdventureWorks;
GO
EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";
GO

Note: See my post over here for other uses of sp_MSforeachtable 8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

Recompile all the Stored Procedures in a Table

-- Recompile all Stored Procedures that act on the Customer table
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO

Recompile a specific Stored Procedure

-- Recompile a specific Stored Procedure uspGetEmployeeManagers
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetEmployeeManagers';
GO

Feedback:

Find out the Binary, ASCII and Character of a Given String in SQL Server

When you are storing data inside fields like ‘address’, there are bound to be unusual characters in it which make way due to poor validation rules. A good way to look for them is to convert your string to varbinary.

I came across this cool query from Peter which shows how to convert a string to varbinary and find out the Binary, ASCII and Char of each character in the string. Here’s the query:

DECLARE @MyAddress varchar(35)
SET @MyAddress = 'CANTB RY EA%T P.O.Box 55343'

DECLARE    @BIN AS VARBINARY(100)
SET    @BIN = convert(varbinary(100),@MyAddress)

SELECT    SUBSTRING(@BIN, Number, 1) AS Binary,
ASCII(SUBSTRING(@BIN, Number, 1)) AS ASCII,
CHAR(ASCII(SUBSTRING(@BIN, Number, 1))) AS Character
FROM    master..spt_values
WHERE    Type = 'p'
AND Number BETWEEN 1 AND DATALENGTH(@BIN)

OUTPUT

Feedback:

List the Remaining Business Days of this Year in SQL Server

I recently required a query for a report which listed the remaining business days for this year, including the current day. I immediately thought of using spt_values count table.

The spt_values System Table inside the master database is an ideal table to use when you need to use  numbers in your query. This system table is used internally by sql server for various operations. For eg: The query shown here generates values from 0 to 2047

SELECT number from master..spt_values
WHERE type='p'

Let’s use this system table to list the remaining business days for this year:

SELECT DATEADD(d,number,GETDATE())
FROM master..spt_values
WHERE Type='p'
AND DATENAME(WEEKDAY, DATEADD(DAY, number, DATEDIFF(DAY, '19000101', GETDATE())))
NOT IN ('Saturday', 'Sunday')
AND YEAR(DATEADD(d,number,GETDATE()))=2009

OUTPUT

To Find the Business Days in a Quarter, check this query of mine

Finding the Business Days In a Quarter and Number them in SQL Server 2005/2008

Feedback:

Get BackUp Information of all SQL Server 2005/2008 Databases

I had recently shared a few queries related to SQL Server BackUp over here:

How to do a Full and Differential Database Backup in SQL Server

Verifying and Restoring a Differential Backup using T-SQL In SQL Server

Delete BackUp and Restore History for databases in SQL Server 2005/2008

The msdb database stores details of all backup and restore operations. Now if you want to retrieve back up information of all your databases on your server, then here’s the query to do so:

SELECT database_name, user_name as ExecutedBy, physical_device_name,
backup_finish_date, backup_finish_date
FROM msdb..backupset bckset
INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id

OUTPUT

Note: I have run this query on a new setup hence do not have many backup’s.

Feedback:

Stop, Start, Pause and Resume a SQL Server Instance using net Commands

A user asked me recently if it was possible to start and stop a SQL Server Service using command line. The answer is YES and it is quite easy to do so as shown below:

Open your windows command prompt and type the following command to Stop, Start, Pause and Continue a SQL Server Instance.

Note: If you are running this on Vista or higher OS, you may have to run the command prompt as administrator. Go to Start > All Programs > Accessories > Right click Command Prompt > Run as Administrator

To Stop, Start, Pause and Resume a SQL Server Default Instance

Stop – net stop MSSQLSERVER

Start – net start MSSQLSERVER

Pause – net pause MSSQLSERVER

Continue – net continue MSSQLSERVER

To Stop, Start, Pause and Resume a SQL Server Named Instance

Note: I have a named instance called SUPROTIM2 on my machine.

Stop – net stop “SQL SERVER SUPROTIM2”

Start – net start “SQL SERVER SUPROTIM2”

Pause – net pause “SQL SERVER SUPROTIM2”

Continue – net continue “SQL SERVER SUPROTIM2”

Feedback:

A Simple Family Tree Query Using Recursive CTE’s in SQL Server 2005/2008

I was recently working with one  of  my colleagues Steve on a Family Tree query. His software collected information about Relations of a person and then displayed it in a TreeView. We started discussing about Recursive Common Table Expressions(CTE) and how well CTE’s fit into such requirements.  I will give you an example of how we used Recursive CTE’s to get the Parent and Generation Level of each person in the Family Tree. One of the tables in his software looked similar to the following -

Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query

DECLARE @TT TABLE 
(
ID int,
Relation varchar(25),
Name varchar(25),
ParentID int
)

INSERT @TT 
SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3 

Here’s the query to find the Parent and Generation Level using Recursive CTE’s

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
    FROM @TT
    WHERE ParentID IS NULL
    
    UNION ALL
    
    SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1
    FROM @TT AS Fam
    INNER JOIN FamilyTree
    ON Fam.ParentID = FamilyTree.ID
)

SELECT * FROM FamilyTree

OUTPUT

Feedback:

Viewing Linked Server Information in SQL Server 2005/2008

In one of my previous posts, I demonstrated how to create a Linked Server.

Now during maintenance, if you desire to view the linked servers on your SQL Server instance, use the ‘sys.servers’ system catalog view as shown below:

SELECT name, provider, data_source, connect_timeout, 
query_timeout, is_rpc_out_enabled
FROM sys.servers
WHERE is_linked = 1

OUTPUT

As part of the maintenance activities, you can also drop a linked server using the following query:

EXEC sp_dropserver @server= 'SUPROTIM-PC'

To remove related remote and linked server logins for the server, use ‘droplogins’ as the second parameter as shown below:

EXEC sp_dropserver @server= 'SUPROTIM-PC', @droplogins='droplogins'

Note: Use sp_helpserver to show available servers.

Feedback:

Finding the Business Days In a Quarter and Number them in SQL Server 2005/2008

I recently wrote a post about Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008 . For the same report, a requirement was to pull the working days in the current Quarter and number them sequentially. The requirement also stated that the weekends should be numbered as 0 in the report. Here’s how the query works for this requirement

DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
SELECT @date2= DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1);

WITH CTE (dt) 
AS
(
SELECT @date1
UNION ALL
SELECT DATEADD(d,1,dt) FROM CTE
WHERE dt < @date2
)

SELECT cte.dt as BDate, COALESCE (dte.WorkingDay,'') as DayNumber from CTE cte
LEFT JOIN
(
SELECT dt as d, 
 ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm, '19000101', dt) ORDER BY dt) WorkingDay
 FROM CTE 
WHERE DATENAME(dw,dt) Not In ('Saturday', 'Sunday')
)
AS dte ON dte.d = cte.dt 
ORDER BY cte.dt

OUTPUT

Feedback:

Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008

Recently I had to pull out the number of weekends in this quarter and list them for a report. Here’s how I did it

DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
SELECT @date2= DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1)
;WITH CTE (dt)
AS
(
SELECT @date1
UNION ALL
SELECT DATEADD(d,1,dt) FROM CTE
WHERE dt < @date2
)
SELECT dt from CTE 
WHERE DATENAME(dw,dt) In ('Saturday', 'Sunday')

OUTPUT

If you want to list all the weekends in a year, just replace the @date1 and @date2 variables with the appropriate dates as shown here:

SELECT @date1 = '1/1/2009'
SELECT @date2= '12/31/2009'

Feedback:

Rebuild System Databases in SQL Server 2008

I recently had to rebuild my Master database to fix a corruption problem. Here’s how I did it in two steps using the command prompt

Step 1: Open your Command Prompt (Start > Run > Cmd). Switch to the directory where you have installed SQL Server. In most of the cases the directory is C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

Step 2: Run the following command to rebuild the system databases (Windows Authentication)

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=instancenm  /SQLSYSADMINACCOUNTS=ac

In my case, the instance name was Suprotim-PC and SysAdminAccount was Suprotim

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SUPROTIM-PC /SQLSYSADMINACCOUNTS=SUPROTIM

If you have Mixed Authentication Mode Configured, then use the following command

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SUPROTIM-PC /SQLSYSADMINACCOUNTS=SUPROTIM /SAPWD=sapassword

On running the command, a new command window will appear for a few seconds and if everything goes smooth, the command will return back to the original command window with no messages.

There should be some points considered before rebuilding system databases. To learn more about rebuilding system databases in SQL Server 2008, read this article http://msdn.microsoft.com/en-us/library/dd207003.aspx

To rebuild system databases in SQL Server 2005, read this article http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx#rebuilddatabase

Feedback:

Generate a HASH value using SQL Server 2005/2008

Do you want to pass a string and generate a HASH value from it using SQL Server? Here’s a very simple way of doing so using HashBytes. Let us see this with an example:

DECLARE @SomeValue nvarchar(50);
SELECT @SomeValue = 'SQLServerCurry';
SELECT HashBytes('SHA1', @SomeValue);
GO

generates an output

0x51FD96B1BD5CE0003370551D5498BA3C0E64BE4C

Using HashBytes, you can use algorithms like MD2, MD4, MD5, SHA or SHA1. The input string can be a varchar, nvarchar or varbinary and can be upto a maximum of 8000 bytes

Feedback: