Earlier this month, I presented a webcast on the “Underappreciated features of Microsoft SQL Server” during the “Exclusive Webcast series on Microsoft SQL Server”. As one of the developer productivity features provided with the SSMS, I presented the Template Explorer.

The session received a very good reception, and I thank-you – the community for the same. After the session, we had a very nice round of Q&A, and I was asked a very interesting question – “How can I deploy a template to all of my developer workstations?”

It’s a very interesting question, and you would need the help of your IT Administrator for the same. However, here are the high-level steps to do so.

NOTE: These steps are formulated for Microsoft SQL Server 2008/2008 R2. Depending upon your version of SQL Server, the paths referenced may change.

1. Login to SQL Server Management Studio
2. Go to View->Template Explorer or hit Ctrl+Alt+T
3. Customize the template of your choice or create a new one by using the method described in my post – http://beyondrelational.com/blogs/nakul/archive/2011/01/24/template-explorer-underappreciated-features-of-microsoft-sql-server.aspx
4. Navigate out to the location: %APPDATA%MicrosoftMicrosoft SQL Server100ToolsShellTemplates
5. Notice that the newly created template is stored as a .sql file under the category chosen. Thus, if you developed a template for a T-SQL Stored procedure, you will find it under ..SqlStored Procedure
6. All that needs to be done now is to copy this template over to the same location on the target machine. Thus, if you have a team of 10 developer machines, all you need to do is to make sure that the template is placed at the mentioned path for each of these 10 developer machines

Typically, deployment of the custom template to the %APPDATA% folder hierarchy on the destination machine can be handled via a logon script by your IT administrator.

Do let me know if the above mentioned steps helped you roll out custom templates confirming to your coding standards across your team.

Until we meet next time,

Be courteous. Drive responsibly.

I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

During the session, I demonstrated the T-SQL Debugger (http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx). A week before the session, I received a new laptop from IT. I was planning to conduct the demos from the same, and hence went about testing them and running through them as if it were a live session.

But, all was not good. As soon as I started the T-SQL Debugger demo, I encountered the following error:

—————————
Microsoft SQL Server Management Studio
—————————
Unable to start program ‘MSSQL:://MyInstance/master/sys/=0’.
A debugger dll, msdbg2.dll, is not correctly installed. Please repair your Visual Studio installation via ‘Add or Remove Programs’ in control panel.
If the problem persists, you can manually register msdbg2.dll from the command prompt with ‘regsvr32 "%CommonProgramFiles%Microsoft SharedVS7Debugmsdbg.dll"’.
—————————
OK  
—————————

The issue in my case was a bad dll (a problem we know as “dll ghost”). Here’s how I troubleshot the error:

1. Navigate out to the location mentioned – “%CommonProgramFiles%Microsoft SharedVS7Debug”
2. Confirm that the dll – msdbg2.dll is present at the location
3. Right click on the dll and note the version of the dll

Because we know that the SQL Server 2008 R2 uses the Visual Studio 2008 shell, the dll version should at least be a 9.xx.xxx. Unfortunately, in my case, it was: 7.10.3077.0 – which is a very old version.

I had to do the demo and most of the laptop had already been configured for my day to day activities. Therefore, I looked around for a “healthy” installation of the SQL Server and checked the version of the dll there. The version was exactly as expected:

So, what do I do?

1. Close the SSMS instance
2. Unregister the existing dll by launching the Command Prompt under the Administrator mode and using regsvr32 /u msdbg2.dll
3. Delete the “bad” dll from the location mentioned above
4. Copy and paste the “healthy” dll
5. Register the dll by using regsvr32 msdbg2.dll at the administrative command prompt
6. Restart SSMS and attempt to debug

Looking at the version of the bad dll, I suspect the dll was installed by Visual Studio 2003 (v7.0), and IT probably installed SQL Server before they began the Visual Studio installation (I need to use the entire spectrum – Visual Studio 6 – 2010 and only one version of SQL Server!).

Do you know of any other scenarios that would cause this issue? Please leave a note for the benefit of all.

Until we meet next time,

Be courteous. Drive responsibly.

I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

During the session, I demonstrated the T-SQL Debugger (http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx). Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature, which always gives a sense of satisfaction to a presenter. I felt that I had brought something new to the audience, and that the audience would be using the features demonstrated. That’s great!

I received a number of questions related to the debugger. Earlier this week, I had mentioned about which SQL Server versions/editions support T-SQL Debugging, and what are the basic permissions that one needs to use the T-SQL debugger (Enabling T-SQL Debugger in SQL Server Management Studio (SSMS)).

Even after you make sure that the user permissions are configured correctly, you may find that you are still not able to use T-SQL Debugging if attempting to debug when the Database engine is running on a computer other than where the Query editor is running. This is because debugging involves both server & client side components and your Windows Firewall is not configured correctly.

WARNING: If you are not comfortable with modification of the Windows Firewall rules, please allow your IT administrator to make these configuration changes. The author is not responsible for any security or other system stability issues that may arise due to improper Firewall configuration.

Here are the configuration changes that need to be done (for SQL 2008):

