SQL Server: SET NOEXEC ON prevent the accidently execution of entire SQL script

I just recently got to know about the SET NOEXEC option of SQL Server which is available since SQL Server 7.0.

As a DBA or DB Developer, we are preparing a database change script with all the database changes that we have made earlier and that SQL script needs to be executed on different database servers like development server, validation server, production server.

An earlier, I faced an issue like accidently DB developer or system admin executed entire SQL Script. The script was tested very well and put all the commands in the sequence, but then also it needs to be review before actual execution especially in production server.

SQL Server Stop Script Execution

You can disable the execution of your batch script by adding SET NOEXEC ON in your SQL script.

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

Let me demonstrate this,

Execute below few prints:

Now execute same prints after SET NOEXEC to ON:
You cannot see any of the print messages.

Now execute below batch:
You can see last print message which is after SET NOEXEC to OFF.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of