Msg 7314 while querying across linked servers

Hello
I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
From srv1, when I do
select * from srv2.db1.information_schema.tables
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
It works if I connect to srv2 with the login being used to link the 2 servers and do
use db1
go
select * from information_schema.tables
go
Also if I do <<<select * from srv2.db1.dbo.someothertable>> it works.
Trying this on SQL Server 2000 enterprise hotfix 837.
Any clues?
Thanks for reading and appreciate your help
ArunArun,
Try this:
exec srv2.db1.dbo.sp_executesql N'select * from information_schema.tables'
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Arun wrote:
> Hello
> I am beginning to think that linked server are more trouble than they are worth and MS will need to work harder to get these to work properly. Here is the latest problem.
>
> From srv1, when I do
> select * from srv2.db1.information_schema.tables
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'srv2' does not contain table '"db1"."information_schema"."tables"'. The table either does not exist or the current user does not have permissions on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='srv2', TableName='"db1"."information_schema"."tables"'].
> It works if I connect to srv2 with the login being used to link the 2 servers and do
> use db1
> go
> select * from information_schema.tables
> go
> Also if I do <<<select * from srv2.db1.dbo.someothertable>> it works.
> Trying this on SQL Server 2000 enterprise hotfix 837.
> Any clues?
> Thanks for reading and appreciate your help
> Arun
>