On the Server

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:Program FilesMicrosoft SQL ServerMSSQL10.InstanceNameMSSQLBinn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance
3. If using IPSec, you will also need to open UDP port 4500 and 500

On the Workstation (where the Query editor is running)

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE

That should get your debugger working as expected.

Until we meet next time,

Be courteous. Drive responsibly.

I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

The session went very smooth, and I was fortunate enough to have a strong following throughout the session. I presented the following underappreciated features during the session:

1. For Administration: Object Explorer Details and Object Explorer
2. For Development: Template Explorer and T-SQL Debugger

Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature – the T-SQL debugger. I received a number of questions related to the debugger, and I will attempt to address a few of these in the upcoming sessions.

For those who would like a quick, hands-on experience of the T-SQL Debugger, you may want to visit my article at: http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx

Some of the questions I received were:

1. Is the T-SQL Debugger version or edition specific?
2. I use SQL Server 2005 and I am not able to see the debugger
3. I use SQL Server 2008 R2, and yet the debugger is not available

The answer to each of these questions is as follows:

History

The T-SQL Debugger has been around at least since the days of Microsoft SQL Server 2000 (that’s when I started using it) – it’s just that it’s not being used often which makes it an underappreciated feature, and one of the key demos that I wanted to demonstrate in the session.

Enabling the T-SQL Debugger in SQL Server Management Studio

To the best of my knowledge, the debugger is available in all editions of the Microsoft SQL Server, except the Express edition. Hence, if you are not able to see the T-SQL Debugger in your SSMS, most probably,  you are using the SSMS that comes with the Express edition.

If you can see the debugger, but are not able to debug a T-SQL query, it is important to note that not everyone is allowed to debug T-SQL code. This is because, T-SQL Debugging is a highly-privileged operation. To enable the T-SQL debugger in SSMS, the following must be true:

1. The user context under which the SSMS is running must be a member of the sysadmin fixed server role
2. The user must be connected to the SQL Server using a user who is member of the sysadmin fixed server role

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

The pre-requisite for today’s session is a basic knowledge of connecting to a SQL Server using the command line.

SQLCMD Utility

Batch files and command line utilities are perhaps some of the most underappreciated features of any IT establishment. When connecting to a database, we had the “OSQL” utility in the days of SQL Server 2000.

When SQL Server 2005 came about, it introduced the SQLCMD utility. This utility is a replacement of OSQL, and has a large number of advantages over the same. To list a few:

1. Parameterized variable support
2. Use SQLCMD to execute both T-SQL and DOS-commands (Refer Pinal’s post here)
3. Improved Performance & security
4. Support for DAC
5. Multi-server connection

There may be more features of SQLCMD, which you are more than welcome to mention in the discussion associated with this post.

SQLCMD rules

There are some basic rules for any SQLCMD query. They can be summarized as under:

1. SQLCMD commands must be the first statement on a line
2. Only one SQLCMD command permitted on each line
3. SQLCMD commands can be preceded by white spaces or comments
4. Before the start of each SQLCMD command, use a colon (“:”). Only exception to this rule are “exit” and “!!” commands
5. Single-line comment characters are two hyphens (–) and must appear at the beginning of a line

SQLCMD and SSMS

What many people don’t know is that the SQLCMD mode is also accessible from the SQL Server Management Studio. This gives you as a developer, the comfort of writing and executing SQLCMD files using the familiar environment of the SQL Server Management Studio.

Enabling SQLCMD mode in the SSMS

1. Launch the SQL server Management Studio
2. Click on “New Query” to open a new instance of the T-SQL Query editor
3. Go to Query –> “SQLCMD”

Writing a simple query in the query editor

Taking an example from the Books On Line (http://msdn.microsoft.com/en-us/library/aa833281.aspx), write the following code in the T-SQL editor:

!!mkdir c:TempSqlcmdOutput
:out c:TempSqlCmdOutputtestoutput.txt
select @@VERSION as 'Server Version'
!!dir
GO
select @@SERVERNAME as 'Server Name'
GO 4

Now, click “Execute” or hit “F5” to execute the query. Navigate out to the directory – “C:TempSqlCmdOutput”, and open the “testoutput.txt” file. You can see that the combined output of the DOS-commands and the T-SQL Query is available there.

Execution technology

When run from the command-line SQLCMD uses the OLE DB provider, whereas when run from SSMS, it uses the Microsoft .NET framework sqlclient.

Some limitations of the SQLCMD mode in SSMS

Because the SSMS cannot display interactive prompts, such queries cannot be executed from within the SSMS, and must be used from the command line.

Conclusion

We can now see how the most modern SSMS interface can help us write efficient command line SQLCMD queries. I personally use the SSMS to edit production level SQLCMD queries for our product and hence I urge you all to experiment with the SQLCMD mode and experience the raw power that it has to offer.

This post concludes my series on the "Under Appreciated Features of SQL Server". It has been great learning new stuff about SQL Server throughout writing of this series. I will be sharing a few of the nuggets that I have accumulated in the time to come. I hope that you, my kind readers will enjoy my future posts as well.

Until we meet next time,

Be courteous. Drive responsibly.