help needed with data access

• sql2k8

  Hall of Fame

  Points: 3455

  More actions

  May 12, 2011 at 10:56 am

  #250172

  Hi,

  I am trying to access the below query on sql server 2008 r2 which is present on windows server 2008 enterprise edition - 64 bit

  SELECT [CITY]

  FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="\\ServerName\FolderName\test.xls";Extended properties=Excel 5.0')...[testname$]

  i am getting the below error message

  Msg 7403, Level 16, State 1, Line 1

  The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

  when I tried to register the below files they were successfully registered

  Msjetoledb40.dll

  Msjet40.dll

  when I tried to register the below files they were not successfully registered

  Mswstr10.dll

  Msjter40.dll

  Msjint40.dll

  Please help me in fixing the issue.

  Thank You very much

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 12, 2011 at 11:05 am

  #1323834

  you cannot use 32 bit drivers on a 64 bit instance; the JET drivers are 32 bit, so you'll have to download the ACE drivers from microsoft instead;

  i had a problem on my laptop where i had installed 32bit Office 2007, which prevented me from installing/registering the ACE 64 bit drivers; I had to uninstall office to get those drivers in place.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• sql2k8

  Hall of Fame

  Points: 3455

  More actions

  May 12, 2011 at 1:56 pm

  #1323953

  which version of the driver should i use on 64 bit machine.

  I see both 32 bit and 64 bit for ace

• sql2k8

  Hall of Fame

  Points: 3455

  More actions

  May 12, 2011 at 1:58 pm

  #1323957

  i was able to install 64 bit and 32 bit of ace drivers on the 64 bit machine.but 32 bit gave me only one error messager. where as 64 bit gave me 2 error messages. but the results were not retrieved from both of the drivers

  Please help me

  Thank You