Opening DBF Files thru Linked Server in Windows Server 2019/SQL Server 2019 environment

Question

I am in the process of migrating a Database from Windows Server 2012/SQL Server 2014 environment to a Windows Server 2019/SQL Server 2019. I have a Linked Servers defined with 'Advantage OLE DB' provider to open .DBF files. As SAP does not have a latest Driver for Windows Server 2019, the Linked server hangs after few successful runs.

I have tried many changes with this Driver and also have tried Access Database engines 'Microsoft.ACE.OLEDB.12.0' and 'Microsoft.ACE.OLEDB.16.0', however, still not able to open DBF files.

I can see the list of DBF Files by expanding defined Linked Server/ Catalogs / Default / tables / System Tables.

Any help/advice is solicited..

Thanks.

Answer 1

Hi Criszhan,

Regarding 'Advantage OLEDB Driver', yes, it works fine on Windows Server 2012/SQL Server 2014 environment.

I do not get any error in Windows Server 2019/SQL Server 2019, after few runs it just hangs.

I did capture System Internals thru Process Monitor in both environments. Old environment, 'Operation' reports 'Create file', Read file' and ultimately a 'close file', where as in new environment, operation is 'Create file', Read file' and continuously try to 'FlushBuffersFile'. Never completes that operation and never 'closes the file'.

I would like to use any other OLE DB 64 bit driver to create a Linked Server to open DBF files, so that there is no code change is required. I should be able to access the file thru 'SELECT * FROM OPENQUERY (Linked Server, 'SELECT * FROM DBF File')

Thanks.

Answer 2

In addition, I have also tested 'Advantage OLEDB Driver', in Windows Server 2019/SQL Server 2016, with the same failure.

Per SAP forums, this is a known issue.
Michael Loop
Posted onAug 12, 2020 at 03:45 PM
Windows Server 2019 has introduced some new security settings that interfere with the ability for the LocalSystem service to create the link to ADS. Our developers are working on a fix to adapt to these new requirements.

I have tried ACCESS OLEDB Drivers (12 and 16), but is not successful yet. Any solution with 'Microsoft.ACE.OLEDB.12.0' or 16.0 would be helpful.

Thanks.

Answer 3

Hi,

Make sure that using this driver in the Windows Server 2019 environment is no different from using it in Windows Server 2012 in terms of settings.

It seems that this 'Advantage OLEDB Driver' does have problem when used in the Windows server 2019 environment according the imformation you got from SAP forums. Unfortunately, the problem does not seem to have been resolved even though the news about the fix was released last year. Maybe you can try to ask about the progress of this fix.

Sorry I am unfamiliar with .DBF files and have no experience in using linked server to access them.
What errors did you encounter when using ACCESS OLEDB Drivers? I have seen some people using them to access .DBF files on some posts on the Internet, but they are for earlier SQL Server versions.

Answer 4

HI,

I have tried both OPENQUERY and OPENROWSET to access DBF files, but both fails with following ERRORS..

SELECT * FROM OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','dBase 5.0;HDR=NO;DATABASE=F:\TEMP', 'SELECT * FROM TEST.DBF')

Msg 7357, Level 16, State 2, Line 53
Cannot process the object "SELECT * FROM TEST.DBF". The OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" indicates that either 
the object has no columns or the current user does not have permissions on that object.

Completion time: 2021-06-03T06:02:59.9636708-07:00

--OPENQUERY
SELECT *
FROM openquery(TestACE,'SELECT * from TEST');
GO

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TestACE" returned message "The Microsoft Access database engine could not find the object 'TEST'. 
Make sure the object exists and that you spell its name and the path name correctly. If 'TEST' is not a local object, check your network connection or contact the server administrator.".
Msg 7350, Level 16, State 2, Line 36
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TestACE".

Completion time: 2021-06-03T06:01:38.1065263-07:00

Thanks.

Answer 5

Hi,

Also, when I try 'SELECT TO' within Linked server, gets the following error.

TITLE: Microsoft.SqlServer.Smo

------------------------------

Enumerate columns failed for LinkedServer 'TESTACE'.

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46367.54+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Enumerate+columns+LinkedServer&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TESTACE". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)

For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7311-database-engine-error

Thanks.