2

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?

Improve this question
3
  • 3
    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.
    – Aaron Bertrand
    Jun 6, 2013 at 3:37
  • Its of the form Server.Database.DBO.Table. They would be different users, so perhaps it is a permssions problem.
    – GordyII
    Jun 6, 2013 at 3:58
  • That would be my guess then, yes.
    – Aaron Bertrand
    Jun 6, 2013 at 3:58

2 Answers 2

Reset to default
2

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.

Improve this answer
1

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]
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.