Solved

Problem with linked server to Advantage Database Server 10.10 64-bit

Posted on 2012-03-21
3
985 Views
Last Modified: 2012-03-22
I've set up a linked server to ADS 10.10 64-bit.  I'm using SQL 2008 R2 Express 64-bit.  I installed the ADS Ole DB provider 10.10 64-bit.  I set up my linked server as follows:

exec master..sp_addlinkedserver
                   @server = 'CLink'
                  ,@srvproduct = 'Advantage OLE DB Provider'
                  ,@provider = 'Advantage OLE DB Provider'
                  ,@datasrc = 'E:\compulink\Eyemd'
                  ,@provstr = 'TableType=ADS_CDX; ServerType=ADS_REMOTE_SERVER; SecurityMode=ADS_IGNORERIGHTS; TrimTrailingSpaces=true; DbfsUseNulls=true; LockMode=ADS_PROPRIETARY_LOCKING;'
            exec master..sp_serveroption @server='CLink', @optname='data access', @optvalue='true'
            exec master..sp_serveroption @server='CLink', @optname='use remote collation', @optvalue='true'

I use a script for this and it's always worked on other sites.  I tried setting "Allow in-process" for the provider options, and no difference.  I've tried various account options, all of which should have worked and no joy.  SQL's logon account is local system, so it should have the permissions it needs to access local files.

The error I get when I try to do a select * from openquery(....) is:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Advantage OLE DB Provider" for linked server "clink" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1

What's interesting is that if I change the query to:

   select * from openquery(Clink,'select * from appt where 1=0')

then I get the column headers from appt with no errors.  It's when it tries to fetch rows of data that I get the error.  I also get this same error if I execute sp_tables_ex Clink.

The server is Windows Server 2008.  It's pretty tightly locked down.  Any chance something in the server config is preventing this from working?

Any ideas appreciated.
0
Comment
Question by:Brett_Gerlach
[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
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
TempDBA earned 500 total points
ID: 37751051
Allow inprocess for the linked server
Go to  Management Studio Express ->
         Server Objects ->
              Linked Servers ->
                   Providers ->
                        Advantage OLE DB Provider (Right Click->Properties or Double Click)
                        Set "Allow Inprocess"
0
 

Author Comment

by:Brett_Gerlach
ID: 37752594
As mentioned above, I already tried that with no joy.  Other ideas?
0
 

Author Closing Comment

by:Brett_Gerlach
ID: 37754877
After checking this again this morning, it seems to be working now.  I don't know why it wasn't working last night.  Maybe I needed to close and re-open the mgmt studio and flush my sessions for the Allow inprocess to work.  Anyway, the solution suggested was the only reasonable thing to try, and may have contributed to the resolution, so I'm going to call it correct.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

717 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