OPENROWSET

  • Hi All,

    I am trying to execute below query, its an SQL Server 2005 Named Instance:

    create table #orphaned1 (username varchar(300), loginname varchar(300), passwords varchar(400))

    INSERT INTO #orphaned1 SELECT * FROM OPENROWSET('SQLNCLI','DRIVER={SQL Server);Server={Named Instance};UID=xxx;PWD=xxx','select i.name as username, j.name as loginname, j.password as password from test22..sysusers i, master.SYS.SQL_LOGINS j where (i.issqluser = 1 or isntuser is not null) and (i.sid is not null and i.sid <> 0x0) and i.sid=j.sid order by i.name ')

    select * from #orphaned1

    When I Ran te above query its running with Errors, please find the error messages below:

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "(null)" 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].

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

    Please Help me in this:

    Thanks In Advance

    Thank You.

    Regards,
    Raghavender Chavva

  • You have a mismatched bracket here

    SELECT * FROM OPENROWSET('SQLNCLI','DRIVER={SQL Server);Server={Named Instance};

    Should be DRIVER={SQL Server}

  • Assuming that the posted code is just an example and not the real code, the error looks like a configuration issue related to not allowing remote connections on the remote server. Try allowing remote connections in surface area configuration tool.

    --Ramesh


Viewing 3 posts - 1 through 2 (of 2 total)

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