I created a new ODBC data source using SQLNCLI10 then created a linked server to SQL 2000 using this new ODBC data source on a server with SQL 2012 and although it displays the DBs and tables under the Linked Server, when I use it to query in TSQL, I get this error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSERVERNAME" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSERVERNAME". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Am I creating the ODBC data source correctly - or is it simply impossible to connect to SQL2000 from SQL2012?