Post reply

Named Pipes Provider

  • January 24, 2008 at 3:57 pm

    #183251

    Hi,

    I have SQL 2005 Ent SP2 on Windows 2003 server. When I tried to compile a proc from local box and the error:

    OLE DB provider "SQLNCLI" for linked server "PROD" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "PROD" 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 5, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [5].

    Msg 15151, Level 16, State 1, Line 2

    Cannot find the object 'pr_proc', because it does not exist or you do not have permission.

    "PROD" is alias of a SQL 2000 server. Inside the proc has something like:

    select * from PROD.database.dbo.table.

    If I go to SQL 2005 server (there is linked server setup. I could see all databases and table...when expand the folder), I could run the select statement from SQL Server Management Studio without any problem. Do I have to setup on local box? Or something missing from my local box? Thanks.

    Chris

  • January 24, 2008 at 8:14 pm

    #771567

    can you please give a try as it happened with me once.

    try

    select * from [PROD].[database].dbo.

    instead of

    select * from PROD.database.dbo.table.

    i know there is no difference in this but it worked for me.

  • January 24, 2008 at 9:40 pm

    #771579

    Have you actually gone in to the Surface area configuration tool and enabled remote connections? from the server console on the box you can't talk to?

    By default the install comes with that turned off.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 24, 2008 at 10:45 pm

    #771588

    Hi SCC and Matt,

    Thanks for the help. I will try:

    select * from [PROD].[database].dbo.

    tomorrow when I get back to office.

    And yes, I signed onto SQL server itself and made sure it allows all local, remote, named pipe and TCP/IP connection. I also ran cliconfg and added alias/server on server box. From server box, I could expand server object to view all database, table and views on SQL 2000 box. I could run:

    select * from PROD.database.dbo.table

    on server box with no problem. But I could not compile procedure nor execute the SQL statement on my local client box. Not sure what prevent me from access SQL 2000 box.

    Chris

  • January 24, 2008 at 11:47 pm

    #771591

    Hi,

    Please try this. It should help:

    Go to SQL Server 2005 Surface Area Configuration

    Select Surface Are Configureation for Services and Connections

    Under Database Engine

    -> Select Remote Connections

    -> Select Local and Remote Connections

    -> Using both TCP/IP and Named Pipes

    Rgds,

  • January 25, 2008 at 7:41 am

    #771744

    Are you using integrated security? It looks like a double hop issue, i.e. NTLM credentials cannot be hopped to the second machine. So the linked server works fine if you run the query on the server but does not work when you run it from a client. Check this link:http://support.microsoft.com/kb/238477

    named pipe is a workaround. Make sure both servers enable the named pipe protcol.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply