?
Solved

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

Posted on 2007-10-02
5
Medium Priority
?
825 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.  
0
Comment
Question by:agcsupport
  • 3
5 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20000441
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 ...
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20000797
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

0
 

Author Comment

by:agcsupport
ID: 20001799
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.
0
 
LVL 18

Accepted Solution

by:
Yveau earned 2000 total points
ID: 20001832
:-) No such trick in SQL2000 ... I'm afraid ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20001976
Glad I could be of any help and thanks for the grade !
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question