OPENROWSET ERROR using a LINKED Server

Question

OPENROWSET ERROR using a LINKED Server

Ashok Jebaraj

Ten Centuries

Points: 1215
More actions
June 18, 2009 at 2:36 am

#206036

Hi All,
I am trying to use a OPENROWSET function to run a simple SQL Statement on a linked server. When I run the query directly using the 4 part identifier then it works perfectly.
select * FROM [MyLinkedServerName].MyDataBaseName.dbo.TableName
However when I run the query using OPENROWSET function I get the below error.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=[MyLinkedServerName];uid=MyUID;pwd=MyPWD;',
'SELECT TOP 10 * FROM MyDataBaseName.dbo.TableName') AS a
Error:
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 87, Level 16, State 1, Line 0
SQL Network Interfaces: Connection string is not valid [87].
Can anyone help me resolve this issue?
Ashok Jebaraj

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

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

Don Urquhart SSCrazy Points: 2423 More actions · Answer 1

I think it is because you are specifying a provider string but have not indicated the driver type. For instance if it is a SQL linked server you might try:

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Driver={SQL Server};Server=[MyLinkedServerName];uid=MyUID;pwd=MyPWD;',

'SELECT TOP 10 * FROM MyDataBaseName.dbo.TableName') AS a

If you don't stand for something, you'll fall for anything!,

Don Urquhart

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 2

Thank you for your response. I tried the same and I got this error.

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

SQL Network Interfaces: Connection string is not valid [87].

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

I do not understand why SQL server is not able to identify the linked server name?

Ashok Jebaraj

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

-> Make sure the named pipes and TCP/IP protocols are open (SQL Server Configuration Manager).

->Make sure that the SQL Browser service is runing .

-> Make sure the server allows the remote connections (check the Surface area configuration)

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

also try using openquery .

basically , you conection string you are using seems incorrect .

I used the following and it worked

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure with override

EXEC sp_addlinkedserver

@server='test',

@srvproduct='',

@provider='SQLNCLI',

@datasrc='IGTGGN1645\ORION',

@catalog='Adventureworks'

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=test;Trusted_Connection=yes;',

'SELECT GroupName, Name, DepartmentID

FROM AdventureWorks.HumanResources.Department

ORDER BY GroupName, Name') AS a;

Using openquery :

------------------

select * from openquery (test,'SELECT GroupName, Name, DepartmentID

FROM AdventureWorks.HumanResources.Department

ORDER BY GroupName, Name')

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 5

Hi All,

thank you all for your responses. The issue is resolved. It is because I used square brackets for the server/instance name in the connection string.

Wrong Syntax:

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=[MyServerName,Port];Trusted_Connection=yes;Database=MyDatabaseName,

'SELECT TOP 10 * FROM DatabaseName.dbo.TableName') AS a

Correct Syntax:

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=MyServerName,Port;Trusted_Connection=yes;Database=MyDatabaseName,

'SELECT TOP 10 * FROM DatabaseName.dbo.TableName') AS a

Thank you all again for your support.

Ashok Jebaraj