|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
@@ERROR HandlingI am trying to log some information to another table when @@Error returns an error. Below is the sample code to see how @@error works. In this sample code, I am purposely failing INSERT INTO TEST command and want to log informaiton in TestLog. Even though there is a error but the insert to TestLog never works. What is the catch here ? /* Create Sample Tables */ Create Table Test (ID Smallint) Create Table TestLog (ID Smallint) /*Fail the Insert statement */ INSERT INTO TEST Values (10, 20) /*This insert should work because error exists. */ IF @@Error <> 0 BEGIN INSERT INTO TESTLOG Values (10, 20) END /* No Records are found*/ Select * from testlog If I run this code in pieces one by one, the code works. When I highlight the entire code and run it, it never makes it to TestLog table. Thanks in advance. Sorry, the second Insert statment was
INSERT INTO TestLog Values (10) Show quote "Mark" wrote: > To All Gurus: > > I am trying to log some information to another table when @@Error returns an > error. Below is the sample code to see how @@error works. In this sample > code, I am purposely failing INSERT INTO TEST command and want to log > informaiton in TestLog. Even though there is a error but the insert to > TestLog never works. What is the catch here ? > > /* Create Sample Tables */ > Create Table Test (ID Smallint) > Create Table TestLog (ID Smallint) > > /*Fail the Insert statement */ > INSERT INTO TEST Values (10, 20) > > /*This insert should work because error exists. */ > IF @@Error <> 0 > BEGIN > INSERT INTO TESTLOG Values (10, 20) > END > > /* No Records are found*/ > Select * from testlog > > If I run this code in pieces one by one, the code works. When I highlight > the entire code and run it, it never makes it to TestLog table. > > Thanks in advance. > > > Mark,
See Erland's articles: http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html HTH Jerry Show quote "Mark" <M***@discussions.microsoft.com> wrote in message news:87927DFA-D785-42B2-BF22-972996A8F852@microsoft.com... > To All Gurus: > > I am trying to log some information to another table when @@Error returns > an > error. Below is the sample code to see how @@error works. In this sample > code, I am purposely failing INSERT INTO TEST command and want to log > informaiton in TestLog. Even though there is a error but the insert to > TestLog never works. What is the catch here ? > > /* Create Sample Tables */ > Create Table Test (ID Smallint) > Create Table TestLog (ID Smallint) > > /*Fail the Insert statement */ > INSERT INTO TEST Values (10, 20) > > /*This insert should work because error exists. */ > IF @@Error <> 0 > BEGIN > INSERT INTO TESTLOG Values (10, 20) > END > > /* No Records are found*/ > Select * from testlog > > If I run this code in pieces one by one, the code works. When I highlight > the entire code and run it, it never makes it to TestLog table. > > Thanks in advance. > > > I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed. Any comments on that one ? Show quote "Mark" wrote: > To All Gurus: > > I am trying to log some information to another table when @@Error returns an > error. Below is the sample code to see how @@error works. In this sample > code, I am purposely failing INSERT INTO TEST command and want to log > informaiton in TestLog. Even though there is a error but the insert to > TestLog never works. What is the catch here ? > > /* Create Sample Tables */ > Create Table Test (ID Smallint) > Create Table TestLog (ID Smallint) > > /*Fail the Insert statement */ > INSERT INTO TEST Values (10, 20) > > /*This insert should work because error exists. */ > IF @@Error <> 0 > BEGIN > INSERT INTO TESTLOG Values (10, 20) > END > > /* No Records are found*/ > Select * from testlog > > If I run this code in pieces one by one, the code works. When I highlight > the entire code and run it, it never makes it to TestLog table. > > Thanks in advance. > > > Mark,
I think it has to do with the type of error you're creating. If you add a CHECK constraint to Test and perform your insert and it violates the insert then the value IS inserted into the TestLog table. HTH Jerry Show quote "Mark" <M***@discussions.microsoft.com> wrote in message news:52B1E1E2-2422-4728-8447-FA4CB25F576E@microsoft.com... >I read the article but it still didint make it clear as to why Insert into > TestLog never happened when the entire code was highlighted and executed. > Any comments on that one ? > > "Mark" wrote: > >> To All Gurus: >> >> I am trying to log some information to another table when @@Error returns >> an >> error. Below is the sample code to see how @@error works. In this sample >> code, I am purposely failing INSERT INTO TEST command and want to log >> informaiton in TestLog. Even though there is a error but the insert to >> TestLog never works. What is the catch here ? >> >> /* Create Sample Tables */ >> Create Table Test (ID Smallint) >> Create Table TestLog (ID Smallint) >> >> /*Fail the Insert statement */ >> INSERT INTO TEST Values (10, 20) >> >> /*This insert should work because error exists. */ >> IF @@Error <> 0 >> BEGIN >> INSERT INTO TESTLOG Values (10, 20) >> END >> >> /* No Records are found*/ >> Select * from testlog >> >> If I run this code in pieces one by one, the code works. When I highlight >> the entire code and run it, it never makes it to TestLog table. >> >> Thanks in advance. >> >> >> Thanks to both Jerry and Aaron. You are right, it has to do with what kind of
insert error it is. It either rollsback the batch or logs the info based on the type of error. I created two scenarions and in one case it works and in other one, it doesnt. Once again thanks to both Show quote "Jerry Spivey" wrote: > Mark, > > I think it has to do with the type of error you're creating. If you add a > CHECK constraint to Test and perform your insert and it violates the insert > then the value IS inserted into the TestLog table. > > HTH > > Jerry > "Mark" <M***@discussions.microsoft.com> wrote in message > news:52B1E1E2-2422-4728-8447-FA4CB25F576E@microsoft.com... > >I read the article but it still didint make it clear as to why Insert into > > TestLog never happened when the entire code was highlighted and executed. > > Any comments on that one ? > > > > "Mark" wrote: > > > >> To All Gurus: > >> > >> I am trying to log some information to another table when @@Error returns > >> an > >> error. Below is the sample code to see how @@error works. In this sample > >> code, I am purposely failing INSERT INTO TEST command and want to log > >> informaiton in TestLog. Even though there is a error but the insert to > >> TestLog never works. What is the catch here ? > >> > >> /* Create Sample Tables */ > >> Create Table Test (ID Smallint) > >> Create Table TestLog (ID Smallint) > >> > >> /*Fail the Insert statement */ > >> INSERT INTO TEST Values (10, 20) > >> > >> /*This insert should work because error exists. */ > >> IF @@Error <> 0 > >> BEGIN > >> INSERT INTO TESTLOG Values (10, 20) > >> END > >> > >> /* No Records are found*/ > >> Select * from testlog > >> > >> If I run this code in pieces one by one, the code works. When I highlight > >> the entire code and run it, it never makes it to TestLog table. > >> > >> Thanks in advance. > >> > >> > >> > > > Mark (M***@discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what As noted in my article, an error can lead to termination on three > kind of insert error it is. It either rollsback the batch or logs the > info based on the type of error. I created two scenarions and in one > case it works and in other one, it doesnt. different levels: 1) Statement 2) Scope 3) Bacth. Your error was of the second kind. Scope-aborting errors are, as far as I know, always compilation errors. What is tricky, is that due to deferred named resolution, compilation errors can happen at run-time. Consider: Create Table Test (ID Smallint) Create Table TestLog (ID Smallint) --go print 'Hello!' /*Fail the Insert statement */ INSERT INTO Test Values (10, 20) /*This insert should work because error exists. */ IF @@Error <> 0 BEGIN INSERT INTO TestLog Values (10) END go SELECT * FROM TestLog This script will print Hello!, but if you uncomment the go, it will not. This is because in the script as it stands, Test does not exist, so SQL Server defers compilation of that statement. If you uncomment the go, the tables exists when the batch is compiled, and thus you get the error directly, so execution never starts. Finally, note that if you write: INSERT INTO Test(ID) Values(10, 20) the batch always fails to compile, as SQL Server does not need to know the table definition. Incidently, most people agree that INSERT without a column list is not good practice. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a multi-batch technique inside of an object (e.g. stored procedure). Show quote "Mark" <M***@discussions.microsoft.com> wrote in message news:52B1E1E2-2422-4728-8447-FA4CB25F576E@microsoft.com... >I read the article but it still didint make it clear as to why Insert into > TestLog never happened when the entire code was highlighted and executed. > Any comments on that one ? > > "Mark" wrote: > >> To All Gurus: >> >> I am trying to log some information to another table when @@Error returns >> an >> error. Below is the sample code to see how @@error works. In this sample >> code, I am purposely failing INSERT INTO TEST command and want to log >> informaiton in TestLog. Even though there is a error but the insert to >> TestLog never works. What is the catch here ? >> >> /* Create Sample Tables */ >> Create Table Test (ID Smallint) >> Create Table TestLog (ID Smallint) >> >> /*Fail the Insert statement */ >> INSERT INTO TEST Values (10, 20) >> >> /*This insert should work because error exists. */ >> IF @@Error <> 0 >> BEGIN >> INSERT INTO TESTLOG Values (10, 20) >> END >> >> /* No Records are found*/ >> Select * from testlog >> >> If I run this code in pieces one by one, the code works. When I highlight >> the entire code and run it, it never makes it to TestLog table. >> >> Thanks in advance. >> >> >>  
Other interesting topics
|
|||||||||||||||||||||||
