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

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: How do I prevent linked server errors?


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: 
 
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.  
 
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.

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?

 

 


Created: 11/3/2005 | Last Updated: 4/19/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (242)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...