Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 6 '13 at 9:20

This question came from our site for professional and enthusiast programmers.

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 '13 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 '13 at 3:58
    
That would be my guess then, yes. –  Aaron Bertrand Jun 6 '13 at 3:58

2 Answers 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]
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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