August 1, 2012 at 8:41 am
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
August 1, 2012 at 8:44 am
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
August 1, 2012 at 1:26 pm
This job worked before and I don't understand why it is faling now
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy