Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works:

-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.

I can use the keyboard combination ALT+Q, S to get the same results. How can I update the template so that it references the working combination rather than the deprecated one?

I sometimes go for long stretches between working with stored procedures, and I don't always remember the correct combination. It would be nice not to have to search for it each time this happens.

EDIT: I tried Jon's suggestion of opening up the template from the template explorer. I was surprised to find the correct combination listed in the template. Is there a way to force SSMS to use the correct template?

share|improve this question
    
Ctrl+Shift+M still works for me. What is your keyboard scheme under Tools / Options / Environment / Keyboard? –  Aaron Bertrand Jun 25 '12 at 19:19
    
It's "standard". –  Head of Catering Jun 25 '12 at 19:54
    
Mine is "Standard" also and Ctrl+Shift+M raises the template dialog as it should. Are you possibly in SQLCMD mode? –  Aaron Bertrand Jun 25 '12 at 19:55
    
SQL Server 2012 has a fully customizable keyboard shortcut interface a la Visual Studio, which might allow you to get your UI working the way you expect at least. If you are worried about how your output scripts look to other users who might execute them, have you validated that Alt+Q+S works for them and Ctrl+Shift+M does not? Or is this problem isolated to your machine (in which case, <shrug>)? I think that text is added by the program, not stored in any text file, so I don't think you have any control over it. –  Aaron Bertrand Jun 25 '12 at 20:20

2 Answers 2

up vote 3 down vote accepted

I take it back! Just stop using the Template Explorer. You can edit the files here:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\
  Common7\IDE\SqlWorkbenchProjectItems\Sql\

For example if you edit the file:

Stored Procedure\Create Stored Procedure (New Menu).sql

Then right-click Stored Procedures and select New... and you will see the changes you made. I'm not sure why the Template Explorer exposes that item to you when it's not the one used in the menu. I couldn't figure out why making changes to the template didn't reflect what I was getting when I right-clicked in Object Explorer.

I'll leave the rest of the answer here for posterity. Especially the part about SQL Server 2012, since it handles snippets in a much more intuitive and useful way.


I don'tdidn't believe you cancould interfere with what Management Studio adds to the template when you open them... as far as I cancould tell that comment is injected internally.

But I have a better recommendation anyway: use SQL Server 2012 Management Studio and its new Snippets feature. Some benefits over templates:

  • You can store snippets in a network location, and all of your users can point their custom snippets folder to the same place. This means that if you want to change the template for your stored procedure, let's say, you change it in one place and everyone's next use is up to date - you don't have to distribute an updated template to every single user.
  • No hokey VB6-looking template parameters dialog. You can just use tab to cycle through and replace tokens in the snippet. You should try it out, I can almost guarantee you'll like it better. (Plus you'll get a bunch of other enhancements, such as IntelliSense that actually starts to live up to its name.)

Back in January, I wrote a detailed tip about setting up snippets:

http://www.mssqltips.com/sqlservertip/2589/use-sql-server-code-snippets-to-encourage-consistent-conventions/

share|improve this answer
    
Thanks, Aaron. That worked perfectly. I'll get to 2012 as soon as I finish the project I'm on. –  Head of Catering Jun 26 '12 at 2:47
    
SQL 2012 Management Studio Path to Template to modify is C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Stored Procedure –  user15929 Dec 6 '12 at 15:57

Right-click the template in the Template Explorer and click Edit.

EDIT: If you inspect the path to the template files, they are stored buried in your user profile folder. If your AD profiles are out of sync, or the original machine you installed the management tools had different settings, this could account for the difference. You still should be able to edit the templates, though.

share|improve this answer
    
+1 for what should be the right answer. Unfortunately it didn't work. I've updated my question based on this comment. –  Head of Catering Jun 25 '12 at 19:16
    
Just FYI the edit doesn't help. The templates are indeed stored on disk, but if you go to that location e.g. C:\Users\<username>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql and open any of the templates, the comment that the OP is talking about (which mentions Ctrl+Shift+M) is not stored as part of the template - it is injected by Management Studio when you, say, right-click Stored Procedures and choose New... –  Aaron Bertrand Jun 25 '12 at 21:01
    
@Aaron: Ohhhhhh I see now. If you double-click the template you get the changed version (which is what I tried), but using the New Stored Procedure item you get the original. Weird. –  Jon Seigel Jun 25 '12 at 21:06
    
The nonsense in the template explorer is not the same version of the template that you need to update in order to get different code when right-clicking in Management Studio. The one in template explorer is stored in your profile, and isn't the one that SSMS looks at. Personally I think this is a bug but there's no way it's ever going to get fixed now. –  Aaron Bertrand Jun 25 '12 at 21:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.