SELECT from openrowset (exec stored procedure)

Question

Post reply

SELECT from openrowset (exec stored procedure)

gdudley

SSC-Addicted

Points: 489
More actions
November 4, 2009 at 9:09 am

#156373

Using a technique I found on the web in various forums:
SELECT *
FROMOPENROWSET('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?

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

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

Try this

select *

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

'exec dbo.sp_who') AS a

gdudley SSC-Addicted Points: 489 More actions · Answer 2

gdudley

SSC-Addicted

Points: 489

November 4, 2009 at 12:09 pm

#1074975

I still get the same error

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

gdudley SSC-Addicted Points: 489 More actions · Answer 4

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.

matt6288 SSCertifiable Points: 5058 More actions · Answer 5

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

This works OK for me.

select

a.*

from

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

'

set fmtonly off;

exec master.dbo.sp_who

') a

gdudley SSC-Addicted Points: 489 More actions · Answer 7

THANKS !!!

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