How to track Level 16 errors in SQL scripts?

Question

How to track Level 16 errors in SQL scripts?

Niranjan Shaiwale

SSChasing Mays

Points: 627
More actions
March 1, 2005 at 4:56 am

#164791

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

barsuk SSChampion Points: 12280 More actions · Answer 1

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.

rochana Newbie Points: 4 More actions · Answer 2

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

Niranjan Shaiwale SSChasing Mays Points: 627 More actions · Answer 3

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

noeld SSC Guru Points: 96590 More actions · Answer 4

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

Niranjan Shaiwale SSChasing Mays Points: 627 More actions · Answer 5

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