October 8, 2009 at 4:45 am
Is there a way to catch @@ERROR = 1222 but to suppress the fact that it has happened so calling routines don't know that the stored procedure call has errored.
Procedure is called from SSIS and chooses to branch to error route if procedure encounters a locked record. I catch the error in the procedure so I don't want it passed to SSIS.
October 9, 2009 at 12:37 am
I'm not sure whether you're already doing it or not, but you can use the try...catch block to achieve this. Below is a small sample.
CREATE PROC testerror
AS
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT 'exception'
END CATCH
you can utilize the error related functions to get error specific information and process the error accordingly in the catch block. Below are some of the functions that give you this information
ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_LINE()
ERROR_PROCEDURE()
Thanks,
Dhimant
October 9, 2009 at 2:22 am
This indeed would be a solution if I was using 2005!
October 10, 2009 at 1:08 am
Kelvin Phayre (10/9/2009)
This indeed would be a solution if I was using 2005!
Well if you are using SQL 2000 then in that case I believe the @@ERROR can be of use. The only problem with that is that you have to check for the error after every error prone statement.
Thanks,
Dhimant
October 10, 2009 at 10:00 am
@@error is your only way to do this in SQL 2000. Error trapping was fairly simplistic.
October 11, 2009 at 4:30 am
I don't want to sound ungrateful for your help but if you look at my post you will see that I am using @@ERROR. My problem is that when I trap the error it is also interpreted by SIS as a failure. I want SSIS not to see this error.
October 11, 2009 at 10:31 am
SSIS is not available in SQL 2000. Are you using SQL 2000 with DTS or SQL 2005 with SSIS?
If it's DTS, I think you're stuck with this error. What you could do, is add something in your flow that if this is the error trapped, send a note to the admin(s) so they know what happened without having to dig through the logs. If it's common, then you know to ignore it, or handle it another way.
October 11, 2009 at 10:32 am
The other thing you could try to do is pre-trap the error. If you know it's a particular type of data, then look for that data before you process it in the flow and either eliminate it, or don't pull it in.
October 11, 2009 at 2:05 pm
The most common error 1222 is the infamous "lockout time exceeded message". This is not so much a SQL error as a "connection-type" error, meaning - it will pop up any old time your process runs into a block over a remote connection, and the connection doesn't see the block be resolved within the LOCKOUT_TIMEOUT period.
You might be able to reduce the errors by increasing your connection timeout setting, but in general, you need to find what is blocking you, and find a better way make the two requests work around each other.
----------------------------------------------------------------------------------
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply