August 17, 2009 at 2:02 pm
Hi,
We have a stored procedure that "partially" ran the other day. Here is the top part and a description of the problem.
This stored procedure selects a bunch of order numbers into a table to be used later for archiving. The entire stored procedure is wrapped nicely in a transaction.
The very first SELECT * failed because a previous run left this "keystoarchive" table in the database and a "SELECT * INTO" expects to be able to create the table.
With my limited knowledge I understand that this error would occur but the I read the code below that checks the error code and nicely sends control to a Rollback routine and then out of the stored procedure with no harm done - wrong. Unfortunately it continues running through the stored procedure and produces unexpected results.
Is there any way to trap this error? There clearly is an error when the SELECT statement executes but it doesn't seem to set a value in @@error.
Sorry - I'm a .NET developer and was asked to debug this. Any help would be great.
BEGIN TRANSACTION OrderArchive
SAVE TRANSACTION OrderArchive_save
-- prep for archiving
select * into keystoarchive from RSTOP_ORDER where ORDER_ID in
(select ORDER_ID from ORDERS where ORDER_DATE < @archive_before_date)
if @@error != 0
GOTO _ROLLBACK
August 17, 2009 at 2:21 pm
I've had this explained to me before but as usual have forgotten the reason and what causes this and am unable to find any relevant material...I think this happens because the batch is aborted due to the error and @@ERROR is not populated with the error number (I might be wrong)
Since you've posted in the SQL 2005 forum I'm assuming you're using SQL 2005 - in that case you can use TRY...CATCH and catch this one...
Sample code attached - the first sample is using @@ERROR which doesn't work...run this twice - it will work the first time (as the table #syscolstemp doesn't exist) and it will ignore the @@ERROR when the error occurs the second time
--DROP TABLE #syscolstemp
DECLARE @Err int
SELECT * INTO #syscolstemp
FROM sys.columns
IF @@ERROR 0
BEGIN
PRINT 'error occured - caught via @@ERROR'
END
ELSE
BEGIN
PRINT 'Proceed'
END
PRINT 'Proceeding'
GO
This one uses TRY...CATCH and catches the error properly
DECLARE @ErrNum int
--DROP TABLE #syscolstemp
BEGIN TRY
SELECT * INTO #syscolstemp
FROM sys.columns
PRINT 'Proceed'
END TRY
BEGIN CATCH
SELECT @ErrNum = ERROR_NUMBER()
PRINT CAST(@ErrNum As Varchar(30))
PRINT 'caught error in catch block'
-- do whatever is to be done here
END CATCH
PRINT 'Proceeding'
August 17, 2009 at 3:06 pm
Yes it was orginally written for SQL2000 and now running in 2005. You are right, I can modify this using TRY/CATCH and will be much happier if I do I guess. I just thought it was weird the way this was handled.
It looked like the original coder had prepared for the "unexpected" and created/used transaction logic and even tested for an error condition and rolled back. Then one day that table didn't get deleted!!
thanks for your help
August 17, 2009 at 3:23 pm
I am puzzled by your statement that the procedure continues to run, producing unexpected results. In the absence of try/catch logic, when I force the same 2714 message it crashes out of the procedure below:
--
use sandbox
CREATE PROCEDURE dbo.errTrap
AS
BEGIN
SET NOCOUNT ON;
declare @err int
select *
into Sample
from dbo.Tally
set @err = @@ERROR
if @err != 0 select @err, 'procedure proceeded past query'
END
GO
--
While converting to try/catch logic, you might also consider
1) making the table you insert into a #temporary table, so it will not persist past the end of the procedure, or
2) testing for the existence of your table and dropping it before trying your select into
--
if object_id(N'dbo.Sample') is not null drop table dbo.Sample
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply