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

Question

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

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Do the Access Database Engine drivers work on Windows Server 2012?

What I am thinking is we know of a bug in the Advantage driver in Windows Server 2019, so lets try to replace that driver on the old server and make things work on that box. Then you should be able to configure things to match on the new box.

As for 64-bit vs 32-bit, I am not certain. If your DBF files were created in a 32-bit dBase (or whatever tool it was), you MAY require a 32-bit driver to get to the data which could be part of the problem.

Any chance you can import the DBF files into SQL Server on the 2012 server (where things work) and retire the DBF files in favor of SQL Server?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

thomas.p.chacko@gmail.com SSC Enthusiast Points: 187 More actions · Answer 2

I have tried Access Database engine on Windows Server 2012, and gets similar error.

Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "DBFServer16". The provider supports the interface, but returns a failure code when it is used. (.Net SqlClient Data Provider)

Somehow, none of the drivers are able to retrieve the table structure (schema) from the dbf file, which is created in a 32-bit environment.

I have also tried defining an ODBC connection in both odbc32-bit and odbc-64-bit to create a Linked Server, but wasn't successful.

Thanks.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Was doing a bit of googling on the ACE 12 and 16 drivers and found this article:

https://techcommunity.microsoft.com/t5/sql-server-support/dbf-file-name-is-truncated-to-8-characters/ba-p/1493456

Apparently in some versions of the driver, the file name is truncated to be the 8.3 file name. Could this be the issue with the driver? do you have a DBF file that has a file name under 8 characters (such as TEMP or TEST) to try?

My ONLY other thought is that your DBF file(s) are something specific to the Advantage driver and won't work with the ACE driver. OR the DBF file may be locked by something. Might not hurt to do a trace with process monitor and see what is happening when you go to do that SELECT out of the DBF file. when it is giving the error.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

thomas.p.chacko@gmail.com SSC Enthusiast Points: 187 More actions · Answer 4

I did try changing the filename to 8.3 format, but no luck.

Also, the folder we have DBF files are set to 'Everyone' 'Full Control' for testing purpose.

I remember reading about a TEMP folder not having permission under 'C:\Program Files' sub folder. That was an old post. The folder/sub-folder structure is very different in Windows Server 2019. I will have to find that article to see whether creating that folder structure would help.

Thanks.