64-bit SQL 查詢 32-bit SQL 2000 Linked Server錯誤(Msg 7311)

2010-11-16

6874
0
MSSQL
2012-02-17

在64-bit 的SQL 用戶端對32-bit 的SQL 2000 或SQL 7.0的linked server作查詢時，會有如以下的錯誤訊息:

OLE DB provider "SQLNCLI10" for linked server "XXXXX" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "XXXXX" 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 DB provider "SQLNCLI10" for linked server "XXXXX". The provider supports the interface, but returns a failure code when it is used.

解法是32-bit的SQL 2000或SQL 7.0需升級SP3或SP4，並手動執行附在SP3及SP4裡的nstcat.sql升級stored procedure (預設路徑C:\Program Files\Microsoft SQL Server\MSSQL\Install)。

Reference : http://support.microsoft.com/kb/906954/en-us

Update on 2012/02/17

從64位元的SSMS去Query 32位元的Linked server時，會回傳錯誤。但昨天意外發現Open Query可以避掉這個error.

Select * From OPENQUERY(LinkedServerName,'select * from XXXXX')

回首頁