Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)

• Krasavita

  SSC-Insane

  Points: 23138

  More actions

  August 1, 2012 at 8:41 am

  #260059

  Hello,

  I run this code and run error:

  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)

  insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;Database=\\DBtest\test.xls;',

  'SELECT * FROM [Sheet1$]') select Region_name, department, job_title, employee,

  cell, business, extension, home, vip

  from DBtest1.JUT.dbo.Test order by 1,2,3

  The file is there I checked,but excel is not installed on the server what that matter?

  Thank you

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 1, 2012 at 8:44 am

  #1520325

  the jet driver would already be on a normal server, but only works from 32 bit installations, ie SQL 2005;

  if you are on a 64 bit SQL server, you need to install the ACE drivers (probably witht eh /passive switch), and use

  something like this:

  SELECT * FROM OPENROWSET('MSDASQL',

  'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

  UID=admin;

  UserCommitSync=Yes;

  Threads=3;

  SafeTransactions=0;

  ReadOnly=1;

  PageTimeout=5;

  MaxScanRows=8;

  MaxBufferSize=2048;

  FIL=excel 12.0;

  DriverId=1046;

  DefaultDir=C:\Data\BlockGroups_2010;

  DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

  'SELECT * FROM [AK$]')

  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!

• Krasavita

  SSC-Insane

  Points: 23138

  More actions

  August 1, 2012 at 1:26 pm

  #1520495

  This job worked before and I don't understand why it is faling now