Solved

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

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell help for creating accounts 283 55
Building JSON Results Table FROM DB 9 38
UPDATE JOIN multiple tables 5 23
Sql server Error message 3 16
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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