Modifying the “New Query” template.

12

June 11, 2015 by Kenneth Fisher

TLDR; Search for and modify the sqlfile.sql file.

During a recent T-SQL Tuesday Boris Hristov (b/t) taught me about SET NOEXEC ON in his post Teaching and Learning. The NOEXEC Thing. When this setting is turned on queries in the current window are compiled but not actually executed. He suggested putting this command at the top of your query window to prevent accidental executions of the entire query. For example when I’m doing some testing I’ll frequently end up having a single query window an assortment of different queries as I research various problems. The number of times I’ve hit Ctrl-E (execute) and executed the whole thing by mistake is astronomical. Because of this I keep trying to remember to put NOEXEC at the top of each query window. And being me I almost always forget. Imagine how helpful it would be to have it show up every time I open a new script.

I always love it when I learn new information in unusual places. Someone today tweeted a link to SQL Sentries top 10 DBA Mistakes: Horror Stories! and since I had some time I decided to listen to it. About half way in Aaron Bertrand (b/t) mentions modifying the new query template in SSMS. The possibility that I could actually manage this and put NOEXEC at the top of each new query excited the heck out of me.

After a few minutes of googling I found this post asking How to set the default query for the SSMS ‘New Query’ button?. Near the bottom is the answer. Modify the sqlfile.sql file. I tried the paths given in the answer and they didn’t work for me so I just did a search on my directory and found it in the following locations.

  • C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
  • C:\Users\kenne_000\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\Sql
  • C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
  • C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

 
Given that I do most of my work with SSMS 2014 these days I modified the first one. Editing this file requires a tool opened as administrator. So I hit shift-right-click on notepad and selected Run as administrator and then opened the file. Next I typed in my code and saved.

NewQuery1

Now I opened a new query in SSMS and

NewQuery2

Woo Hoo! Hopefully this will save me some effort in the future.

In case anyone is wondering the commented out SET NOEXEC OFF was just so I didn’t have to type when I need to turn it back off again.

Edit

Kendra Little (t/b) just suggested this as a piece of code to put at the top.

RAISERROR ('Did you mean to run the whole thing?', 20, 1) WITH LOG;
GO

That way it doesn’t just end but returns an intelligent error. As Kendra said, “Less elegant, but less confusing for a student if they do it by accident”.

12 thoughts on “Modifying the “New Query” template.

  1. […] Studio Online Build Using the DAC with SSMS PowerTip: Read NTFS File System Stream with PowerShell Modifying the “New Query” template. SQL SERVER – The Basics of the Execute Process Task – Notes from the Field #084 Getting File […]

  2. gwbritton says:

    Other thing I do is add a RETURN; after each section (even at the top, instead of SET NOEXEC ON) to avoid running too much if I hit f5

  3. […] (Management Studio). I’ve used Aaron Bertrand’s (b/t) query window color format and modified my new query template. So while I was at it, I finally decided that since I couldn’t find a short cut to open the […]

  4. […] other installers. Now it’s time to set things up. Aaron Bertrand’s (b/t) color scheme, change the New Query template and map a keyboard shortcut to change connection. And of course open up my common windows (Object […]

  5. […] Modify the New Query template I add the statement RETURN at the top of any new query that I open. […]

  6. […] your window or that you are protected by a RETURN or SET EXECUTION OFF at the top of your screen. I have this put in place by default on new query windows. This protects you from running too much code by […]

  7. dallasbikr says:

    You can set the NoExec on in Tools>>Options as well, SSMS 2016, maybe others?

    • Yea, but if I understand correctly that turns it on for the query. The objective here is to only turn it on when you make a mistake and run the entire query instead of just the piece you want.

      In general I like Kendra’s approach best at this point since it actually returns an error that explains what you did. Another option is to put RETURN at the top. It has the same effect but doesn’t make it so you have to change a setting back to run anything.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,151 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: