?
Solved

Linked Server: Using Four Part Syntax

Posted on 2006-06-26
8
Medium Priority
?
382 Views
Last Modified: 2010-05-18
I have tried the "Four Part Syntax" and it fails.
However, the standard "openquery" technique works great.

For example, as displayed in this article:
http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/4/

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

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

Is this typed correctly?

Thanks.



0
Comment
Question by:born4code
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16989063
select * from [AccessDB].DatabaseName.DBO.Employees

I am assuming that AccessDB is your linked server?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16989917
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?
0
 
LVL 2

Author Comment

by:born4code
ID: 17075854
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.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 2

Author Comment

by:born4code
ID: 17085711

Statement was: select * from AccessDB...select * 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.
0
 
LVL 2

Author Comment

by:born4code
ID: 17334061
Obviously there are no solutions provided in this thread that work.
Please close.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 17374316
>>Please close.<<
This is how it works here (from the EE Help):

Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
0
 
LVL 2

Author Comment

by:born4code
ID: 17381092
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17381899
While I appreciate the points, they are not meritted, please see here (also from the EE Help):

I accepted the wrong answer. Now what?
http://www.experts-exchange.com/help.jsp#hi17
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question