How do I prevent linked server errors?

Many of us use systems where multiple SQL Servers need to talk to each other over the network. But nobody's network is perfect. Sometimes the linked server is down, sometimes the link between them is down, sometimes another user changed the login credentials. Whatever the case, you need to be prepared in the event that the server is not available.

In SQL Server 2000, you will see this:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

In SQL Server 2005, you get a much more useful error message:

OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].

--or

OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.

--or

OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

--or

OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Unable to complete login process due to delay in opening server connection".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "linked_server_name".

--or

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

--or

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Most of these errors could be due to a misconfigured linked server, but they could also be due to network or hardware issues. Of course, these errors abort the entire batch, so whether or not the rest of your code requires the remote server to be active, it still terminates. In SQL Server 2000, you cannot trap for this type of error using @@ERROR, ROLLBACK, etc.

There are some workarounds, thankfully.

PING: the knee-jerk reaction

A very common suggestion to avoid this pitfall is to use PING from a command line; for example:

CREATE TABLE #foo
(
pingResult SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'ping linked_server_name';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

DROP TABLE #foo;

There are several problems with this approach:

In SQL Server 2005, xp_cmdshell is disabled by default. You will get the following error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

(See SQL 2005 Article #29 for more details.)

When you get past that, you might see the following:

OLE DB provider "SQLNCLI" for linked server "linked_server_name" returned message "Unable to complete login process due to delay in opening server connection".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "linked_server_name".

This one I have not resolved yet—I am in the middle of configuring a brand new set of SQL Server 2005 machines, and the fact that they are virtual servers using VMWare might have something to do with it. I am finding that linked servers in 2005 only stay "initialized" for a short period of time, then become idle. At which case, they require some sort of "prodding" in order to be useful again. My workaround for now is to set up a scheduled job, that runs every minute, and runs a single one-row SELECT against each linked server. This is a hack workaround but for now is the only way I can keep the linked servers running reliably. I am hoping to get a more official answer from Microsoft...

The linked server name might not be one that Windows understands. For example, it could be an alias for the actual network name or IP address, in which case the alias name might not be understood by WIndows or, even worse, the alias name might be used to maintain compatibility even though a server has been renamed. So, for all you know, you could be pinging the wrong box.
Ping and other ICMP traffic may be blocked by any firewall or router between the caller and the callee. This is a very common scenario in servers or data centers exposed to the Internet (go ahead, try pinging www.microsoft.com).
Even if your ping request gets through and is successful, this does not necessarily mean that you will be able to communicate with SQL Server via the port SQL Server is listening on (1433/1434 by default). The port may be disabled, or blocked by software (e.g. ZoneAlarm) or even hardware (router or firewall). It also does not necessarily mean that the SQL Server instance you are trying to connect to is actually running. Nor does it guarantee that your linked server credentials are (still) valid.

A Better Approach

A more reliable way to determine if a SQL Server is up is to actually try to connect to it. As opposed to calling it directly, you can capture any errors by shelling out to osql instead of querying the server directly (e.g. using four-part naming). Note however that this will take 10 seconds to timeout if the server cannot be reached.

SET NOCOUNT ON;

CREATE TABLE #foo
(
dbname SYSNAME NULL
);

INSERT #foo
EXEC master..xp_cmdshell
'osql
-S<server_name>\<instance_name>
-dMaster
-U<user_name>
-P<password>
-Q"SELECT Name FROM sysdatabases"';

IF EXISTS
(
SELECT 1
FROM #foo
WHERE LTRIM(RTRIM(dbname)) = N'master'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END

DROP TABLE #foo;

In SQL Server 2005, first, you have to enable xp_cmdshell, otherwise you will see this error:

To enable xp_cmdshell, run the following code:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

A little more elaborate sample for SQL Server 2000 or SQL Server 2005 uses SQL-DMO. See usp_serverup.

In SQL Server 2005, you have some new options. You can use the new system procedure sp_testlinkedserver within the new TRY/CATCH construct:

BEGIN TRY
EXEC sp_testlinkedserver N'linked_server_name';
END TRY
BEGIN CATCH
PRINT 'Linked Server not available';
ROLLBACK;
RETURN;
END CATCH

-- continue with usage of linked server

This will still raise warning messages, but it will at least let you get out of the batch on your own terms.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I prevent linked server errors?

Related Articles