Linked Server - On Connection Error Resume

Hot Topic

SELECT in a table takes FOREVER
Storing images in sql server 2005
saving DTS package to sql server
Enterprise Or Express Edition?
Getting Started with SQL 2005 CTP
How to add SQL statements to the package created by Export Data wizard?
Virtual Labs - No Connection message
OLE DB Provider - Saving SQL UserName and Password error
Error: Assembly is not marked as Serilizable
Is this a silly way to use SSIS?

SQL Server

Solution Explorer in SQL Server Management Studio
Excel connection properities -
Missing image in PDF Export version
Windows 2003 SP1 Comaptibility with SQL Server 2000 (SP3)
SQL Server 2005 Service Pack 1 is now available
Adventureworks 2005
Calculated member for Grand Total...
Grouping by custom datepart for shift patterns
June CTP - Multi Value Parameter
SSIS will not start after SQL Server 2005 SP1 upgrade

Linked Server - On Connection Error Resume

I have a stored procedure which connects to multiple linked servers.  If one connection fails, the stored procedure ends with error.

Is it possible to have the stored proc continue instead of quit

I have tried the following and none work:
- connect in a different sp
- use sp_executesql

Thanks

Oh yeah...I am using SQL SERVER 2000 SP4 and I know SQL SERVER 2005 has a sp to test a linked server, but I cannot upgrade. 

Answer this question

Linked Server - On Connection Error Resume

  • mdickin

    I once had the problem to check for the availbility of linked server. The solution (in our case) was to ping the servers on a regular basis, by using a SQL Server Agent job, which did something like

    --INSERT INTO PingTable
    --SomeSelectonTheServer, mainly something like SELECT 1, or SELECT * FROM dual (in this case for Oracle)
    --UPDATE PingTable, update the status of the ping record to successfull.

    Therefore we could query the table for the successfull connectivity, if the column Successfull was not filed or updated properly we threw a manual error.

    HTH, jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
  • Linked Server - On Connection Error Resume
Answer this question