pulling remote data via linked server (solved)

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

General SQL Server Forums

New to SQL Server Programming

pulling remote data via linked server (solved)

Author

Topic

rhavenn
Starting Member

14 Posts

Posted - 2009-09-23 : 15:37:31

Hello,

I've got a SQL 2008 server that has a linked server object to a SQL 2000 server. I'm trying to pull the location of the last backup for a specific database. The SQL query works correctly when directly on the 2000 box, but fails when I try to run it via the linked server.

The query:


SELECT TOP 1 [LINKEDSERVER].msdb.dbo.backupmediafamily.physical_device_name
		FROM   [LINKEDSERVER].msdb.dbo.backupmediafamily 
			INNER JOIN [LINKEDSERVER].msdb.dbo.backupset ON [LINKEDSERVER].msdb.dbo.backupmediafamily.media_set_id = [LINKEDSERVER].msdb.dbo.backupset.media_set_id 
		WHERE  [LINKEDSERVER].msdb..backupset.type = 'D' AND [LINKEDSERVER].msdb.dbo.backupset.database_name = 'DBNAME' 
		ORDER BY [LINKEDSERVER].msdb.dbo.backupset.backup_start_date DESC

The error:


OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "LINKEDERVER". The provider supports the interface, but returns a failure code when it is used.

If I remove the [LINKEDSERVER] and run that on the SQL 2000 box directly it works. My linkedserver is using a SQL user that has read permissions to the msdb tables. Is this a permissions issue or is the code wrong?

The end goal is to insert that into a stored procedure, but I want to get the query working first.

Thanks.

rhavenn
Starting Member

14 Posts

Posted - 2009-09-23 : 17:16:17

Solved. This is due to a bug on the 2000 server side explained in this KB article: http://support.microsoft.com/kb/906954

The solution was to do an openquery like this:
select * from openquery([LINKEDSERVER],'select statement'

Note, the KB article doesn't mention this. However, the server SQL 2000 is on is cranky and the SP4 files were no longer available there.

ryan4u
Starting Member

1 Post

Posted - 2009-11-11 : 07:29:52

This is the fix for this problem :

-- Original Source :
-- http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx
--
-- Run this on the SQL 2000 database:

Use Master
Go
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as

declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
go

Ryan Thompson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources