sunshine (3/24/2008)

---

I can select from a master table however get this error for all user databases. any idea what is going on?

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "Server Name" does not contain the table ""DBname"."dbo"."sysfiles"". The table either does not exist or the current user does not have permissions on that table.

This is the setting from my linked server.

declare @ServerName varchar(50)

Set @ServerName = 'Server Name'

--exec sp_dropserver @ServerName

exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'

-- Set options

exec sp_serveroption @ServerName, 'collation compatible', 'true'

exec sp_serveroption @ServerName, 'data access', 'true'

exec sp_serveroption @ServerName, 'rpc', 'false'

exec sp_serveroption @ServerName, 'rpc out', 'true'

exec sp_serveroption @ServerName, 'use remote collation', 'false'

-- Test connection

declare @SQL nvarchar(200)

set @sql = 'select top 1 * from ' + @ServerName + '.master.dbo.sysobjects'

exec sp_executesql @sql

-- test retreival

select count (*) from ServerName.master.DBO.sysdatabases