Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null) Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 8:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
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
Posted Wednesday, August 1, 2012 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,902, Visits: 32,141
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
Posted Wednesday, August 1, 2012 1:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
This job worked before and I don't understand why it is faling now
Post #1338782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse