How to create code snippets in SQL Server Management Studio (SSMS)

Recently I’ve been working on a project which requires plenty of stored procedures and custom logging. For error handling I don’t want to repeat my logging TSQL statements for each and every SP I write. I created a code snippet and let the IDE code for me. Follow these simple steps to create code snippets in SSMS. The following example inserts pure TSQL code without any parameters and at the end of this blog post I’ve mentioned how to create code snippets which include parameters using one of the built in code snippets of SSMS.

Code snippets are stored as XML files in particular format and imported in to the SSMS. These XML files have the extension of .snippet and you can find the built in code snippets in this location

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033

Let’s call this path as 1033 Snippet Root

In order to get the .snippet file template open one of the simplest built in code snippets from the 1033 Snippet Root. You can find this from the View folder. Under the View folder there’s a code snippet name Create View.snippet

Open it in a text editor, (Notepad++ would be a good choice). This is the structure of a basic TSQL code snippet XML file.

View Snippet

The snippet I created is far more simpler than the above as it does not require any parameters, so I do not need the <Declarations></Declarations> section. Under the <Snippet></Snippet> I have only the <Code></Code> block.














The above code snippet will get the error details of the current execution context and THROW it.

After creating the XML file save it with a .snippet extension. I saved the above as Default Throw.snippet Now we have to import the snippet into the SSMS.

Importing a code snippet into SSMS

Open SSM, go to Tools and then click Code Snippets Manager


Select a folder where you want to put your snippet (This folder structure is simply a categorization of the snippets). If you want you can add a new folder as well. In my case I wanted to put the snippet under My Code Snippets folder. Then click Import. Browse for the snippet file and import it.


That’s all, now we can use the our snippets in the TSQL editor of SSMS. In order to bring the snippet intellisense press Ctrl +K, Ctrl + X and choose the folder then your snippet and press TAB to insert it.



Recovering a SQL Server database from Suspect Mode

Yesterday my SharePoint environment didn’t respond I checked the DB server and got to know few SharePoint databases were in suspect mode. Now I want to make the databases up and running as usual in order to get my work done. This is a SQL Server thing. There could be several reasons for a database to go to the Suspect Mode. In my case my .ldf file was corrupted.

This forum thread explains more reasons why a database goes to Suspect Mode.


In order to get the database follow these steps. Login to the server using an account with sysadmin privileges. And set the target to database to EMERGENCY mode.




Then set the database to SINGLE USER mode in order to run the recovery.


Now you can run the following command to rebuild the transactional log file. Here data loss is allowed.

   1: DBCC CheckDB ([db name], REPAIR_ALLOW_DATA_LOSS)

Then set back the database to MULTI USER mode.



Sometimes you might run into problems where you cannot set the database to MULTI USER mode; SQL Server complains with this error “The database is in single-user mode, and a user is currently connected to it.”