SQL Server native client 11.0 does not support connection to SQL Server 2000 or earlier version

Question

I'm trying to get data from a SQL Server 2000 database and insert it into another database in SQL Server 2012.

The linked server was created, but I get an error when I use SELECT from one of the tables.

Something like this:

SELECT * 
FROM linked_server_name.database_name.schema.table_name

Error:

SQL Server native client 11.6 does not support connection to SQL Server 2000 or earlier versions

Any idea can be helpful. Thanks.

Answer 1

Here is how I fixed it today using SQL Server 2014 on Windows 8.1 Pro. I was trying to add a Linked Server for a remote SQL Server 2000 instance. While using the SQL Server Native Client 11.0 I received the same error as the OP.

Then I tried creating an ODBC data source on my machine, using "ODBC Driver 11 for SQL Server" and got the following error. "ODBC Driver 11 for SQL Server does not support connections to SQL Server 2000 or earlier versions"

At last I created a new Data Source using "SQL Server" driver in the New Data Source wizard, NOT "SQL Server Native Client 11.0" driver. The Data Source was successfully created. And then I used this newly created data source as "Product name" and "Data source" both, in "New Linked Server" dialog box in the Management Studio, and selected "Microsoft OLE DB Provider for ODBC Drivers" as the provider. In the "Linked server" field, I put the IP address of the remote SQL Server. It successfully created a new Linked Server in my SQL Server 2014 Management Studio. I hope this answer will help anyone who lands here in search of an answer for this issue.

Answer 2

Use the "Microsoft OLE DB Provider for SQL Server" (SQLOLEDB) OLE DB provider, not SQL Server Native Client 11.0" (SQLNCLI11) OLE DB provider to access SQL Server 2000. Although SQLOLEDB is deprecated, you'll need to use it for SQL Server 2000, which is unsupported anyway.