SQL Server Performance Forum – Threads Archive
Prevent an script to run by mistakeI’ve created a script that drops a database and re-creates it. To prevent the execution of the script by mistake, I have added the following lines at the top of the script: ‘YOU MUST REMARK THIS LINE TO BE ABLE TO EXECUTE THE SCRIPT’
GO When I run the script, it generates an error message but continues and drops the DB and re-creates it. Why this happens? How can I implement my idea? CanadaDBA
The only way I can think if the database is in use then it may not drop and recreate. And before that you can query against sysdatabases in master in order to check whether the database is present or not.
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= …..
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Remove GO. The execution of a batch is cancelled if an error is encountered in the same. GO creates a new batch. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Or something like.. RAISERROR(‘YOU MUST REMARK THIS LINE TO BE ABLE TO EXECUTE THE SCRIPT’, 16, 1) IF (@@ERROR = 0)
I trid both Gaurav and Chappy suggestions. The script generates the error message and then continues to work. The script is a database script so, it drops the DB and rebuilds all tables, SPs, … Therefor, there are a lot of "GO" commands and this may be the cause. I tried USE the database but it didn’t prevent execution of whole scripts but prevented the drop statement and lots of "already exist…" errors. If there was a command that could HALT the execution immediatly, then I could use Chappy’s code in this way: IF (@@ERROR <> 0)
END Thank guys, CanadaDBA
I should have been clearer perhaps. Dont just paste my script above yours. You need to replace the line
SELECT ‘CONTINUED’ with your snippet of code, so that its inside my BEGIN/END block