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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Peter Larsson » Cannot initialize the data source object of...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Krasavita

Posted Wednesday, August 01, 2012 8:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

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

Post #1338592

Lowell

Posted Wednesday, August 01, 2012 8:44 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 11,605, Visits: 27,647

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1338595

Krasavita

Posted Wednesday, August 01, 2012 1:26 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, April 19, 2013 8:15 AM
Points: 1,251, Visits: 1,840

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

Post #1338782

« Prev Topic | Next Topic »

Permissions