Solve SQL Server linked server error (Msg 7311)
I have two SQL servers. The primary one is SQL Server 2008 x64 (10.0.2531.0). The linked one is a SQL Server 2000 SP4 (8.00.2055).
When I try to perform a query from the primary server to a linked server (in four-part name format:
SELECT * FROM LINKED_SERVER.MY_DB.dbo.MY_TABLE
I received the following error:
OLE DB provider "SQLNCLI10" for linked server "LINKED_SERVER" returned message "Unspecified error".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "LINKED_SERVER". The provider supports the interface, but returns a failure code when it is used.
I don't know if the problem caused by LINKED server according different SQL Server version (2000 and 2008) or different bit (32 bit vs 64 bit)
Most of the solution on the internet is about to patch the old SQL Server 2000. But my SQL Server 2000 is already in SP4. I think modifying the store proc carry certain of risk. What I have done is to re-write the SQL using OpenQuery():
SELECT * FROM OPENQUERY( LINKED_SERVER,
' SELECT * FROM MY_DB.dbo.MY_TABLE ')
Then the problem solved.
When I try to perform a query from the primary server to a linked server (in four-part name format:
SELECT * FROM LINKED_SERVER.MY_DB.dbo.MY_TABLE
I received the following error:
OLE DB provider "SQLNCLI10" for linked server "LINKED_SERVER" returned message "Unspecified error".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "LINKED_SERVER". The provider supports the interface, but returns a failure code when it is used.
I don't know if the problem caused by LINKED server according different SQL Server version (2000 and 2008) or different bit (32 bit vs 64 bit)
Most of the solution on the internet is about to patch the old SQL Server 2000. But my SQL Server 2000 is already in SP4. I think modifying the store proc carry certain of risk. What I have done is to re-write the SQL using OpenQuery():
SELECT * FROM OPENQUERY( LINKED_SERVER,
' SELECT * FROM MY_DB.dbo.MY_TABLE ')
Then the problem solved.
Comments