Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now