Impossible to catch error for : Cannot insert duplicate key row in object .......
-
Some types of error cause immediate termination of a batch
For example referencing a non-existent table returns:
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name ‘%d!'.
No further code is executed in that batch, it terminates immediately - the current transaction is not rolled back. @@error is set (to 208 in this example), but no tests for @@error in that batch are valid, and no return status is set.
Need to test for @@error immediately after the point at which the DML is called.
Unfortunately, you can’t easily categorize which errors behave like this - they are generally of severity 16, but not all severity 16 errors are untrappable. Microsoft acknowledges that the behaviour is inconsistent: http://support.microsoft.com/default.aspx?scid=kb;en-us;811034
What you have to do is check for the existance of the table first. See the link for an example.
Dave J
-
Dave, I don't think this is a batch termination problem. James has posted a couple of example that catch the error without any problems.
My thought is there is some sort of coding or logic error in the OP's code. Hopefully the code will get posted and we'll all get some sleep
--------------------
Colt 45 - the original point and click interface
-
What would you expect this to do?
create procedure TestProcedure @id int
as
BEGIN
Declare @price int
SELECT @price = price
FROM Books
WHERE bookid = @id -- Refer to a table that does not exist
IF @@error <> 0
Begin
print 'Error Occurred.'
End
END
go
Exec TestProcedure 10
go
drop procedure TestProcedure
Dave J
-
Um, can't help feeling we're flying in ever-decreasing circles here!
@@ERROR will return a non-zero value if you attempt to insert a duplicate value in a column with a UNIQUE index.
It will also return a non-zero value if you attempt to insert a value in a non-existent table
Do you have any triggers that might be interacting with the INSERT?
David
If it ain't broke, don't fix it...
-
David: that procedure would break and exit the routine prior to the error trap, however if you do the following:
begin
declare @sql_ nvarchar(100)
declare @error_ int
set @sql_ = N'Exec TestProcedure 10'
exec sp_executesql @sql_
set @error_ = @@ERROR
if @error_ <> 0
print 'error in sp'
else
print 'no error in sp'
end
The procedure breaks but you can actually catch the error and handle as needed, though what you would do at that point is beyond me since we have a serious problem
James.
-
The procedure breaks but you can actually catch the error and handle as needed, though what you would do at that point is beyond me since we have a serious problem
Oh absolutely!
The point is that Errors that are level 16 may stop code execution. But some don't!
So if you have a possibility of a Level 16 Error, you need to test the condition before you try to do it.
Hope that makes sense
Dave J
-
David Jackson posted a URL regarding the inconsistent actions by SQL Server for errors.
From the Microsoft site: "In some situations, error handling in a Transact-SQL batch or a stored procedure by using @@ERROR is not possible. After the error occurs, the Transact-SQL batch or the stored procedure is aborted, all uncommitted transactions are not rolled back, and the execution of the statements stops. Because of this behavior in SQL Server, it is not possible to handle all kinds of errors by using @@ERROR in a Transact-SQL batch or a stored procedure."
I recall that which errors terminated the batch were different at SP2 vesus at SP3. That is, some errors that were catchable were no longer catchable.
As a rule, I just include "set xact_abort on" at the start of all stored procedures, triggers or SQL batchs, which causes any error of severity 16 or above to rollback, abort and return to the client.
The reason is that what can you do with an error anyway?
One action is to first rollback and then record in a table the error information. Unfortunately, the error text is not available to SQL, only to the client, so only the error number can be recorded. But the error number is not very useful as the error numbers are generic (2627 always means a primary key violation and the table name is a variable).
Rollback and then let the client do the error actions seems to be a better option and since xact_abort does this automatically, error checking is not needed.
My 2 cents.
SQL = Scarcely Qualifies as a Language
-
I'm having a similar problem. Here's the code:
Declare @sql_stmt nvarchar(250)
Declare @template_name varchar (30)
Declare @table_name varchar (31)
Declare @field_name varchar (30)
set @template_name = 'hpi_oncprostate'
set @field_name = 't_dec'
set @table_name = @template_name + '_'
delete from fields_master Where field_name= @field_name and table_name= @table_name
delete from template_fields where field_name= @field_name and table_name= @table_name
set @sql_stmt = 'ALTER TABLE ' + @table_name + ' drop COLUMN ' + @field_name
exec sp_executesql @sql_stmt
if @@error != 0
print 'error message'
-
jesus,
In what way is your problem similar? You would probably have better results in terms of feedback on your problem if you start a new thread. Also include any error messages that you may be getting.
-
David is correct, there are some errors which cause SQL Server to terminate batches.
Jesus' script is a victim of that. The alter table statment is failing, when it does SQL Server is terminating the batch and nothing else in the batch gets executed so there's no way to capture and respond to the error within the batch. This is one of the reasons I look forward to working with SQL Server 2005, assuming certain errors don't completely terminate batches.
-
set @sql_stmt = 'ALTER TABLE ' + @table_name + ' drop COLUMN ' + @field_name + ' SER @ErrNo = @@ERROR '
Declare @Error int
exec sp_executesql @sql_stmt, N'@ErrNo int OUTPUT', @ErrNo = @Error OUTPUT
if @error 0
print 'error message'
_____________
Code for TallyGenerator -
Wouldn't it also be advisable to capture the return value from sp_executesql?
eg: exec @rtn = sp_executesql etc...
--------------------
Colt 45 - the original point and click interface -
Heh... went to the doctor and said "Doc, if I raise my arm like this... it hurts". Doctor said not to do that anymore
The discussions on error trapping are great (thanks folks), but it seems to me that the OP is programming by exception... throw it against the wall... if it sticks, it stays... if it doesn't, tell someone and then cleanup the mess it made with a rollback. And, like raising your arm the wrong way, it's gonna hurt. So, stop doing it that way!
Why is it that the OP doesn't already know if there's going to be a PK violation? Why isn't there something like maybe a WHERE clause that says "Don't insert items that already exist!"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Old Hand,
Check the table your inserting into for a trigger. If you have an after trigger that fires those commands in the trigger will flush the value in @@error your loking for.
Answer get rid of triggers they are evil.
Enjoy
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply