Solved

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

Posted on 2012-03-21
3
939 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
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

809 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