Home > database >  Will assigning a long string to an int stop SSMS processing and prevent a disastrous "naked&quo
Will assigning a long string to an int stop SSMS processing and prevent a disastrous "naked&quo

Time:07-08

Today in SSMS I misplaced my pointer and clicked the Execute button instead of the Database drop-down (they're adjacent on the screen). Fortunately no damage done, but it scared me that I might have executed everything in the current query window since nothing was highlighted. I'd like to put a simple command at the top of any new query window which will stop an F5 type of execution in its tracks. This seemed to work:

UPDATE atable SET intfield = 'freakout prevention' WHERE tablekey = 123
UPDATE atable SET intfield = 55 WHERE tablekey = 123

where intfield is a column defined as an int. Running both lines resulted in

Conversion failed when converting the varchar value 'freakout prevention' to data type int.

Furthermore, the value of intfield was NOT set to 123.

So is this a fairly reliable (I don't need 100.00% here -- just enough to catch the rare time when I accidentally execute a "bare" F5) method of "prefixing" a query window to prevent wild execution when nothing is highlighted and the execute command is given?

CodePudding user response:

If you don't have batches (e.g., GO), then you can put a RETURN as the first line.

If you need to prevent all batches from running, you can put a delay at the beginning of the script. Or you can add a bit of messaging....

I edited this answer to add some extra code to check for an open transaction. This has nothing to do with the original question, but I have found this to be a bigger issue with some developers.

RAISERROR('You ran me by accident.  I will be wait for an hour for you to kill me.', 10, 1) WITH NOWAIT
WHILE (1=1) BEGIN
    WAITFOR DELAY '1:00:00'
    RAISERROR('I''m still waiting.  Please kill me.  Here goes another hour...', 10, 1) WITH NOWAIT
END
GO
RAISERROR('OMG!  Get the backups ready for a restore in production!  Also, update the resume.', 16, 1) WITH NOWAIT
GO
BEGIN TRAN
GO
-- [Updated] Extra check - open transaction
WHILE ( @@TRANCOUNT > 0 ) BEGIN
    RAISERROR('Execution is almost complete; however, a transaction is open.  Please COMMIT or ROLLBACK after you kill me.  Waiting 1 minute...', 10, 1) WITH NOWAIT
    WAITFOR DELAY '0:01:00'
END
RAISERROR('Execution is complete.', 10, 1) WITH NOWAIT

CodePudding user response:

Per the comment, one option is to add set noexec on to the top of the query window. This setting persists across batches. It is evaluated at execution time and can therefore be run conditionally (unlike many other set statements).

As noted by Randy in Marin, this is still not completely safe because the script could contain a set noexec off. If you set noexec on SQL will still execute the set noexec off (obviously, otherwise there wouldn't be a way to turn it off!), and then any subsequent statements would be executed.

Another option - and possibly an even better one - would be set parseonly on.

One difference between the two is that with set parseonly on The engine will literally do only that - parse the syntax. It won't actually do any "work". With set noexec on any statements will still produce plans, but the plans won't be executed. So set parseonly on is "cheaper" than set noexec on.

The other difference is that set parseonly on cannot be executed conditionally. That is to say, a line of code like if (1 = 0) set parseonly on will result in parseonly being set to "on", because the if is evaluated at execution time, but for obvious reasons parseonly is not evaluated at execution time, because that would defeat the point!

And another difference: while parseonly will persist across batches, only one parseonly within each individual batch counts, and it's the last one. For example:

set parseonly on;

select 'hello';

set parseonly off;
go

This will return the result set "hello", because there is a parseonly off in the batch, and it is the last parseonly setting in the batch.

And of course, even with parseonly a similar danger applies: If the script has a set parseonly off, then some statements can still get executed. Not just the statements following, but even other statements that precede it in the same batch, if it is the last setting for parseonly in that batch.

Is there anything else you can do? Yes. Enter sqlcmd. The :on error exit sqlcmd directive will tell sqlcmd to stop executing anything if any kind of error occurs - batch terminating or otherwise.

What we do in our deployment scripts is this:

:on error exit
set xact_abort on;
begin tran;

-- migration script content here

commit;

You can do something similar here to avoid execution of anything in the script in a way that has no danger of being turned off, you can put the query window into sqlcmd mode, and put this at the top:

:on error exit
throw;

-- rest of script here

Now, the throw here won't actually throw an error, since we're not in a catch block. In fact, it is an error to have this throw here. But... that's all we need. The error will cause the :on error exit to terminate all further execution of the file. You could also just have a raiserror (...) instead, but that means more typing :P

Is this now a guaranteed solution? No, because what if you forget to put your window into sqlcmd mode? You can set windows to open in sqlcmd mode by default... but what if you turn it off? The first batch will fail (syntax error since the sqlcmd syntax won't be valid), but subsequent batches will execute.

You can of course combine both methods...

:on error exit
throw;
go
set noexec on;

-- ... rest of script with many batches

But as already described, its still possible for statements to be executed if you are not in sqlcmd mode and there are set noexec off statements anywhere in the script.

  • Related