OPENROWSET error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OPENROWSET error

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-03-30 : 14:16:06

I run the following script and it runs fine:

Select *
from
OPENROWSET('SQLOLEDB','Data Source=CPSHOUDB13;Trusted_Connection=yes;
Integrated Security=SSPI','Execute RTMS_BAS_Prod..SP_RTMS_BAS_LastSeenOver18Months')

I then run the following script:

USE RTMS_BAS_Prod
Select *
from
OPENROWSET('SQLOLEDB','Data Source=CPSHOUDB13;Trusted_Connection=yes;
Integrated Security=SSPI','SELECT * FROM tblapproved_inv_hdr')

I receive these errors:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tblapproved_inv_hdr'.

Would anyone know what I'm doing wrong here? I am still learning of the openrowset stuff. Thanks to anyone who can help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-30 : 14:18:10

use four-part naming convention when selecting from your table.

E 12°55'05.63"
N 56°04'39.26"

osupratt
Posting Yak Master

238 Posts

Posted - 2009-03-30 : 16:23:30

i don't need to have linked server set up to run OPENROWSET do i?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 02:17:39

The linked server is giving you the default database (most often master) as database, so when you try to run the select query, you are running that query in the master database context.

Select *
from
OPENROWSET('SQLOLEDB','Data Source=CPSHOUDB13;Trusted_Connection=yes;
Integrated Security=SSPI','SELECT * FROM mydatabase.myowner.tblapproved_inv_hdr')

E 12°55'05.63"
N 56°04'39.26"

osupratt
Posting Yak Master

238 Posts

Posted - 2009-03-31 : 14:44:43

i still don't know if i needed to add linked server so i did anyway:

USE master
GO
EXEC sp_addlinkedserver
'CPSHOUDB13',
N'SQL Server'
GO

now here is the query:

Select *
from
OPENROWSET('SQLOLEDB','Data Source=CPSHOUDB13;Trusted_Connection=yes;
Integrated Security=SSPI', 'SELECT*
FROM CPSHOUDB13.RTMS_BAS_Prod.dbo.tblapproved_inv_hdr')

and now here are the errors:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

i am not familiar with naming conventions. i could have something wrong in the query, but since is the first time i've used OPENROWSET and linked server.... i really can't tell.

thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources