How can I document on error rollback or commit?

Please see the image and answer its question with proper arguments.
-.bmp
LVL 1
midfdeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, that is because the errors you get abort the whole batch, so the code does actually not continue to run for the second sections...
0
midfdeAuthor Commented:
Where do you know it from please (did I mentioned 'proper argumentation'?)?
In fact I am interested in convincing output about whether the transaction was committed or rolled back and how to make T-SQL work as it is supposed to according to ACID .
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I forgot that. this tech note is for the CATCH TRY, but the extract below applies to your "problem":
http://msdn.microsoft.com/en-us/library/ms175976.aspx

Errors Unaffected by a TRY…CATCH Construct
TRY…CATCH constructs do not trap the following conditions:
....
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

   
Compile errors, such as syntax errors, that prevent a batch from running.
...
These errors are returned to the level that ran the batch, stored procedure, or trigger.


your error is indeed a compile error, and hence make the transaction as a whole is not run (because compilation fails)
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

midfdeAuthor Commented:
>>your error is indeed a compile error
This is a false statement. Query-->Parse (ctrl F5) works fine. It would be a compile error if it had something like "pprint" instead of "print". In this case the query would not even start, but in my case as you can see it succeeds in the previous and subsequent statements.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I can tell you that it "is" a compile error.
the issue is that the DROP and CREATE constraint being in the same batch, it will try to "compile" the CREATE CONSTRAINT while it actually still exists.

try to run the DROP and CREATE in 2 batches.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
but I can understand your problem... "compile" error is eventually not the 100% accurate term. but it's the most appropriate term I could find in the past years for this behavior
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
midfdeAuthor Commented:
Thanks. Conclusion: Check @@error at the beginning of the next batch.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.