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:
@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.