Error when executing a distributed query on a linked server

Hi,
It's a repost of an old thread with more information included
I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits. I've
been trying to link a sql server 2000 SP4 to this but I'm having some
troubles. When creating the linked server from the Sql Server Management
Studio/New Linked Server I choosed Server type to be "SQL Server" for the 2K
linked server. The linked server was created. Then I go to
Properties/Security and configure the linked server to be accesed as sa. But
when I run a distributed query like
Select * from linkedserver.database.dbo.table I get the error:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 2
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
server "sqlservhpold". A four-part name was supplied, but the provider does
not expose the necessary interfaces to use a catalog or schema.
I tested the link by using sp_testlinkedserver and it returns no failure.
I also created a linked server in the reverse. I mean let server A be the
Sql Server 2005 64 bits Enterprise, and server B be a Sql Server 2000
Standard. I'm in A making B as linked server and trying to make a
distributed query from A to B (Select * from B.database.dbo.table) and this
fails. I tryed in B create A as a linked server. Then I made a distributed
query from B to A (Select * from A.database.dbo.table) and it works as
expected.
The most frustrating is the message that error 7399 returns: "The provider
did not give any information about the error." I cn not find nothing about
such an error message...
Any hint is welcomed
Thanks in advance
SammySammyBar (sammybar@.gmail.com) writes:
> I'm testing a Sql Server 2005 Enterprise Evaluation Edition 64 bits.
> I've been trying to link a sql server 2000 SP4 to this but I'm having
> some troubles. When creating the linked server from the Sql Server
> Management Studio/New Linked Server I choosed Server type to be "SQL
> Server" for the 2K linked server. The linked server was created. Then I
> go to Properties/Security and configure the linked server to be accesed
> as sa. But when I run a distributed query like
> Select * from linkedserver.database.dbo.table I get the error:
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "SQLNCLI" for linked server "sqlservhpold" reported an
> error. The provider did not give any information about the error.
> Msg 7312, Level 16, State 1, Line 2
> Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked
> server "sqlservhpold". A four-part name was supplied, but the provider
> does not expose the necessary interfaces to use a catalog or schema.
Can you double check the SQL 2000 instance, by doing SELECT @.@.version?
I seem to recall that to run linked queries from SQL 2005 to SQL 2000,
the latter needs some catalog procedures that comes with SP4, or possbly
SP2.
Also the exact command you use to create the linked server could be of
interest.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx