linked server select problem - 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)

linked server select problem

Author

Topic

mmcnary
Starting Member

16 Posts

Posted - 2010-06-14 : 09:17:54

I have a strange situation.

I have a sql server instance that has a number of linked servers associated to it. I have multiple jobs and stored procedures that spin through a list of these servers and get various bits of data and accumulate it in this instance. I am adding a new process to this line-up and am having a difficult time in getting my data. I am receiving these messages when I try to get data from new table in a linked server. (I’m just using one to build out my process, then I'll push it to the rest of the linked servers.)

OLE DB provider "SQLNCLI10" for linked server "nnnnnn\SQLnnn" 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 'umb_dba..stuff'.

Since I couldn't get my more complex query to run, I decided to reduce my variables. I found a linked server query that works:

SELECT * FROM OPENQUERY([nnnnnn\SQLnnn],
'SELECT *
FROM master..syslogins')

This returns the rows I expect.

So I try to eliminate the data as the problem (my real table has an identity column and an XML column), I copy the data from master..syslogins into a new table in the source database:

select * into umb_dba.dbo.stuff from master..syslogins

select * from OPENQUERY([nnnnnn\SQLnnn],
'SELECT *
FROM umb_dba..stuff')

Same errors:

OLE DB provider "SQLNCLI10" for linked server "nnnnnn\SQLnnn" 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 'umb_dba..stuff'.

I tried to eliminate the database as the problem by creating the new table in master

select * into master.dbo.stuff from master..syslogins

Same errors.

Just to answer the more obvious questions:

I have tried all of the iterations of table naming, brackets, schema specified, etc.
I have another table in the same database as my new table, I am able to select from it through the linked server.
I have logged into the linked server using the link server credentials and executed the queries without error.
I created the table in the schema of the linked server credentials, no joy.
I have fiddled with permissions on the new table, and nothing seems to make a difference.
I have created the syslogins copy as a view, in all of the above configurations, thinking maybe that was it since syslogins is a system view. Nope.

Any ideas?

Thanx,

-Mark McNary

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources