SQL 2008 Linked serverto SQL 2000

Hi All,
I installed SQL 2008 x64 on Windows 2003 R2 X64 SP2. Also I installed 64
Bit Msdasql.dll. This server was installed SQL 2005 with SP2, and I removed
SQL 2005 before installing SQL 2008.
When I created a linked server from SQL 2008 to SQL 2000 sp4 I met some
strange things.
First, If I choose the linked server type as SQL Server to create the
linked server to SQL 200 SP4, I can't use a full object name to access the
data in linked server. such as: select * from
mylinkedserver.mydbname.dbo.mytable. This query reported errors as :
OLE Database provider "SQLNCLI10" for linked server "mylinkedserver" returned
message "Unspecified error".
OLE Database provider "SQLNCLI10" for linked server "mylinkedserver" returned
message "The stored procedure required to complete this operation could not
be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE Database provider
"SQLNCLI10" for linked server "mylinkedserver". The provider supports the
interface, but returns a failure code when it is used.

But if I use openquery, everything is ok. for example:
select * from openquery(mylinkedserver,'select * from mydbname.dbo.mytable')

Then if I use OLEDB provide to create the same linked server using following
connection string:
Provider=sqloledb;Data Source=mylinkedserver;Initial
Catalog=mydbname;Integrated Security=SSPI;

I can't get any data return by

select * from openquery(mylinkedserver,'select * from mydbname.dbo.mytable')

or

select * from mylinkedserver.mydbname.dbo.mytable

And there is no any error for above queries.

Thanks for any help!


Bill

I am getting the exact results with an upgrade of SQL 2005 to 2008 querying a
SQL 2000 SP4 linked server.

I need this to work. Microsoft?

"Bill Wang" wrote:

> Hi All,
> I installed SQL 2008 x64 on Windows 2003 R2 X64 SP2. Also I installed 64
> Bit Msdasql.dll. This server was installed SQL 2005 with SP2, and I removed
> SQL 2005 before installing SQL 2008.
> When I created a linked server from SQL 2008 to SQL 2000 sp4 I met some
> strange things.
> First, If I choose the linked server type as SQL Server to create the
> linked server to SQL 200 SP4, I can't use a full object name to access the
> data in linked server. such as: select * from
> mylinkedserver.mydbname.dbo.mytable. This query reported errors as :
> OLE Database provider "SQLNCLI10" for linked server "mylinkedserver" returned
> message "Unspecified error".
> OLE Database provider "SQLNCLI10" for linked server "mylinkedserver" returned
> message "The stored procedure required to complete this operation could not
> be found on the server. Please contact your system administrator.".
> Msg 7311, Level 16, State 2, Line 1
> Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE Database provider
> "SQLNCLI10" for linked server "mylinkedserver". The provider supports the
> interface, but returns a failure code when it is used.
>
> But if I use openquery, everything is ok. for example:
> select * from openquery(mylinkedserver,'select * from mydbname.dbo.mytable')
>
> Then if I use OLEDB provide to create the same linked server using following
> connection string:
> Provider=sqloledb;Data Source=mylinkedserver;Initial
> Catalog=mydbname;Integrated Security=SSPI;
>
> I can't get any data return by
>
> select * from openquery(mylinkedserver,'select * from mydbname.dbo.mytable')
>
> or
>
> select * from mylinkedserver.mydbname.dbo.mytable
>
> And there is no any error for above queries.
>
> Thanks for any help!
>
>
> Bill

Refer to this : http://support.microsoft.com/kb/906954

Should fix the problem