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. Last week, I also discussed a question that was asked to me during the session – “How can I deploy a template to all of my developer workstations?”. While writing last week’s post, I noticed that some things had changed in SQL 11 (Code Named: “Denali”) CTP01 when compared to SQL 2008 as far as the available templates are concerned.

Location of the Templates

Templates are deployed on the user workstation (i.e. the machine where SSMS is running) when the user uses the Template Explorer for the first time. These templates are located at the following locations:

In Microsoft SQL Server 2008: %APPDATA%MicrosoftMicrosoft SQL Server100ToolsShellTemplates

In SQL 11 (“Denali”) CTP01: %APPDATA%MicrosoftSQL Server Management Studio11.0Templates

No Shortcut key

The immediate difference is that SQL 11 CTP01 does not have a shortcut key for the Template Explorer. Ctrl+Alt+T no longer works with Denali.

SSMS – SQL Server 2008	SSMS – SQL 11 (“Denali”)

New & Updated Templates!

SQL 11 (Code Named: “Denali”) CTP01 comes with new templates! SQL 2008 SSMS came with 367 templates, whereas SQL 11 CTP01 SSMS comes with 399 templates as of now.

Here is a detailed listing of the changes. Please note that all of these may not be available in the RTM release, but it is safe to assume that this is a fair indication of the changes. I have not considered differences of default paths and/or white spaces.

1. No Templates for “SqlCe” in SQL 11 (“Denali”) CTP01
2. ..SqlRole has been renamed to ..SqlDatabase Role
3. New set of templates for ..SqlServer Role have been introduced
4. The template to create a new Service Broker queue – Create Queue with Activation.sql – now has the POISON MESSAGE HANDLING turned ON by default
5. Brand new set of 38 templates added for SQL Azure database!
6. DROP STATISTICS template now checks for pre-existing statistics (IF EXISTS check)
7. Create Users scripts (Create Data Reader User.sql, Create User as DBO.sql) no longer have a reference to sp_addrolemember. sp_addrolemember has been replaced with ALTER ROLE/ADD MEMBER
8. Create User as DBO.sql – template now uses square brackets ([]) to enclose identifiers
9. There are no changes to the templates for Analysis Services and for the templates for “Connections”

I hope that you are as excited and looking forward to newer templates as I am. Do let me know if this helped you in implementing template explorer in your organization.

Until we meet next time,

Be courteous. Drive responsibly.

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.