March 31, 2016 at 8:31 am
I have a stored procedure that queries data from several linked servers and inserts the data into a local table. The problem is, if one of the linked servers is down, the procedure fails and no data from any of the linked servers gets inserted into the local table. Is there a way to tell the procedure to just ignore the timeout error for the linked server that is down and go ahead and commit the rest of the data? Thanks.
March 31, 2016 at 8:33 am
ApologetixFan (3/31/2016)
I have a stored procedure that queries data from several linked servers and inserts the data into a local table. The problem is, if one of the linked servers is down, the procedure fails and no data from any of the linked servers gets inserted into the local table. Is there a way to tell the procedure to just ignore the timeout error for the linked server that is down and go ahead and commit the rest of the data? Thanks.
Well we can't see your code so it nearly impossible to say with any certainty. Is this a single query or multiple queries? You might be able to utilize a TRY/CATCH if it is multiple queries. But if this is a single query you can't have it partially work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 31, 2016 at 8:43 am
ALTER PROCEDURE [dbo].[GetLogShippingStatus]
WITH
EXECUTE AS CALLER
AS
DECLARE @pErr varchar(256);
DECLARE @pErrVal int;
DECLARE @pTargetDate datetime2;
BEGIN TRY
INSERT INTO dbo.LogShippingStatus
SELECT
primary_server,
primary_database,
backup_threshold,
last_backup_date,
NULL,
CASE WHEN (DATEDIFF(n,last_backup_date_utc,getutcdate()) <= backup_threshold) THEN 1 ELSE 0 END
FROM SERVER1.msdb.dbo.log_shipping_monitor_primary;
END TRY
BEGIN CATCH
SET @pErr = ERROR_MESSAGE();
RAISERROR (@pErr, 10, 1);
END CATCH;
BEGIN TRY
INSERT INTO DBATools.dbo.LogShippingStatus
SELECT
secondary_server,
secondary_database,
restore_threshold,
NULL,
last_restored_date,
CASE WHEN (DATEDIFF(n,last_restored_date_utc,getutcdate()) <= restore_threshold) THEN 1 ELSE 0 END
FROM SERVER2.msdb.dbo.log_shipping_monitor_secondary;
END TRY
BEGIN CATCH
SET @pErr = ERROR_MESSAGE();
RAISERROR (@pErr, 10, 1);
END CATCH;
GO
[font="Courier New"]OLE DB provider "SQLNCLI10" for linked server "SERVER2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SERVER2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].[/font]
If SERVER2 is down, nothing gets inserted into dbo.LogShippingStatus.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply