Problems with SQL2005 64 Bit -> Openrowset export to DBF

• w.durkin@online.de

  Hall of Fame

  Points: 3943

  More actions

  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

• Mathew J Kulangara

  SSCommitted

  Points: 1610

  More actions

  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

• XanderK

  SSCrazy

  Points: 2487

  More actions

  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

• Nirmal-654077

  SSC Veteran

  Points: 213

  More actions

  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.