April 29, 2020 at 8:16 pm
Hi everybody,
We have a policy in our Company that .NET developers are creating builds in VS and DBA' s are running these scripts during upgrades.
Almost all the scripts are transactional and almost every statement in these scripts has On Error Rollback or Commit statements.
One of the build masters started placing :on error exit statement in the beginning of the scripts, that forces me to enable SQLCMD mode in SSMS.
I am also not sure that is clean, because I knew that before we had this additional statement everything was rolled back on any single error. Now I am not sure if :on error exit statement does the same thing, or just rolling back only the last transaction.
Please advice.
Thank you.
April 29, 2020 at 8:33 pm
Have you tested it? Have the developers tested it?
It falls under "it depends", but, similar to working with ROLLBACK or COMMIT on a transaction. If you have something like this:
:ON ERROR EXIT
CREATE TABLE [test1] (id INT)
INSERT INTO test1 (id)
VALUES (1),(2),(3)
SELECT * FROM test1
SELECT 1/0
That whole thing will get rolled back BUT the previous commands still run (ie the table gets created, inserted into, selected from then the error and everything gets rolled back).
BUT if you had something like this:
:ON ERROR EXIT
CREATE TABLE [test1] (id INT)
go
INSERT INTO test1 (id)
VALUES (1),(2),(3)
go
SELECT * FROM test1
go
SELECT 1/0
GO
DROP TABLE [dbo].[test1]
go
it ONLY rolls back to the GO statement prior to the error. so the only thing rolled back is "SELECT 1/0". All commands AFTER the error line do NOT run. So in this case the table is created and populated, but is not dropped.
What I recommend for questions like this is "try it out". I wasn't certain and the google results were not very clear, so tested it out. Just make sure to clean up when you are done (ie if using my scripts, drop test1).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply