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

Lowell

SSC Guru

Points: 323504

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!