Post reply

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

  • May 14, 2009 at 4:21 pm

    #384592

    I've been using the following stored procedure to pull data into a table from an Excel workbook generated on a client workstation:

    INSERT INTO

    [dbo].[IV40600]

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Someserver\clientshare\IM 9.0 Samples\UserDefinedPURCH3.xls', [ADMIN$]

    I tested this on the server and it worked beautifully. However, I tried it on a client workstation and obtained the following error:

    Msg 7303, Level 16, State 1, Procedure zspIMCatValuesPURCH3, Line 4

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

    I found a great article that described how to set permissions for the TEMP folder for that user. However, I'm now receiving this error while logged into the server as administrator. The sproc was created with ANSI Nulls and Quoted Identifier on, and OPENROWSET has been enabled.

    Any and all assistance is greatly appreciated.

    Thanks!

  • May 14, 2009 at 9:49 pm

    #995039

    Hello,

    Might be worth trying with the alternate driver suggested in this article:-

    http://omensblog.blogspot.com/2008/05/opendatasource-or-openrowset-linked.html

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • May 15, 2009 at 6:35 am

    #995251

    Hi John,

    Thanks for your reply. Sadly, it did not work; I get the same message, but this time it refers to the new driver.

    I gladly welcome more ideas.

    Thanks again,

    Trish

  • May 15, 2009 at 11:24 am

    #995540

    Hi Trish,

    One more thought - when you execute the SP are you signed onto the SQL Server with a SQL or Windows Login?

    From my understanding, if you use a SQL Login then the account under which the SQL Service runs will be used to access the Excel file. If that is the case then what account is SQL Service Running under? Is it a Windows Domain Account that has permission to access the directory on the Client?

    If you are signed on to the SQL Server with a Windows Login, then that account will be used to access the Excel file. In which case, check that you can open the file Okay via Windows Explorer on the SQL Server.

    Regards,

    John

    www.sql.lu
    SQL Server Luxembourg User Group

  • January 25, 2010 at 12:18 pm

    #1108327

    I had same problem and everything was fine,temp folder the user I used. everything.

    I traced different servers and different applications to check what happened behind OLE DB jet engine stuff. When I looked at traces I noticed that work flow was different, but when you stop SQL server service and start and run the excel stuff(below script) it was following same root.

    Then it showed me SQL server keeps some configurations when some stuff runs first time.

    Such as below script. When it runs on SQL server first time, it reads user permissions and other settings and after that it uses them and it does not recheck them again.

    SELECT esn from

    openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;

    Database=p:\Promo\Promo_Temp.xls',

    'Select esn from [Sheet1$]')

    In our situation, probably something changed the settings maybe an application or after first restart similar script ran with lower permissions and SQL server started using them.

    So for solution:

    I just restarted SQL server service and I ran above script with higher permission user.

    I hope it helps...

    Rabia

Viewing 5 posts - 1 through 4 (of 4 total)

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