OPENROWSET

15 Sep 2006 1:48 AM

raidken

I have been struggling to run an OPENROWSET query against a SQL 2005
db.
While attempting to query a SQL Server 2005 database from a SQL 2K
database using OPENROWSET I set the following error:
********************************************************************************************************
Server: Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
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.".
********************************************************************************************************
Using Query Analyzer I can connect and query SQL 2005 database, but
can't seem to execute the OPENROWSET query. Does OPENROWSET exclusively
use named pipes?
The SQL 2005 installation is configured to allow connections on TCP/IP.

Secondly, I log onto the SQL 2005 using an account with Sysadmin privs,
but when I try to change configurations using "SURFACE AREA
CONFIGURATION" tool "for services and Connections" I get an access
denied error??

Your help will be highly appreciated.

Ken

15 Sep 2006 4:22 AM

Warren Brunk

Can you post your OpenRowSet Query?

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

<raid***@yahoo.com> wrote in message

Show quoteHide quote

news:1158284918.979981.177150@b28g2000cwb.googlegroups.com...
>I have been struggling to run an OPENROWSET query against a SQL 2005
> db.
> While attempting to query a SQL Server 2005 database from a SQL 2K
> database using OPENROWSET I set the following error:
> ********************************************************************************************************
> Server: Msg 53, Level 16, State 1, Line 0
> Named Pipes Provider: Could not open a connection to SQL Server [53].
> 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.".
> ********************************************************************************************************
> Using Query Analyzer I can connect and query SQL 2005 database, but
> can't seem to execute the OPENROWSET query. Does OPENROWSET exclusively
> use named pipes?
> The SQL 2005 installation is configured to allow connections on TCP/IP.
>
> Secondly, I log onto the SQL 2005 using an account with Sysadmin privs,
> but when I try to change configurations using "SURFACE AREA
> CONFIGURATION" tool "for services and Connections" I get an access
> denied error??
>
> Your help will be highly appreciated.
>
> Ken
>

15 Sep 2006 6:41 AM

SQL Ken

this works for me:
SELECT * FROM
           OPENROWSET('MSDASQL',
          'DRIVER{SQLServer};
                  SERVER=192.125.88.10;UID=login;PWD=password',

                  'SELECT *
          FROM MyDB.dbo.Mytable')

If you are not connecting to a remote server, you can't specifiy the
server name there instead.

when you configure "SURFACE AREA CONFIGURATION" , make sure that you
check the box.. somethinglike ' open query allow'

Ken