Linked Server: Using Four Part Syntax

I have tried the "Four Part Syntax" and it fails.
However, the standard "openquery" technique works great.

For example, as displayed in this article:

select * from openquery(AccessDB, 'select * from employees')  '<< works fine (openquery method)

select * from AccessDB...Employees '<< fails (4 part syntax method)

Is this typed correctly?


Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
>>Please close.<<
This is how it works here (from the EE Help):

Nobody answered my question. What do I do?
select * from [AccessDB].DatabaseName.DBO.Employees

I am assuming that AccessDB is your linked server?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Both should work, but I always prefer the OpenQuery method as it is more flexible to solve a certain amount of problems.
now, when you say it fails, what is the error message?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

born4codeAuthor Commented:
Sorry it took so long for me to get back.  I was on vacation.

Okay... the AccessDB is the name of the linked server.
Let me go and check the error message.
born4codeAuthor Commented:

Statement was: select * from * from myTable
Also tried: select * from AccessDB.MainDatabase.dbo.myTable

Error Code is:

OLE DB provider "SQLNCLI" for linked server "AccessDB" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "AccessDB" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "AccessDB". The provider supports the interface, but returns a failure code when it is used.
born4codeAuthor Commented:
Obviously there are no solutions provided in this thread that work.
Please close.
born4codeAuthor Commented:
Interesting... and definitely some more work to do.

Before you ask the Moderators to help you in this circumstance, take the time to post a message in your question stating your intention to have the question deleted or closed, and make sure you have responded to all of the Experts' comments. Then post a question in the Community Support topic area asking the Moderators to delete your question. Remember to post a link to the original question in your request. If there are no comments in the question, one of the Moderators will delete the question. If there are comments, the Moderator will post notice of your request, and will give the participants 96 hours to object. You may be required to post your reason for asking for the deletion, and objections will be taken into account.

I learned more from this than the question results.
Anthony PerkinsCommented:
While I appreciate the points, they are not meritted, please see here (also from the EE Help):

I accepted the wrong answer. Now what?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.