linked server problem

Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.
When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

linked server problem

Hi,
I have server A and B with SQL ent sp3 / Win Adv sp4. On server A I created
a linked server to B, I also selected Collation compatible. My problem,
It is ok to run on Server A (returning records instantly):
select * from b.master.dbo.sysdatabases
But it hangs when I run this on A:
begin tran
select * from b.master.dbo.sysdatabases
commit
I have checked MSDTC is running on both servers.
Did I miss anything?
Thanks.When you say "It is ok to run on Server A (returning records instantly)",
does that mean,
on ServerB, if you run
select * from a.master.dbo.sysdatabases
it returns records instantly?
If so, and the problem is when you run
select * from b.master.dbo.sysdatabases
OR
begin tran
select * from b.master.dbo.sysdatabases
commit
your best bet would be to,
1. make a connection directly to Server B and run
begin tran
select * from master.dbo.sysdatabases
commit
-- And see if that hangs.
If it doesnt, and it seems teh problem is with Linked servers, then you
might want to look at sysprocesses and see waht the spid is doing - is it
sleeping, waiting on a resource, etc.
Also, when you say it "hangs", what's the longest you've waited for?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Linked Server from SQL-Server 2000 to Oracle 8.1.7

We have a linked server, which worked fine, but we have just changed
the collation sequence, in SQL-Server, from
SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.

Now trying to use the Linked server, we get the following message:
"Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE
DB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
The provider did not give any information about the error]."

Ha anyone seen this before, because I am not sure why changing the
collation sequence, would affect the Linked Server"wotan" <wotan_79@.hotmail.com> wrote in message
news:e5fd30ef.0408100354.6e5ec7a0@.posting.google.c om...
> We have a linked server, which worked fine, but we have just changed
> the collation sequence, in SQL-Server, from
> SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.
> Now trying to use the Linked server, we get the following message:
> "Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider
> did not give any information about the error. OLE DB error trace [OLE
> DB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
> The provider did not give any information about the error]."
> Ha anyone seen this before, because I am not sure why changing the
> collation sequence, would affect the Linked Server

I have no idea about the specific error, however this post may be useful
(note the links to KB articles at the bottom):

http://groups.google.com/groups?hl=...tngxa06.phx.gbl

Apart from that, the usual general comments apply - install the latest SQL
servicepack and MDAC version. It's not clear from your comments which
collation you changed - as far as I know, changing the default collation for
the instance requires you to reinstall completely or just rebuild master,
and that means it's very possible that other system-level configurations
were changed or lost.

Simon|||wotan_79@.hotmail.com (wotan) wrote in message news:<e5fd30ef.0408100354.6e5ec7a0@.posting.google.com>...
> We have a linked server, which worked fine, but we have just changed
> the collation sequence, in SQL-Server, from
> SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.
> Now trying to use the Linked server, we get the following message:
> "Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider
> did not give any information about the error. OLE DB error trace [OLE
> DB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
> The provider did not give any information about the error]."
> Ha anyone seen this before, because I am not sure why changing the
> collation sequence, would affect the Linked Server

I suggest You check syscolumns for any columns with individually
defined collations. Eventually some columns have different collations,
so JOIN statements will fail.
If there are any, You can change the collation via the "collationid".
To be able to update a system table, you have to use:

exec sp_reconfigure 'allow update','1'
reconfigure with override
[put your sql statement here]
exec sp_reconfigure 'allow update','0'
reconfigure with override|||wotan wrote:
> We have a linked server, which worked fine, but we have just changed
> the collation sequence, in SQL-Server, from
> SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.
> Now trying to use the Linked server, we get the following message:
> "Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider
> did not give any information about the error. OLE DB error trace [OLE
> DB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:
> The provider did not give any information about the error]."
> Ha anyone seen this before, because I am not sure why changing the
> collation sequence, would affect the Linked Server

Not an Oracle error so look on the SQL Server side.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)