Post reply

Problems with SQL2005 64 Bit -> Openrowset export to DBF

  • November 6, 2006 at 9:17 am

    #193001

    Hi Everyone,

    I am at my wits end trying to get one simple export to work using openrowset.

    I am running SQL 2005 Enterprise Edition running on an English verison Win 2k3 64 Bit Enterprise Edition.

    I want to use the following script to export information into a dbf file:

    insert

    into

    OPENROWSET

    (

    'MSDASQL',

    'Driver={Microsoft dBase Driver (*.dbf)};DBQ=\\SERVER1\import_export\data\',

    'Select * from export')

    select

    column1,

    column2,

    column3,

    column4

    from

    exporttable(nolock)

    I get the following error whilst trying to run the script (might not be 100% english error message, I work on a german version of management studio!)

    An instance of the ole db provider "MSDASQL" could not be created for the linked server "(null)".

    I had the exact same script running on an SQL 2000 Server and it didn't moan.  However, if I look at the data access drivers on the new server (Admin Tools -> Data Sources) I can only see 2 drivers where the 2000 server had about 15.

    I found a tip somewhere else that the MSDASQL dll needs to be registered to work properly, I did that to fix a problem before getting this far.  I wondered if MDAC needs to be reinstalled, but I tried that too.  There seems to be no way of doing an openrowset to export the information.

    Does anyone have any more ideas where I might be going wrong?  Please be aware of the following things:

    • The output has to be DBF (target system runs an old clipper database and cannot be updated)
    • The system needs to be faster than greased lightning (No SSIS for us, far too slow for what we need)
    • Runs/should run in a sproc to allow for scheduling

    I would be much obliged for any ideas that you may have.

    Regards

    William

    Regards,

    WilliamD

  • November 9, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • April 16, 2008 at 6:53 am

    #803056

    Last tiem I checked.. .a 64 bit Jet driver does not exist.

    Mathew J Kulangara
    sqladventures.blogspot.com

  • April 17, 2008 at 4:18 am

    #803566

    There are no 64 bit Jet Drivers. In those cases I use SSIS in 32 bit mode as a replacement. It's slow, but beats doing it by hand 😉

    Xander

  • October 22, 2008 at 1:51 pm

    #888333

    Few days back even I had the same problem , I works well on 32 bit servers and fails on the 64 bit servers, I chaged the code because of it. The Production DBA's we not willing to install the drivers.

    Please check the drivers for "jet 4.o" (this is not the excat name but close one)

    I am not sure how much this helps you.

Viewing 5 posts - 1 through 4 (of 4 total)

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