Results 1 to 5 of 5

Thread: OpenRowset on a dBase IV file

  1. #1
    Jim Tottser Guest

    OpenRowset on a dBase IV file

    Has anyone had any experience using the openrowset function to access a dbase IV file? I can access the file using ADO from within VB but not getting anywhere using openrowset. If anyone knows the syntax or has an example it would be greatly appreciated.

    Thanks in advance...

    Jim

  2. #2
    Join Date
    Apr 2012
    Posts
    3

    One solution

    This is a REALLY old post but many of the old solutions no longer work. And I'm seeing a LOT of recent requests for this and only a few answers, mostly as obsolete as this post. So here is what I did to make it work in 2012.

    My test server is a 32-bit OS and SQL Server but my production is Win Server 2008 R2 64-bit w/64 bit SQL Server. Since I'm using a newer (at the time of this post) production server with everything in 64 bit, a lot of the solutions out there that succeed on my older test server FAIL on my production server. This adds complications. But it’s not too bad actually.

    There are three steps. We'll get the providers installed on both the 32bit test server and the 64bit production server. We'll turn on the right to do Ad Hoc queries on the servers. Then we'll create and test the queries.

    So let's do this

    1. Setting up the providers.

    1.a. First let’s poke around… Open up SQL Svr Mgmt Studio and connect to the server you’re going to run the queries with. The list on the left generated by the object explorer lists Databases, Security, Server Objects… Stop right there. Expand server objects. Now expand its providers. The "Providers" in the list object tells you what services are available both for setting up a linked server AND for using Ad Hoc OPENROWSET queries.

    Interesting. I see good old MSDASQL, which a lot of old solutions have you use. I got OPENROWSET working with that one on the test server but the 64bit server really hates opening DBF files with it. (In my case I had to suffix it’s name with “.1” for my 32bit server to love me.)

    I found that the best provider for DBF's is installed when you install the Microsoft Access Database Engine. Ya, I know. I saw that shiver. But work with me. It’s actually probably what you used in the past to connect to Excel files or text files. It was just there. But now there’s a new one. And it has a 64 bit twin sister that works.

    1.b. So go to Microsoft and download the regular (32 bit) and/or the x64 version of AccessDatabaseEngine: http://www.microsoft.com/en-us/downl...ng=en&id=13255

    1.c. Copy it into a folder you on the server and run it on the server to install it. There are no options and it just works. Don’t install the regular (32 bit) version for a 64 bit server. It won’t work. If you go back to the object explorer and refresh the list it should be there. But it won’t be listed if you installed the 32 bit version on a 64 bit server. You’ll have to do an uninstall JUST of Microsoft Access Database Engine and install the correct version. (Neither installing nor uninstalling require a server reboot. Their effects are immediate.)

    2. Now you need to enable Ad Hoc queries. By default they will fail. It’s a security thing.

    2.a. Open a new query and plug this into it:
    sp_configure 'show advanced options', 1;
    RECONFIGURE;
    sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    sp_configure 'show advanced options', 0;
    RECONFIGURE;

    2.b. Now run it to turn on Ad Hoc capabilities.

    3. Cool. The server is set and you have a provider we know works with DBFs (and a host of others). So here is what you’ve been waiting for, the secret stuff you feed it whether your server is 32 or 64bit to make it actually do something:
    SELECT AliasOfDBF.*
    FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'dBase 5.0;Database=\\ServerOfDbf\ShareOfDbf\FolderOfDBF'
    'SELECT * FROM [NameOfDbf]'
    ) AliasOfDBF

    Just replace all of the names above that end with “OfDbf” with the names right for your situation and you are ready to rumble. DON’T include the path or the “.dbf” suffix in the NameOfDbf. You used to do that and many solutions tell you to do so. But that was then and this works. You probably won't need the square brackets but they guarantee success. (Since my odd dbf file was named "default" and that's a special word in T-SQL I needed the brackets to convince SQL that I'm not crazy.)

    You can use this chunk of code wherever you’d use any select and you can join with it, etc. (You pros know this but others wondered.)

  3. #3
    Join Date
    Apr 2012
    Posts
    3

    Correction to my previous post - NOTE

    I can't edit my posts and I made a stupid mistake when I converted my SQL to generic form for the above reply...

    So besides replacing all of the "OfDBF" instances with your own real names, you'll also have to add an essential comma I accidentally deleted from my example. It should go right after "FolderOfDBF'".

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You may know it's 12 years old thread.

  5. #5
    Join Date
    Apr 2012
    Posts
    3

    Yep

    Sure I know this is a 12 year old post. Funny, there are 3 month old posts on exactly the same subject all over the internet because the solutions found elsewhere don't work anymore or only in some situations. This old post comes up in the first page of a google search yet until a few days ago had no reply. Seemed like a good place to put it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •