We help IT Professionals succeed at work.

How to capture error in stored procedure and continue with the rest

846 Views
Last Modified: 2012-05-05
I have many remote SQL Servers that I need to check for successful backups.  I have created Linked Servers to all of them.  I have a stored procedure that runs and checks the MSDB.dbo.sysjobhistory for successful back ups.  My problem is if one of those server is turned off or I have a connectivity problem to one of the the stored procedure halts and it does not continue.  I get the following "Could not find server '10.1.1.40' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.........".  If one of the servers is turned off I would like to at least know the results of the other ones.  How can I capture the fact that one of the servers is not online and continue with stored procedure to capture my results.  
Comment
Watch Question

Top Expert 2007

Commented:
Use this construction:

BEGIN TRY
     < sql_statement >
END TRY
BEGIN CATCH
     < sql_statement >
END CATCH

If something in the TRY section goes wrong, the focus is switched to the CATCH section. Just report an error there and the loop can go to the next server. So make sure to do the TRY CATCH for every server !!!

Hope this helps ...
CREATE PROCEDURE spLogError
AS

--Return error details to calling application
SELECT
    ERROR_NUMBER() AS ErrNum,
    ERROR_SEVERITY() AS ErrSev,
    ERROR_STATE() as ErrState,
    ERROR_PROCEDURE() as ErrProc,
    ERROR_LINE() as ErrLine,
    ERROR_MESSAGE() as ErrMsg

--Log error to error databases
INSERT INTO SQLErrors.dbo.ErrorLog
VALUES(ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(),ERROR_MESSAGE())

This procedure can then be called in your Catch block to return and log the error details.

BEGIN TRY
--INSERT CODE HERE: When an error occurs, control will be passed to the Catch block
END TRY
BEGIN CATCH
--This proc will log the error and send the details back to the calling application
EXEC spLogError
END CATCH

Author

Commented:
Sorry, I said SQL Server 2005 by accident.  I have SQL 2000 on my server.  I think TRY/CATCH is new to SQL 2005.  Is there a way to do this in SQL Server 2000?  Sorry for the confusion.  Thanks.
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2007

Commented:
Glad I could be of any help and thanks for the grade !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.