November 21, 2008 at 3:26 pm
Hi everyone:
I am writing a stored procedure en SQL Server 2000, this procedure has one INSERT statement that can fail by violating a primary key of the table it is inserting data to. I am catching @@Error value, but this does not prevent SQL from raising the exception to the caller. I would like the exception not to be raised because I am handling it into the stored procedure itself and when this stored procedure is called from a DTS, this raised exceptions cause the DTS to stop...
Any hints will be appreciated!
November 21, 2008 at 3:39 pm
How is the stored procedure inserting the data? Is it row by row or from another table?
November 22, 2008 at 8:33 am
Hi:
I am doing it row by row, but if inserting in a batch is better I would give it a try.
Thanks
November 22, 2008 at 10:05 am
Can you post the code for your stored procedure?
November 24, 2008 at 7:25 am
Ok, I just changed some columns and table names for secutiry reasons:
DECLARE CURSOR_TEMP CURSOR FOR
SELECT Key1, Key2, Data1
FROM INT_Table
OPEN CURSOR_TEMP
FETCH NEXT FROM CURSOR_TEMP
INTO @Key1, @Key2, @Data1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ERROR = 0
INSERT INTO [INT_Table2](Key1, Key2, data1)
VALUES(@Key1, @Key2, @Data1)
SET @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
PRINT 'ERROR:' + CAST(@ERROR AS VARCHAR)
GOTO CONTROL_ERROR
END
CONTROL_ERROR:
-- Here is where I handle the error but the error is still raised and the DTS aborts and reports the task as failed.
IF @ERROR <> 0
BEGIN
UPDATE INT_Table
SET
MESSAGE = ''
WHERE
Key1 = @Key1 AND Key2 = @Key2
END
FETCH NEXT FROM CURSOR_TEMP
INTO @Key1, @Key2, @Data1
END
CLOSE CURSOR_TEMP
DEALLOCATE CURSOR_TEMP
Thanks for any help.
By now I would do it the old way, check if there is another row into the table with the same primary key values before inserting it.
November 24, 2008 at 8:35 am
As you are pulling the data from a table, I'm at a loss as to why you are using a cursor for this process. Below is the code I would use to replace the code currently in your stored procedure.
UPDATE INT_Table2 SET
MESSAGE = ''
FROM
INT_Table2 t2
INNER JOIN INT_Table t1
on (t2.Key1 = t1.Key1
t2.Key2 = t1.Key2)
INSERT INTO INT_Table2 (Key1, Key2, Data1)
SELECT
t1.Key1,
t1.Key2,
t1.Data1
FROM
INT_Table t1
LEFT OUTER JOIN INT_Table2 t2
ON (t1.Key1 = t2.Key1
AND t1.Key2 = t2.Key2)
WHERE
t2.Key1 IS NULL
No cursor, no error handling needed. Test as I have no test tables or data to do that myself.
November 28, 2008 at 12:00 pm
Thanks for the reply, even it did not answer my original question it help me somehow.
Good way to solve the problem.
November 28, 2008 at 12:40 pm
If you need an answer on your question here it is:
"There is no way".
If you then want to know how to do things properly in SQL read above.
_____________
Code for TallyGenerator
November 28, 2008 at 1:07 pm
Jportelas (11/28/2008)
Thanks for the reply, even it did not answer my original question it help me somehow.Good way to solve the problem.
To take what Sergiy said and expand a little, there is no way in SQL Server 2000. If you were running SQL Server 2005 you could write it using try/catch blocks, or SQL Server 2008 you could use the new MERGE statement.
Problem is, you were using a RBAR (Row By Agonizing Row) solution where a set based solution was available.
November 28, 2008 at 1:14 pm
Just so we answer the original question - use the RETURN statement to return the status back to the caller.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2008 at 1:31 pm
Jeffrey Williams (11/28/2008)
Just so we answer the original question - use the RETURN statement to return the status back to the caller.
That doesn't really answer the question. He wanted to trap and handle the error in the stored procedure AND not have it returned to the caller.
November 28, 2008 at 1:37 pm
Sure it does - handle the error and RETURN 0 to the caller. Stored procedure will return to caller a status of successful completion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2008 at 1:43 pm
Jeffrey Williams (11/28/2008)
Sure it does - handle the error and RETURN 0 to the caller. Stored procedure will return to caller a status of successful completion.
Did you read OP?
I would like the exception not to be raised because I am handling it into the stored procedure itself and when this stored procedure is called from a DTS, this raised exceptions cause the DTS to stop...
Errors of such severity will ALWAYS stop execution.
End procedure will never come to RETURN. Such errors stop execution immediately and return error codes.
But it does not help OP - he wants it does not stop but go ahead with error message.
_____________
Code for TallyGenerator
November 28, 2008 at 7:47 pm
Even with the 2005 TRY/CATCH, you can't prevent EVERY error from bubbling back up. Some failures are so egregious - they're going to bust you up and that's it.
You can get pretty far by having TRY/CATCH in both the calling code layer (the code being sent by the client) and the in code being called, but even then, some things will get through....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2008 at 7:53 pm
Thanks Sergyi, Matt and Jeffrey, so there is no way to achieve this in T-SQL for SQL Server 2000?
Seems like trying to cover all the failure scenarios is the only way.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply