Post reply

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

  • 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

  • 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!

  • August 1, 2012 at 1:26 pm

    #1520495

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply