Problem on OPENROWSET

Question

Post reply

Problem on OPENROWSET

Zahran

SSCommitted

Points: 1724
More actions
December 10, 2008 at 4:15 am

#73416

SELECT a.* FROM OPENROWSET(
'SQLOLEDB',
'Trusted_Connection=yes;Integrated Security=SSPI;DataSource=''10.0.172.53'';' ,
'SELECT * FROM ODS.dbo.retdacct_account') AS a
When I try to execute above sql statement and get records from remote SQL server I get following error message.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ODS.dbo.retdacct_account'.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
I am able to log on remote server from Query Analyzer and get records. dbo.retdacct_account table is available in remote server. Can you help? This is urgent!
- Zahran -

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you see the database ODS in the output generated by this:

SELECT a.* FROM OPENROWSET(

'SQLOLEDB',

'Trusted_Connection=yes;Integrated Security=SSPI;DataSource=''10.0.172.53'';' ,

'SELECT [name] FROM master.dbo.sysdatabases') AS a

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zahran SSCommitted Points: 1724 More actions · Answer 2

Hi Chris Morris,

Thanks for your reply. When I execute your code I get database available in the local server. Not the remote server(10.0.172.53).

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Zahran (12/10/2008)
Hi Chris Morris,
Thanks for your reply. When I execute your code I get database available in the local server. Not the remote server(10.0.172.53).

Then you don't need OPENROWSET. Run the query as-is.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zahran SSCommitted Points: 1724 More actions · Answer 4

ODS database is not available in the local server. It is available in the remote server. I need get data from remote server.

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Zahran (12/10/2008)
ODS database is not available in the local server. It is available in the remote server. I need get data from remote server.

Thanks for your reply. When I execute your code I get database available in the local server. Not the remote server(10.0.172.53).

So...which is it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zahran SSCommitted Points: 1724 More actions · Answer 6

I get list of databases available in the local server. But I need to retrieve data from Remote server.

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Zahran (12/10/2008)
I get list of databases available in the local server. But I need to retrieve data from Remote server.

What is the name of the database on the remote server which you wish to retrieve data from?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zahran SSCommitted Points: 1724 More actions · Answer 8

Zahran

SSCommitted

Points: 1724

December 10, 2008 at 5:32 am

#910240

ODS

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Chris Morris (12/10/2008)
Can you see the database ODS in the output generated by this:
SELECT a.* FROM OPENROWSET(
'SQLOLEDB',
'Trusted_Connection=yes;Integrated Security=SSPI;DataSource=''10.0.172.53'';' ,
'SELECT [name] FROM master.dbo.sysdatabases') AS a

This code will list the databases on the remote server 10.0.172.53. Is the database ODS in the list?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Zahran SSCommitted Points: 1724 More actions · Answer 10

This code lists databases available in the local server, not remote server 10.0.172.53. ODS database is available in the remote server. So, ODS database does not listed in the above list. Why this code cannot access remote server? Any thing wrong in the code?

- Zahran -

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Zahran (12/10/2008)
This code lists databases available in the local server, not remote server 10.0.172.53. ODS database is available in the remote server. So, ODS database does not listed in the above list. Why this code cannot access remote server? Any thing wrong in the code?

The code will list databases on server 10.0.172.53, whether it is local or remote.

If you are sure that 10.0.172.53 is the remote server, then it does not have database ODS.

If you are sure that the remote server hosts database ODS, then it's not 10.0.172.53.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Zahran, there is an error in the provider string in your openrowset statement.

The syntax should be like this

SELECT * from OpenRowSet('SQLOLEDB', 'Server=BARENELL;Trusted_Connection=yes;', 'select SERVERPROPERTY(''MachineName'')')

where BARENELL is the name of the instance of SQL Server which you want to work with.

The statement above will return the name of the Windows server hosting SQL Server.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Zahran (12/10/2008)
SELECT a.* FROM OPENROWSET( 'SQLOLEDB', 'Trusted_Connection=yes;Integrated Security=SSPI;DataSource=''10.0.172.53'';' ,
'SELECT * FROM ODS.dbo.retdacct_account') AS a

Zahran, you use single quote in connection string which is inapproppriate there.

Proper quotes for identifiers are double quotes, not single quotes.

Not sure if you need quotes around that IP address at all.

_____________
Code for TallyGenerator