OpenRowSet query Error

• SQlLogic

  SSCrazy Eights

  Points: 9686

  More actions

  June 20, 2014 at 8:14 am

  #308279

  Hi Guys,

  Any workaround for this error while trying to run openrowset query from server and from my local machine SSMS.

  Server - SQL 2008R2 Ent 64 bit and it is clustered server .

  My account has sysadmin rights.

  Query

  select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','exec dbo.sp_who') AS a

  Go

  select * from openrowset('SQLNCLI','Server=(server\Instance);Trusted_Connection=yes;','exec dbo.sp_who') AS a

  Go

  From Local Server Error

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

  OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

  Msg 2, Level 16, State 1, Line 0

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

  From Local SSMS to Server

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

  OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

  Msg 65535, Level 16, State 1, Line 0

  SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].

  Any solution?

  thank you

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  June 20, 2014 at 10:25 pm

  #1723743

  You need to remove the parenthesis from (server\instance) in the following example...

  select * from openrowset('SQLNCLI','Server=(server\Instance);Trusted_Connection=yes;','exec dbo.sp_who') AS a

  Of course, you also need to sub the correct names for server\instance.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)