Impossible to catch error for : Cannot insert duplicate key row in object .......
-
SQL Server 2000 SP4
Hello,
Suppose to have the following statement:
1 example:
EXEC @return = sp_executesql @SQLCommand
If @SQLCommand has a string (insert statement) like: INSERT...
and I have a duplicate value for a constraint during inert operation, SQL shows the error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'TableName' with unique index 'TableName_TableColumns'.
The statement has been terminated.
and the @return = 0 (should be <> 0, but is =0 and I cannot catch the error for error management).
2 example:
In the case @SQLcommand = 'SELECT * FROM tablename', but tablename does not exist, SQL show the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tablename'
and @return <>0 so it is possible to catch the error for error management.
In the case of some error (like the previous one: Server: Msg 2601, Level 14, State 3, Line 1), how can I catch the error and show the error:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'TableName' with unique index 'IX_TableName_TableColumns'.
The statement has been terminated.
???????????
It looks like a SQL bugs!
Thank to everybody for useful informations.
-
Test @@ERROR instead of the return code. For example the following will error on the second insert command and print an error message:
--create test table with unique column
if object_id('t') is not null drop table t
create table t (col1 int not null, primary key (col1))
--Run a dynamic sql to insert a value and test result
declare @sql nvarchar(100)
set @sql = 'insert into t values (1)'
exec sp_executesql @sql
if @@error <> 0
print 'yep error'
else
print 'no error'
--do it again, so we get the duplicate key value error
set @sql = 'insert into t values (1)'
exec sp_executesql @sql
if @@error <> 0
print 'yep error'
else
print 'no error'
--James
-
Hello,
using if @@error <> 0 I do not catch any error.
Please,
anyone can help me?
Thank
-
Are you checking for @@error inside of the SP?
-
Hello,
using @@error is not possible to catch the error.
Any help will be very appreciated
Thank
-
I catch every possible error inside my procedures using @@ERROR.
Your statement just is not true.
You are obviously doing something wrong.
Check your syntax.
_____________
Code for TallyGenerator -
Post the complete SP text and put a comment above the line where you can't seem to catch the error. @@error needs to be captured IMMEDIATELY after the call to sp_executesql. If it is not the FIRST line after that call then @@ERROR will have the value of the last executed statement. I'm afraid complaining that it doesn't work is not going to help, you will need to post the code.
James.
-
Hi
From BOL
"Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later."
Any statement, including testing or assigning @@ERROR to a variable will also reset the value of @@ERROR
Check that you are trapping the error code from the correct statement as the first action immediately after the statement
David
If it ain't broke, don't fix it...
-
Thank everybody,
the problem is:
for example 1 I do not catch the error : it is a INSERT statement that produce an error because of duplicate key row.
for example 2, it is a SELECT on a table that does not exist, here in this case the error is catched.
So it looks like that in the case of :
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'TableName' with unique index 'TableName_TableColumns'.
The statement has been terminated.
the error is not catched, I mean I cannot handling the error to manage it.
I' ll make an example just to verify both cases.
About @@error, I know that is reset after statement, I use always a variable: @err= @@error
Thank
-
Well until you post some actual code, I've done all I can do. In my first response to you I posted code which specifically catches duplicate key insert errors using sp_executesql. I just added the following to that code:
set @sql = 'select * from t1'
exec sp_executesql @sql
if @@error <> 0
print 'yep error'
else
print 'no error'
which tested a select against a non-existent table and again it works fine. So I'm not sure what you mean when you say you can not catch/handle the error.
Good luck, hopefully someone can help you.
James.
-
Hello,
if you change
set @sql = 'select * from t1'
with a statement like:
set @sql = 'INSERT....' and you have a duplicate key,
the error is not catched, I mean the code goes on witout stopping because @@error is 0 .
-
You are correct, that is not an error. All you have done is assign a string value to a variable. It is not until you execute the sql contained in the string/variable that an error occurs. There is NO WAY to trap an error that "will happen". If you execute the sql with sp_executesql @sql you can then trap the error.
James.
-
I have used sp_executesql, as written at the beginning (first message).
Anyway later I'll give you a piece of code.
-
Should be
EXEC @return = sp_executesql @SQLCommand
SET @ErrNo = @@Error
IF @return 0 OR @ErrNo 0
GOTO ErrorHandler
_____________
Code for TallyGenerator -
I don't know about anyone else, but I'm eagerly awaiting this piece of code.
Please post it quickly.
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply