Post reply

SELECT from openrowset (exec stored procedure)

  • November 4, 2009 at 9:09 am

    #156373

    Using a technique I found on the web in various forums:

    SELECT *

    FROM OPENROWSET('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')

    I get the following errors:

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

    How do I rectify this?

  • November 4, 2009 at 9:34 am

    #1074881

    Try this

    select *

    from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',

    'exec dbo.sp_who') AS a

  • November 4, 2009 at 12:09 pm

    #1074975

    I still get the same error

  • November 4, 2009 at 1:08 pm

    #1075016

    Have you in fact verified that remote connections are allowed in SQL Server Surface Area Config tool? Using OPENQUERY treats the actual connection as "remote" even if you happen to be talking to the instance on the same machine. Also - make sure named pipes are enabled since that seems to eb what is being used to try the connection.

    Also - are you connecting to a default or a named instance? (local) assumes only default instance, so a named instance won't be found.

    ----------------------------------------------------------------------------------
    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?

  • November 4, 2009 at 1:37 pm

    #1075030

    Yes remote connections are successful and Ad Hoc Remote Queries are enabled.

    Named Pipes are also enabled.

    It is not a default instance so I then tried:

    SELECT * FROM OPENROWSET('SQLNCLI','Server=''server\instance'';

    TRUSTED_CONNECTION=YES;','exec dbo.sp_who')

    and I get back:

    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 65535, Level 16, State 1, Line 0

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    To test out connectivity I added a linked server to this server from another server without issue.

  • November 4, 2009 at 2:17 pm

    #1075043

    SELECT * FROM OPENROWSET('SQLNCLI','Server=server\instance;

    TRUSTED_CONNECTION=YES;',

    'exec dbo.sp_who')

    The Second argrument 'Server=server\instance;

    TRUSTED_CONNECTION=YES;' Should all be wrapped in 1 single quote. No extra quotes around the server. I can't test with the named instance but that works for just local.

  • November 4, 2009 at 2:27 pm

    #1075048

    This works OK for me.

    select

    a.*

    from

    openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',

    '

    set fmtonly off;

    exec master.dbo.sp_who

    ') a

  • November 4, 2009 at 3:07 pm

    #1075068

    THANKS !!!

    It was the extra single quotes giving the problem, got carried away with them for sure. (staring at the screen for too long...)

Viewing 8 posts - 1 through 7 (of 7 total)

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