When developing T-SQL code, I frequently have a number of stored procedures created and run inside one .sql file. While I’m generally good at highlighting the single statement I want to run, there are some situations where the highlight is misplaced – usually by me getting complacent with the lack of lag on Remote Desktop. Suddenly I’ll get a dose of lag, my highlight command (shift-uparrow) won’t be processed, but my Execute (F5) command will.
To defend against this, I determined that the best option would be to create a severe error at the top of the .sql file to ensure that execution would cease. Unfortunately, this only stops the execution of the current batch, and as CREATE PROCEDURE must be at the start of the batch, I have many batches in my code.
Five minutes after requesting suggestions from Twitter, Rob Farley suggested using SQLCMD mode – which is a feature that I haven’t used very often. This is activated in Management Studio via the “Query†| “SQLCMD Mode†menu item, and can be specified as the default mode for all new query windows.
Placing the following code at the top of the file results in a fatal error that completely halts execution:
:on error exit SELECT * FROM SomeTableThatDoesntExist GO !!dir GO
The !!dir command will list the contents of the current directory. You can run this line by itself, but this line won’t be executed if you run the entire .sql file.
The drawback to this method is that you lose Intellisense in SQL Server 2008 and the ability to debug your code, but I’m willing to live with this to prevent having to comment out my more dangerous statements!
Hi Jim.
Is there a possibility that you can rollback a transaction/whole script when an error occurs while using SQLCMD?
I use this. Infinite loop, Uses a tiny little bit of server resources, but you’ll notice and stop the execution asap anyway and you don’t have to sacrifice any features.
– Security code
select ‘SAFETY SWTICH’
while 1=1 print ‘SAFETY SWTICH’
—————–
Thanks, Emil – that’s a great idea. You could always put a WAITFOR DELAY ’00:00:01′ in the loop to slow down the impact of the print statement.
[...] Oops, didn’t mean to run the entire script… – A tip on how to avoid a common SQL Gotcha here. [...]