none
Linked Server using Microsoft.Jet.OLEDB.4.0 problem

    Question

  • Hi all,

    I'm using following code to create linked server and then access data

    EXEC sp_addlinkedserver N'XLS',

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'C:\Data\Produkthierachie.xls',

    NULL,

    'Excel 5.0;'

    EXEC sp_addlinkedsrvlogin N'XLS', FALSE, NULL, Admin, NULL

    SELECT * FROM XLS...SWProdukthierachie$

    When executing the select statement I get following error message

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLS" returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Line 1

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

    Strange is that when I run this code on dev machine it works fine. But when I try to execute it on my second (pilot) machine it ends with the error message posted. I'm not a admin on the pilot machine so I don't know it's exact configuration but I'm beeing assured that the only difference is that on pilot machine there is no Visual Studio installed.

    I'm running SQL Server 2005 9.0.1399.

    Could anyone give me some hints?

    Thx

    fleshi

    Thursday, February 09, 2006 10:30 AM

All replies

  • I've seen a couple of fixes for this.  The one that saved me was granting update file system permissions to the temp directory for the sql service login to whoever needed to access that linked server:  C:\Documents and Settings\(sql login name)\Local Settings\Temp.  Good luck!
    Thursday, October 04, 2007 12:12 AM
  • I found one other thing that helped when this error message mysteriously returned, I configured MS DTC to allow remote transactions.  From Administrative Tools, select Component Services.  Expand Component Services until you find the SQL Server.  RIght click on the SQL Server and select Properties.  Click on the MS DTC Tab and select Security Configuration.  Check the "Network DTC Access" box & "Allow Outbound".

     

    Tuesday, October 16, 2007 8:19 PM
  •  

    Hello,

     

    I also had this problem. I used the Jennifer's suggestion and I also restated SQL service, and finally worked.

     

     

    Thursday, October 02, 2008 5:52 PM
  • check this solution.  It solved my problem

    http://support.microsoft.com/kb/814398
    • Proposed as answer by gazza_sc Wednesday, May 20, 2009 10:53 AM
    Thursday, December 04, 2008 2:58 PM
  • Hi Guys,

    I know this is a pretty old post now, but been looking for a while myself on this issue and this was the one i found most useful so just wanted to say thanks,

    Cheers :o)
    Tuesday, April 07, 2009 11:10 AM
  • Hello Jennifer,  I am getting the same messages as above on a laptop running MS SQL.  But I am having trouble locating the Component Services in Vista, any suggestions?

    Thanks,
    John
    John
    • Proposed as answer by Irfan Najmi Thursday, May 07, 2009 5:16 PM
    Friday, April 24, 2009 2:03 PM
  • Hello Jennifer,  I am getting the same messages as above on a laptop running MS SQL.  But I am having trouble locating the Component Services in Vista, any suggestions?

    Thanks,
    John
    John

    I used the following code and was able to import the data successfully from excel using Jet OLE DB.

    Exec

     

    master.dbo.sp_configure 'show advanced options', 1

    Reconfigure

    Exec

     

    master.dbo.sp_configure 'Ad Hoc Distributed Queries',1

    Reconfigure

    Exec

     

    master.dbo.sp_configure 'OLE Automation Procedures', 1;

    Reconfigure

     

    Exec

     

    master.dbo.sp_configure 'Agent XPs', 1;

    Reconfigure

     

    Go

    --

    Select

     

    * From OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=X\XXX\XXX.xls' , [Sheet1$])

    --

    Exec

     

    master.dbo.sp_configure 'show advanced options', 1

    Reconfigure

    Exec

     

    master.dbo.sp_configure 'Ad Hoc Distributed Queries',0

    Reconfigure

    Exec

     

    master.dbo.sp_configure 'Ole Automation Procedures', 0;

    Reconfigure

     

    Go

    Thursday, May 07, 2009 5:20 PM
  • k, I have been fighting this issue for a while and have finally figured out what was happening in my situation. If you're experiencing the error message below, you might also want to check these possibilities.

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

    1. Make sure that something or someone doesn't have the file that's trying to be accessed open. It will give this error message if it can't access the file, regardless of why.
    2. If you're having a problem where accessing the the file works once, but then you have to restart the SQL Server service before it will work again, make sure that the procedure accessing the file isn't inside of a transaction. For some reason, when it's inside of a transaction it never releases the lock on the file when it's done with it (I'm assuming this is the issue), so the next time it tries to access the file it fails with this error.

    I hope this helps someone!
    Nathon


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Friday, January 15, 2010 4:52 PM
  • I solved this problem in a different manner. Now the error is not coming up.

    1. Copy the excel file into DATA folder. Ex: C:\program files\sql server\ms sql\data

    2. Process the excel file using openrowset method.

    3. Delete the excel file from the DATA folder.

    4. I guess, there are some special privilages needed to process the excel file.

     

    Hope it helps.

    Wednesday, March 24, 2010 10:37 PM
  • I actually recently found out that this was due to the MemToLeave memory area running low on memory, thanks to a friend of mine (thanks Jim). It turns out that by default only 256MB of memory is allocated to this area. The OpenRowSet, OpenDataSource, SQL CLR, etc. all use this area. The Jet driver might have some leaks under certain conditions that cause this memory to be consumed. From Jim's information, this needs to be around 71MB or more for the OpenRowSet and OpenDataSource to work.

    To modify this area, edit the advanced options for the SQL Service (using SQL Configuration Manager) and put in the "-g512;" option with the other existing ones. This allocates an additional 256MB of memory from the Buffer Pool (BPool) area to the MemToLeave area. This can cause side effects if you don't have a lot of memory (like performance and other issues), so be careful. Since making this change both Jim and I have not had further problems with this.

    Something to try. :)

    The blog post I wrote detailing this fix is here.

    http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    • Proposed as answer by Nathon Dalton Wednesday, October 27, 2010 8:27 PM
    Wednesday, March 24, 2010 11:07 PM
  • Thanks for your answer is the best option.

    Thank you

    Thursday, April 15, 2010 4:31 PM
  • Perfect. You had the solution in my case. The spreadsheet was in use by me and that was the problem.

    Thanks a lot.

    Bertonio1

    Wednesday, April 21, 2010 5:22 PM
  • Thank you very much.

     

    It solved my problem.

    Tuesday, May 04, 2010 9:03 AM
  • Hi 

    I am getting the same messages but when i restart my server every think works again

     

    Monday, May 10, 2010 11:32 PM
  • By the way, I did wind up solving this problem. I wrote up a blog post about it. You can see the source of the problem and the solution at my blog here.

    http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Thursday, May 13, 2010 4:54 PM
  • SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\Test_Projects\NewAdminWebSite\Masters\Format\BSC_KRA_Departmental_Directory.xlsx''')...[Sheet1$]

    When I fired above query on sql server management studio,the following error shows.

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".



    Could you suggest me,what I'll do? Thanks.
    Wednesday, October 27, 2010 8:55 AM
  • Nil,

    Yes, I wrote an article about how to resolve this issue on my blog.

    http://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/

    This is ALWAYS the top blog post on my site since it's such a common issue. It is due to a misconfiguration in SQL, which is there straight out of the box. There is not enough memory allocated to the MemToLeave memory space by default. If you read the article you'll see how to change this.


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    • Proposed as answer by Nathon Dalton Wednesday, October 27, 2010 8:27 PM
    Wednesday, October 27, 2010 8:26 PM
  • Nathon,

    Good Morning.Thanks for the reply.

    I visited your blog and follow these steps.

    1. Open SQL Server Configuration Manager.
    2. Select the SQL Server Services folder in the left pane.
    3. Right-click the SQL Server (MSSQLSERVER) service in the right pane.
    4. Click Properties.
    5. Click the Advanced tab in the properties dialog that pops up.
    6. Add “-g512;” to the front of the value for parameter “Startup Parameters”.
    7. Click OK.

    But for point 6,"Startup Parameters" is not in the list.

    I used SQl Server 2008.Still the problem is not solved.

    Thursday, October 28, 2010 6:54 AM
  • Can you send me a screen shot of the dialog box that is showing when you finish step #5? It might not be called exactly "Startup Parameters" depending on your version, but it should contain something like the following.

    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<br/>master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\<br/>MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\<br/>MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    

    I have SQL Server 2008 R2 and my advanced tab shows the following items, in order.

    Clustered
    Customer Feedback Reporting
    Data Path
    Dump Directory
    Error Reporting
    File Version
    Install Path
    Instance ID
    Language
    Registry Root
    Running under 64 bit OS
    Service Pack Level
    SQL States
    Startup Parameters  <-------- This one
    Stock Keeping Unit ID
    Stock Keeping Unit Name
    Version
    Virtual Server Name

    Keep in mind that you might have to scroll down to see the Startup Parameters item.


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Thursday, October 28, 2010 2:34 PM
  •  Nathon,

    I checked the all parameters.Used SQl Server 2008 and dont know whether its R1 or R2.Only "Startup Parameters" is absent.I can't able to send screen shot.

    Clustered
    Customer Feedback Reporting
    Data Path
    Dump Directory
    Error Reporting
    File Version
    Install Path
    Instance ID
    Language
    Registry Root
    Running under 64 bit OS
    Service Pack Level
    SQL States
                                           Startup Parameters  <-------- Only this one is absent.
    Stock Keeping Unit ID
    Stock Keeping Unit Name
    Version
    Virtual Server Name

    Friday, October 29, 2010 5:25 AM
  • In Sql Management Studio go to Server Objects - Linked servers - Providers - Microsoft.Jet.OLEDB.4.0 then Options and Uncheck "Allow Inprocess".  This fixed the same issue I had when linking to a MDB file.  I think a service pack changed the setting.
    Wednesday, July 27, 2011 7:16 PM
  • Hi Nathan,

    Even i am using Microsoft SQL Server 2008 R2.

    I have followed your steps as specified. But still i am getting the same error.

    Could you please guide me to resolve the issue.

    Sunday, August 07, 2011 3:48 AM
  • Akhianil,

    Are you in a 32-bit or 64-bit environment? If it is 64-bit, it will not work:

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/45aa44dd-0e6f-42e5-a9d6-9be5c5c8fcd1/


    Argue your limitations and sure enought they will be yours. - Richard Bach, Illusions
    Monday, August 08, 2011 12:48 PM
  • After hours of lookout, this finally worked for me
    Wednesday, August 24, 2011 11:35 PM
  • Nathan,

    Yes, I am using 64 bit  machine. Thanks for you suggestion.

    Thursday, September 08, 2011 2:28 PM
  • Hi Nathon

    It works after adding  -g512  in the startup parameter!

    Thanks

    Cheers!

    Monday, February 18, 2013 4:35 PM