Catching errors if linked server not responding.

[jjjax64]

We have a stored procedure in sql server 2008 that we need to have it continue running the rest of the code if one step can not connect to a linked sql server. The reason being that we have one of our sql server dedicated to taking orders and would like them to keep being able to do so even if one step, which isn't a requirement, fails because other server is down. We have used the try and catch and some other variations of error handling which work fine if get a normal sql process error like divide by zero and other issues but doesn't seem to work for these other types of problems.

Here is a little snippet from sp.

--If this step fails because linked server is down, how to catch and continue to next steps...

INSERT INTO sqlserver2.production2.dbo.tbl_OrderHeader(
tbloh_Customer,
tbloh_Facility,
tbloh_Option,
tbloh_OrderDate,
tbloh_Memo,
tbloh_UserOrder,
tbloh_Status)
SELECT @Customer,
@Facility,
@Option,
@OrderDate,
@Memo,
'Export',
3

--Continue from here on doing local updates and so on no matter what happens in previous step.

UPDATE tbl_OrderHeader
SET tbloh_TotalCases = Cases,
tbloh_TotalWeight = Weight,
tbloh_TotalCube = Cube
FROM tbl_OrderHeader
LEFT JOIN vwSumCasesWeightCube ON
tbloh_ID = HeaderID
WHERE tbloh_ID = @OrderDeptOrder

Thanks, Joe

 

[starsky51]

Using sp_executesql in the example below allows the sproc to continue past the error. I haven't been able to test this against a remote table, though.

eg.
EXEC sp_executesql N'SELECT *
FROM dbo.MissingTable';
SELECT 1 AS Success;