June 1, 2021 at 1:47 pm
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.
June 1, 2021 at 5:25 pm
I would reach out to the company that provided you the driver. Quick google, a "dbf" file is a dBase file.
Quick bit more of googling and I came across a few articles on connecting a linked server to a dBase file.
This link specifically seemed to have a few good tips to try:
https://stackoverflow.com/questions/10036745/how-to-create-linked-server-to-dbf-in-sql-2008
Not specific to SQL Server 2019, but a few different things to try. It could be configuration related.
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.
June 1, 2021 at 6:45 pm
Hi Brian, Thanks for your quick response.
As the latest version of 'Advantage OLE DB' driver does not work with Windows Server 2019, using that Driver is not an option any more.
I am hoping to create a Linked Server with any driver that can successfully open DBF files from a Folder. I feel it's a configuration related issue. I have tried Access Database Engines 12. 0 and 16.0 but do not have a solution yet.
Stackoverflow post is over 9 years ago, does not seem to work with windows Server 2019.
Thanks.
June 1, 2021 at 7:34 pm
I agree the post is old, but the information is still relevant.
I would check things like it suggests with process monitor to see if MAYBE there is some access denied problems. I would also check logs during the query run.
On a completely different approach to this problem - how much data are you pulling across? I am wondering if MAYBE it isn't that the linked server is hanging, it is that the linked server is pulling across TONS of data. That is unless you are getting an error. Another thought - is your old server connected to that database? Some file-based database systems do not allow multiple simultaneous connections. You MAY need to drop the old linked server connection before you can use the new one.
One way to test that theory would be to make a copy of the database file (the dbf) in a different location and make your NEW linked server connection to the copy of the database file. I am wondering if MAYBE the driver is opening the connection to that file on the old server when the SQL instance starts and is keeping the connection open constantly. If the metadata is stored in a shared location in the dbf file, it MAY be that reading metadata is allowed with multiple connections, but reading actual data is only allowed from 1 connection.
Note - I am just throwing ideas out here. I could be way off on this too.
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.
June 2, 2021 at 12:59 pm
With 'Advantage OLE DB', I did capture System Internals thru Process Monitor in both environments. In 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'.
In addition, I have also tested 'Advantage OLEDB Driver', in Windows Server 2019/SQL Server 2016, with the same failure.
The issue is not with the file size, even if there are only couple of records, the issue remains same.
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.
June 2, 2021 at 12:59 pm
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')
I have tried ACCESS OLEDB Drivers (12 and 16), but is not successful yet. Any solution with 'Microsoft.ACE.OLEDB.12.0' /16.0 would be helpful as well.
Thanks.
June 2, 2021 at 2:42 pm
Do you have the same issue if you use the ACE.OLEDB driver or is it a different error?
Or failing that, what about the Visual Fox ODBC driver? It looks like it supports dbf files:
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.
June 3, 2021 at 2:21 pm
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.
June 3, 2021 at 2:22 pm
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://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7311-database-engine-error
Thanks.
June 3, 2021 at 3:52 pm
My guess then is that you may just need to wait for SAP to provide you with a driver that works with SQL Server 2019.
I am guessing that the DBF file you are using is in a format that the ACE driver just doesn't understand how to interact with.
As a short-term solution, is it possible to install SQL Server 2014 on the Windows Server 2019 box and then upgrade when SAP has an updated driver for you to use?
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.
June 4, 2021 at 12:39 pm
Good morning Brian,
In fact the issue we are having is with Windows Server 2019 and it's new Security features. I have a test environment with Windows Server 2019 / SQL server 2016 and experiencing similar issues. In addition, I have analyzed the system Internals on both (Windows Server 2012 and 2019) environments thru Process monitor. The new environment somehow does not close the file and stuck at FlushBuffersFile operation.
Now I am just looking for a solution with any OLEDB/ODBC driver to open the DBF file in new environment thru Linked Server.
Thanks.
June 4, 2021 at 2:51 pm
I have a thought (may be WAY out to lunch, but just want to suggest it anyway) - what happens if you run SSMS as an administrator and then run the query where you got the permission denied error:
SELECT * FROM OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','dBase 5.0;HDR=NO;DATABASE=F:\TEMP', 'SELECT * FROM TEST.DBF')I am wondering if MAYBE the permission denied error is accurate?
Failing that, what if you change the above query to NOT include the .DBF on the end or even:
SELECT * FROM OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','dBase 5.0;HDR=NO;DATABASE=F:\TEMP', TEST.DBF)or potentially without the .DBF on the end?
I know it is a lot of things to test, but I don't have your environment or files to test it with.
Alternately, using the OPENQUERY:
SELECT *
FROM openquery(TestACE,'SELECT * from TEST.DBF');
The error says it can't find "TEST", so I am wondering if it MAY require the .DBF on the end OR it may need SSMS to be run as an administrator or have permissions adjusted on the file/folder that contains TEST.DBF.
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.
June 4, 2021 at 7:08 pm
Have tried all three queries and have returned error.
Most of the queries pointing to 'the object has no column'. Looks like the DBF structure is not being retrieved properly.
SELECT * FROM OPENROWSET ('TestACE','dBase 5.0;HDR=NO;DATABASE=F:\TEMP\', 'SELECT * FROM TEST.DBF')
--Msg 7357, Level 16, State 2, Line 41
--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.
SELECT *
FROM openquery(TestACE,'SELECT * from TEST.DBF');
Msg 7357, Level 16, State 2, Line 65
Cannot process the object "SELECT * from TEST.DBF". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TestACE" indicates that either the object has no columns or the current user does not have permissions on that object.
Thanks.
June 4, 2021 at 8:00 pm
Any luck running SSMS as administrator?
I am just reading the error that the object has no columns (possible, but unlikely as it works on the old system) or the current user does not have permissions on that object.
Just thinking that MAYBE the problem isn't with the driver but with the permissions.
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.
June 7, 2021 at 8:28 pm
Hi Brian,
Yes, I have tested by running SSMS as an administrator, but no luck.
I am just wondering is there any 64-bit OLDDB driver that can enable to open a DBF file in windows Server 2019 ? Microsoft has stopped support of Visual FoxPro, and the last OLD DB driver available was VFPOLEDB 9 with 32-bit. I have tried Access Database Engines 12 and 16 (both 64-bit).
Really appreciate your time.
Thanks.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply