Msg 7314 while querying across linked servers

Hello
I am beginning to think that linked server are more trouble than they are wo
rth and MS will need to work harder to get these to work properly. Here is t
he 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"."t
ables"'. 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 conta
in the table: ProviderName='srv2', TableName='"db1"."information_schema"."ta
bles"'].
It works if I connect to srv2 with the login being used to link the 2 server
s 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 hav
e permissions on that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not con
tain 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 serv
ers 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
>|||It works. Thanks Mark
Arun
"Mark Allison" wrote:

> Arun,
> 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:
>