SQL Server 2012 linked server to SQL Server 2000

Question

I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000.

The linked server has been setup and I can browser the target database through the linked server which indicates that permissions are not a problem.

However, when I try to run a query, I get the following error (not my actual sql):

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM BOB" against OLE DB provider "SQLNCLI10" for linked server

Anyone have any ideas of what the problem is and how to solve it?

• Aaron Bertrand: Does your actual query include a four-part name? A linked server doesn't automatically include database reference, for example. Can you show the syntax you are using when you "try to run a query"? We can't see exactly what that is. Would also be useful to know how that differs from what you're doing when you "browse" the database and whether you are performing both actions as the same user.
• GordyII: Its of the form Server.Database.DBO.Table. They would be different users, so perhaps it is a permssions problem.
• Aaron Bertrand: That would be my guess then, yes.

Answer 1

Using SQLNCLI10 to create a linked server to SQL Server 2000 causes a fault. You can create an odbc connection than attach tables to your 2012 database. Then you build your treatments in SQL 2012 database as if the table was native. This is just a workaround that sometimes works. It's not recommended for large data transaction, like more that 7MM.

Answer 2

Your query looks a little strange I think.

When querying involving a linked server, your syntax should look like this (including the brackets):

SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]