Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-21
3
Medium Priority
?
1,031 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

704 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