locked
Named pipes provider : could not open a connection to a SqL server . . . . . RRS feed

  • Question

  • Hey all, i need help 

        I have a job which was running quite peacefully for the last EVER, but since last Saturday it keeps failing  and then run successfully and then fails. . . . .and gives me the below error.

       Executed as user: Sx\xxx. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  9:00:00 PM  Progress: 2014-03-11 21:00:02.16     Source: {122F32F1-F99F-4A5A-BA70-F5426B2747DA}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2014-03-11 21:00:02.19     Source: Manually replicate tables      Executing query "  USE DBNAME  ".: 50% complete  End Progress  Error: 2014-03-11 21:00:22.35     Code: 0xC002F210     Source: Manually replicate tables Execute SQL Task     Description: Executing the query "          exec usp_ReplicateEsourceTable 'Ebase'; ..." failed with the following error: "Named Pipes Provider: Could not open a connection to SQL Server [53].   OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "Login timeout expired".  OLE DB provider "SQLNCLI10" for linked server "ServerName" 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.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Progress: 2014-03-11 21:00:22.45     Source: Notify on failure      Executing query "exec msdb.dbo.sp_notify_operator   @profile_name =...".: 100% complete  End Progress  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:00:00 PM  Finished: 9:00:23 PM  Elapsed:  22.25 seconds.  The package execution failed.  The step failed.

    I have already tested the linked server connection between those servers and its fine. And what is weird is that when i run the job manually it runs successfully. 

      Any ideas? 

    Wednesday, March 12, 2014 2:05 PM

Answers

  • Hi,

    How often does the job run? Does the query fail intermittently?

    I suggest you check the following configuration on both servers:

    1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties. What’s the order of protocol?

    2. Check if the default port of TCP/IP  is set to 1433.

    By default, the top protocol is to use TCP/IP and then Named Pipes. There are two possible situations:

    1. Connection fails and client protocol is using TCP/IP 1433.

    When server A remotes to server B, it will use TCP/IP connection. If it encounters some issue during connecting (such as network issues), it will use Named Pipes instead. If the Name Pipes 445 still fails, thus the above error message will be thrown out. It will use Name Pipes afterwards until the SQL Server service is restarted.

    2. The port number is not 1433.

    However, the remote machine is listening 1433. Then, the linked server will trying to use TCP/IP first. Because of the port error, it will change to Name Pipes.

    Thanks.


    Tracy Cai
    TechNet Community Support


    • Edited by tracycai Friday, March 14, 2014 3:12 AM
    • Proposed as answer by Uri DimantMVP Saturday, March 15, 2014 7:15 AM
    • Marked as answer by tracycai Friday, March 21, 2014 9:51 AM
    Friday, March 14, 2014 3:11 AM

All replies

  • Is it DTS/SSIS package? Can you run it manually?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 12, 2014 2:15 PM
  • There are actually two jobs. One is SSIS package and the other is just a job which runs 11 stored procedures one after the other.

    Thanks.

    Wednesday, March 12, 2014 2:27 PM
  • Hi,

    How often does the job run? Does the query fail intermittently?

    I suggest you check the following configuration on both servers:

    1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties. What’s the order of protocol?

    2. Check if the default port of TCP/IP  is set to 1433.

    By default, the top protocol is to use TCP/IP and then Named Pipes. There are two possible situations:

    1. Connection fails and client protocol is using TCP/IP 1433.

    When server A remotes to server B, it will use TCP/IP connection. If it encounters some issue during connecting (such as network issues), it will use Named Pipes instead. If the Name Pipes 445 still fails, thus the above error message will be thrown out. It will use Name Pipes afterwards until the SQL Server service is restarted.

    2. The port number is not 1433.

    However, the remote machine is listening 1433. Then, the linked server will trying to use TCP/IP first. Because of the port error, it will change to Name Pipes.

    Thanks.


    Tracy Cai
    TechNet Community Support


    • Edited by tracycai Friday, March 14, 2014 3:12 AM
    • Proposed as answer by Uri DimantMVP Saturday, March 15, 2014 7:15 AM
    • Marked as answer by tracycai Friday, March 21, 2014 9:51 AM
    Friday, March 14, 2014 3:11 AM