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.
Now I opened a new query in SSMS and
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.
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, SSMS 
