March 1, 2005 at 4:56 am
Hi,
How can we track Level 16 errors in our SQL queries?
I am creating many INSERT / UPDATE queries dynamically and running them at one time. For every query I am taking care of errors by looking at @@ERROR.
But some Level 16 Errors are not trapped in @@ERROR. I want to know, is there any way we can trap these errors in TSQL.
Below is a simple example for more clarification.
I am creating table -
CREATE TABLE [dbo].[TEST_ERROR] (
[ERRORId] [int] NOT NULL
) ON [PRIMARY]
Now in following script error in second statement is not trapped in @@ERROR, instead SQL stops the execution when error occurs.
Error is - Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'xyz' to a column of data type int.
INSERT TEST_ERROR SELECT 1
IF @@ERROR <> 0 PRINT 'ERROR'
INSERT TEST_ERROR SELECT 'xyz'
IF @@ERROR <> 0
BEGIN
SELECT 'ERROR'
END
INSERT aaaa SELECT 3
IF @@ERROR <> 0 PRINT 'ERROR'
Where as I can trap error related to NULL values properly in script below.
INSERT TEST_ERROR SELECT 1
IF @@ERROR <> 0 PRINT 'ERROR'
INSERT TEST_ERROR SELECT NULL
IF @@ERROR <> 0
BEGIN
SELECT 'ERROR'
END
INSERT aaaa SELECT 3
IF @@ERROR <> 0 PRINT 'ERROR'
Is there any way we can trap these level 16 errors?
Thanks.
--Niranjan
March 4, 2005 at 8:00 am
This was removed by the editor as SPAM
March 5, 2005 at 2:32 pm
Check if it was reflected in SQL Server Error Log. It should. And then you can use master..xp_readerrorlog to check error log for whatever info you need.
March 7, 2005 at 2:31 pm
I had the same problem.but couldn't find a way to trap the error.
Instead you can check the sysobjects if the table exists before creating it and catching the error.
If you really want to catch the error you could use DBCC OUTPUTBUFFER.
Hope would help
March 9, 2005 at 3:18 am
Thanks guys for the replies!
I was also exploring more on this but could not find anything concrete.
The problem here is SQL stops execution of the script when it encounters
the error and hence we can not check anything since next statement is not executed.
If any one finds any other solution please share it.
Thanks again.
--Niranjan
March 9, 2005 at 12:09 pm
two issuses here:
1. Insert must specify field list to work correctly (This is considered a best Practice) Like: insert into tblname (fld2) select 1
2. Al long as you don't have syntax errors you can try with "XACT_ABORT" settings
HTH
* Noel
March 9, 2005 at 10:28 pm
Great!
This is good option when you want to neglect the error encountered
and move on (which is I wanted to do in my case). So I guess it's only in case
of syntax errors we are helpless.
I was wrong when I said "But some Level 16 Errors are not trapped in @@ERROR"
at the start of the thread, in fact if you encounter any syntax error and if
you manually fire SELECT @@ERROR you will get the error number.
It's only because the script stops executing when it encounters something like
type mismatch (syntax error) etc., we can not select @@ERROR.
--Niranjan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply