Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WoodHouse
Posting Yak Master
211 Posts |
 Posted - 2010-01-21 : 03:08:18
|
| Due to some reason, SQL Server is giving two different error message if you run the below scriptBut @@error is having the latest error number which is not useful, Is there any way to get the previous error if sql server is giving more than one error while executing single statement Sample script-------------drop table authorsgoCREATE TABLE Authors (AuthorID INT NOT NULL PRIMARY KEY,Name VARCHAR(100) NOT NULL)GOALTER TABLE AuthorsADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)Select @@errorGOwill display the error number 1750, but need to catch the error number 1779 in the error log table. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 03:20:06
|
"Is there any way to get the previous error if sql server is giving more than one error while executing single statement"No, @@error refers to the immediately preceding statement only. It is reset when the next statement is executed. You can store its value into a local variable (e.g. SET @MyError1 = @@ERROR), but that won't "survive" beyond the next "GO".If you have a long DDL script and want to "stop" it if something goes wrong, the only way I have come across is to have any error (i.e. that you trap) insert a row in a "debug" table, and then check for that row as part of every statement.Either that, or use dynamic SQL to perform each CREATE action, and monitor the return code from that.We just take a backup, run the script, and if it fails we restore the DB ... too much work to try to catch and handle each individual CREATE statement (although we do do them in TRANSACTION blocks, which sometimes works - and sometimes SQL does an implicit ROLLBACK and then the rest of the script in that block is run OUTSIDE the Transaction block  ) |
 |
|
|
|
|
|
|
|
Error