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

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

MSDTC and Delete transactions

Hello
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:

Quote:

Originally Posted by

I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.
>
However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@.@.ERROR value. There are just DELETE statement and a plain RETURN.

In a normal stored procedure if you have:

BEGIN TRANSACTION
DELETE tbl WHERE ...
DELETE tbl2 WHERE ...
COMMIT TRANSACTION

you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.

A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.

But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @.@.error
or make sure XACT_ABORT is ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx