SQL Memory and External Data


The Problem

I have been having problems importing data into SQL Server from Excel file for nearly a year. I would perform an OpenDataSource command in SQL to open an Excel 97-2003 file and import the data into a temporary table. The problem is that it would work once and would then return the following error on every attempt after that, until the SQL Server service was restarted.

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. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

You can also get the following error when performing queries.

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 66669. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'nexgen database, Version=1.0.3945.15002, Culture=neutral, PublicKeyToken=a80b074634e01a5e' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)

I submitted several forum posts over this time trying to elicite some sort of information on how to fix this problem. Here are a couple of the forum posts I made to try to get this problem fixed.

Attempts to Fix It

However, all  of my attempts at getting this resolved always resulted in either a ton of people replying that they’re also experiencing this issue or links to the same old articles. Here are some of the things that MS KB Articles and other resources around the Internet recommended along with some things I tried in order to resolve this problem. There is a mess of other things I tried as well, but I don’t want to have 3 pages of things that don’t work listed here.

  • Make sure the user that the SQL Server service is running as has read and write access to the temp and tmp environment variable folders.
  • Try importing a different file.
  • Try a file that’s local to the SQL Server.
  • Try setting up a Linked Server for the file.
  • Run ASP.NET, SQL Server, memory, file system, registry, process and service traces to try to track down the problem.
  • Install SQL Server 2005 SP3, which according to this article is supposed to fix the excessive open temp tables problem.

Recently I received a message from someone named Jim Cheong. He posted a comment through my first blog post on this site asking whether I had solved the problem in this MSDN forum post. After talking with him he said that he was experiencing the same issues and had possibly come across a fix for it, which he was currently trying out.

The Root of the Problem

He said that the MemToLeave area might be to blame and pointed me towards this SQL Server Central article. I ran this script they have on their site to determine the amount of total available memory and largest free size. I found that I only had 18MB of available memory and the largest free block was 11MB.

With VAS_Summary As (
     Select Size = VAS_Dump.Size,
            Reserved = SUM( Case(Convert(int, VAS_Dump.Base) ^ 0)
                                When 0
                                Then 1
                                Else 0
                            End),
            Free = Sum( Case(Convert(int, VAS_Dump.Base) ^ 0)
                            When 0
                            Then 1
                            Else 0
                        End)
     From (Select   Convert(varbinary, Sum(region_size_in_bytes)) [Size],
                    region_allocation_base_address [Base]
           From     sys.dm_os_virtual_address_dump
           Where    region_allocation_base_address <> 0x0
           Group By region_allocation_base_address
           Union
           Select   CONVERT(varbinary, region_size_in_bytes) [Size],
                    region_allocation_base_address [Base]
           From     sys.dm_os_virtual_address_dump
           Where    region_allocation_base_address = 0x0) As VAS_Dump
           Group By Size)
Select     SUM(convert(bigint, Size)*Free)/1024 As [Memory: Total Avail (KB)],
           CAST(max(size) as bigint)/1024 As [Memory: Max Free (KB)]
From       VAS_Summary
Where      Free <> 0

According to testing by Jim, he found that the OpenDataSource and OpenRowSet commands need approximately 71MB of free memory in order to run. This means that I was far below the necessary amount in order to run. So, the imports would fail until I restarted the SQL Server service, thus freeing up this memory and allowing me to do an import (maybe 2) before it would start failing again.

The Real Fix

As the SQL Server Central article states though, allocating more memory to the MemToLeave pool isn’t as easy as just setting the SQL startup switch. Whatever additional memory you allocate to the MemToLeave pool is taken from the Buffer Pool (BPool), which is SQL’s main operating memory. For this reason, reducing the amount of memory in this pool can have some very undesirable side effects. So, set this responsibly, do testing out of production first and get ready to set it back if things don’t work out the way you hoped, but here is how you allocate more memory to the MemToLeave pool.

  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.

Summary

So, Jim and I setup processes to track how much total available and free memory we had over time. At the end of a week we compared notes. Both of us had well over the necessary amount, even after a week and neither of us had experienced the problem again. For me, it’s been more than 2 weeks now and I have not had to touch the SQL Server service at all. This has been a major life saver and I am eternally greatful to Jim for spotting this solution!


Advertisements

111 thoughts on “SQL Memory and External Data

  1. You post is very useful, which solved our problem.
    We were trying to import Excel/CSV files using OPENROWSET with Microsoft.ACE.OLEDB.12.0, maximum file size tested was 1.74MB. Occasionally, a query could hang forever, which caused subsequent queries to hang as well. The server Activity Monitor would say Wait Type: OLEDB, Wait Resource: External. Killing those session caused them go into KILL/ROLLBACK state, and the sessions stayed there until the server was restarted.
    By adding the -g512 to the startup parameter for the SQL Server service, the problem had gone away.
    Thanks again for the solution!

    1. No problem! As embarassing as it is, I tracked down problems and possible fixes for this issue for almost a year before I ran into Jim and we came across the solution. I can’t believe nothing was posted about this in relation to the OpenRowSet and OpenDataSource problems. So, I figured I had to write a blog post about it to save others the headache. Thanks for the comment!

  2. Thanks a lot. You saved my life ! I spent hours trying to fix this problem. -g256 was enough for my server. Perhaps you could suggest to Microsoft an error message more explicit for this memory problem.

    Thanks again.

    Patrick

    1. Good, I’m glad I could help! I know what you mean. I spent so much time trying to track this down. Me and a colleague were both shaking our heads that such a common issue could have such an undocumented solution. That’s why I wrote the article; to hopefully help people like yourself finally find a fix for this.

      If I get some time, I’ll see about talking to MS about creating some sort of documentation for this problem.

    1. Sam, what option size did you use (e.g. -g512, -g256, etc.)? Please also check the following.

      • Ensure you’re running the query from the SQL Server itself.
        • I’ve had it return null when running the query when not local to the server itself.
      • Make sure file isn’t in use.
        • If the file is in use, it will return a null since it can’t get a lock on the file.

      These are just some things to check. Let me know if you get it resolved.

  3. I was able to get the problem resolved. I think I had the syntax incorrect.

    My original problem was that I didn’t put a ; between the last entry and the -g512.

    Your article really helped put us on the right track to getting our issue resolved. Thanks…

  4. This looks very relavant to the issue and I am please that somebody figured this out. My problem is a little different though. I used the jet 4.0 drivers for openrowset with few issues. Now I’d like to start using the ACE OLEDB 12.0 drivers to view xlsx files. I can, through many reboots and reinstalls, get them to work but the Jet 4.0 drivers stop working. However, once i reboot the server, the Jet drivers work and the ACE drivers stop working. Any clues what i am doing wrong?

  5. This seems to be a little different issue than what I was experiencing… in symptom. However, the source of the problem could be the same if the ACE driver also uses the MemToLeave memory space. I would run the script that I have in this article and view how much free space you have before, during and after imports with both drivers. Often once these imports happen the memory is not freed up for quite some time and even when it is, not all of it is returned to the pool. What I did was create a job that inserted these values into a table every 10 minutes and ran it for about a week. Then I was able to see what was happening with this memory space over time and see where I was dropping below an acceptable memory level, which was causing future imports to fail. I’m glad this article was of use to you. Let me know if you have further questions!

    1. You’re welcome! I’m very glad it solved the problem. It makes me happy to know that I’m saving people all the trouble I had to go through until I found the solution. Have a great day!

  6. I had this issue with an Access database as well. While this worked I was also able to resolve the problem by replacing the Jet Driver reference with:

    Microsoft.ACE.OLEDB.12.0

  7. I wish I could say it has solved my problem but it hasn’t. I’m trying to open an Excel spreadsheet using a query along the lines:

    SELECT * FROM OPENROWSET( ‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\Myfolder\MyFile.xls’,’SELECT * FROM [Sheet1$]’)

    and it fails with the error (SQL2008 R2 Express under Windows 7):

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

    The same code works fine under SQL 2005 Express and Windows XP. It’s driving me potty! The destination file exists. I tried installing Office 2010 in case absence of Excel was an issue but that made no difference.

  8. 2008 r2 is 64 bit. the jet 4.0 drivers are 32 bit and will not work. Install the new ace 12.0 drivers and change your query to this:
    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;Database=c:\MyFolder\MyFile.xls;’, ‘SELECT * FROM [Sheet1$]’);

    That should take care of it.

    1. Sorry I didn’t get to your comments before now. Yea, SQL uses the account that the SQL Server service is running under to access anything from the file system. So, you must ensure that the account has permissions to the file system where you’re going to open files. This gets especially tricky when opening files from shares on another computer, but it can be solved. I’m glad you got this up and working!

  9. Steve, I work on SQL 2008 and Windows 2008 both 32 bit. I’ve been struggling all morning with the Jet driver issue. Changing to the Ace driver saved my day.

  10. When I run the sql statement it gives me

    57 MB & 15 MB in first & 2nd column.

    As you have mentioned that OpenDataSource and OpenRowSet commands need approximately 71MB of free memory in order to run. It means my test system can never run this. Can you please explain what does “-g512” parameter will do?

    Thanks
    Shafiq

    1. Hi Shafiq! The “-g512” parameter takes 512MB of memory from SQL’s primary memory space (BPool or Buffer Pool) and allocates it to the MemToLeave memory space. This memory area is used for things like OpenRowSet, OpenDataSource, SQL CLR, etc. The default is 256MB, which is hardly ever adequate. Please note though (as mentioned in the article) that allocating more memory to the MemToLeave area removes this memory from the BPool area. Depending on the amount of memory your server has, this can cause performance problems and other issues. So, please test this before going live in a production environment. I hope that helps!

      – Nathon

  11. Thanks for the post. It worked for me. I chacked the available memory which was about 56MB. I then simply restarted the service. This was done in our dev environment. I was on the hook because I had promised the open row set was a quick and easy solution. I will adjust the -g512 after analysis of other memory requirements.
    Thanks again to you and your partner…

    Joseph

    1. No problem Joseph. I understand being under pressure to get things fixed immediately. I hope it takes care of the issue for you more permanently.

    1. Great Tatiane! I’m very glad it helped you! Before I wrote this article nothing existed on this problem. It took me over a year to come across the solution. So I’m very glad it is helping so many people not have to go through what I did!

  12. Thank you so much Nathon, for sharing not only the solution but also the explanation.

    I struggled with this for a long time. I’m using SQL Server 2008 R2(32bit) developer edition on Windows 7(32bit).

    1. Maria,

      You’re quite welcome! I’m very glad to help so many people with this. It makes me feel good when I get these comments about having saved people the trouble I went through. It sort of makes up for it having taken me a year to figure out (as embarassing as that is). If you love the article make sure to vote for it! 🙂

      Have a great day!
      Nathon

  13. I have been battling with this problem too – I am importing a range of XLS files, the largest of which is around 50MB. The testing (on smaller test files!) worked perfectly until I ran the job this morning against all the files and was side-swiped by the dreaded non-specific error. My confusion was exacerbated by the the fact that no further imports would run until I restarted in desperation! I was initially disappointed that your “-g512;” solution did NOT work, but I persisted along this line and eventually threw “-g1024;” at it: Bingo, it works! So it seems that this subsystem is quite memory hungry…

    1. Henri,

      Yea, it all depends on your individual configuration, the amount of data being imported and how much other stuff is utilizing those resources.

      As I mentioned in the article though you have to be careful because SQL will “rob Peter to pay Paul”. Whatever you assign to the MemToLeave area above 256MB is taken from the BPool. Since the buffer pool is SQLs primary memory space, you can do real performance damage. So, the best plan is to have a test environment or try it during off hours first.

      Anyway, I’m glad it helped!

  14. Sir, you are a hero! I’ve spent hours on this – Iwas at the point of desperation when I found your site, and sceptically gave it a go. And it works – beautiful. Thank you.

    1. I’m so glad that it helped you as well! I know exactly what you were going through. If you read the other comments you’ll see that I battled this issue on and off for the better part of a year before nailing down the exact cause. It still remains the most popular article on my blog, so it must be helping others. That’s why I posted it, so others don’t have to go through what I did. Cheers!

  15. i getting same error message, but my case i am using sql2000.

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider ‘Microsoft.Jet.OLEDB.4.0’ IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

    it was working well, only since today it is giving error. my other server with same configuration working well.

    can u help on it.

    1. mahbub, sorry for the late reply. I’m starting a new job and trying to move across the state. Regarding the issue you’re having, I would first try normal troubleshooting steps. The error I describe above is recurring. I would check things like ensuring the Tab name in Excel (or whatever you’re importing from) is correct, make sure the file is not open while importing, ensure you have permissions to read the file, reboot the server, etc. Let me know if you still have issues.

  16. Like others, this solved my issue.
    Unfortunately, it looks like the 512 might have caused corruption of my master table as I no longer have my login and the error log shows an issue with

    After this started working, I took it down to 128 and then I took it out completely (each time restarting the service.) It still is working without the -g parameter in place. Very odd.
    Here’s hoping a reboot somehow clears up the master issue.

    1. Paul, I’m sorry to hear that it caused issues for you. I have never heard of it causing master database corruption, removal of accounts, etc. If I were to guess, I’d say something else was at work with those issues. However, I do hope they are resolved when you reboot.

  17. Nathon:
    Yes, fortunately, there were other issues at play that had created the master table problems. So that’s good as it would have seemed strange (although I thought maybe pulling too much ram had caused corruption.)

    But unfortunately, the -g parameter did not fix my problem.
    And I’ve opened up every temp folder I can find. Very odd.

    However, I did get this to work finally with a linked server by using the Access 12.0 driver going to an Excel file. Very weird, makes some sense in a hand waving sort of way. I did find that it needed the file to be local (as others have pointed out. Even mapping a drive wouldn’t cut it.)

    Here is what worked for me:

    EXEC master.dbo.sp_addlinkedserver @server = N’ROADINV_XLS’, @srvproduct=N’Excel’,
    @provider=N’Microsoft.ACE.OLEDB.12.0′,
    @datasrc=N’C:\Temp\test.xls’,
    @provstr=N’Excel 8.0′

    Strange stuff and really, very dissapointing that an issue that so many folks have had problems with is still out there. I wonder if 2008 corrected this.

  18. I have been having a issue with my SQL

    EXEC master.dbo.sp_addlinkedserver @server = N’ROADINV_XLS’, @srvproduct=N’Excel’,
    @provider=N’Microsoft.ACE.OLEDB.12.0′,
    @datasrc=N’C:\Temp\test.xls’,
    @provstr=N’Excel 8.0′

    This worked for me aswell, so thanks Nathan

    1. SolarSun,

      Another happy customer! I’m very happy that it helped you as well. This is consistently the single most popular article on my blog, so you know there’s a lot of folks out there experiencing this issue!

  19. [Censored] M$OFT!!!! YOU SAVED MY DAY!!! LOV YOU NATHON!

    ok seriously, btw, i’m using SQL 2005, and ima constantly backup all my tables since there are reports saying bla bla bla tables might get corrupted….

    thanks again pal, you saved my day 🙂

    1. Adam,

      Ha, ha… now you had to know I’d have to edit that post! 🙂 Anyway, I’m very glad that helped you out! That’s exactly why I published the article. It took me almost a year (on & off) to finally come across the answer to this incredibly frustrating issue! I agree that Microsoft should at least have an article similar to this on how to fix this issue along with the side effects you’d see. Oh, well. It just means that I get a very popular article! Thanks for the comment and have a great day!

    1. Thanks InfoDemers. Yea, as noted in the article I spent a lot of time trying to get to the bottom of this. I’m glad I’m able to save some others the time and hassle of doing the same!

      Have a great day and I hope you don’t run into anymore SQL issues! Thanks for the comment!

  20. Greate solution! With the parameter (-g256;) added to the Startup Parameter, will the available memory decrease after a while? In other words, if the available memory drops, do we need to restart the service to reclaim them back? Thanks

    1. I’m glad this helped you! Unfortunately no, it will not release the memory just by allocating more memory to the Mem 2 Leave area. You will have to restart the service to reclaim that memory if SQL hasn’t reclaimed it itself (which seldom happens). Usually it will allocate memory, won’t release it again and again until it reaches some sort of equilibrium. At that point it will fluctuate as you define CLR assemblies or whatever and will mostly release it afterwards, staying after release of all memory space at a certain level. I hope that helps!

  21. Well the same problem brings me to your blog, i am getting the same error whether its 97-2003 or 2007 excel file. And unfortunately your solution of adding up -g512; also didn’t work.. its sql server 2008 r2. But my real concern is different with so many things involved here is it a good way to transfer data from excel file to sql database. In real live scenario we might don’t have all these permissions on live database server.

  22. When i am trying directly in sql server:
    OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” 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 “(null)”.

    When i am uploading the file from front-end:
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
    OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.

    1. K, the main things to check first are that the file is not already open like in excel or something. Then make sure the account that the SQL service is running as has access to the folder the excel file is in. Generally just tossing the file in the sql data folder temporarily (for trusted excel file and testing only). Make sure the worksheet (tab) name is simple and correct in your statement. If that still doesn’t work try another test excel file. Let me know what you get after trying this stuff. Also copying your actual statement in a comment (or send me a private message with it) would help. Have a great night!

      1. Hi Nathan, I have tried all your steps but the problem still remains. Sheet name is always strange. Since i have office 2007 installed, any 97-2003 file opens in compatibility mode. Now if i upload the file from front end, i am showing the sheets name in dropdownlist. Even though the excel file has only one sheet named as ‘physical verification’, In the dropdownlist it shows many sheets like ‘physical verification$’ , ‘physical verification$_’ , ‘physical verification$Z_hjhjE_h567_hjh56_aku46’. I don’t know why its showing so many sheet names.

      2. There should be a “Contact Author” item in the side column in the about page of my site. This will let you email me. Please just let me know to look for it because it will likely go straight to my junk folder.

      3. Firstly, i would like to thank you for replying so fast on my queries. You are doing a great job. I have send you an email about the issue. Please have a look at it.

      4. Thank you! No problem. I get notified on my phone that I have a new comment so I can respond quickly. I haven’t received the message yet, but I’ll keep checking and let you know when I get it.

      5. HI Nathan, I have send a mail to you regarding the issue i am facing with sample excel file and sql script files. Please have a look at it. Thanks

      6. I am so sorry I haven’t replied again recently. I have been extremely busy where I’m currently on contract. What needs to happen is to get a clean read using just the opendatasource command. Try to find some working examples online and also try using a different Excel file. Once that is working (it can be tempermental) then try using that command in the stored procedure.

  23. No issues, unfortunately my problem has not been resolved till now, and i have change the approach also. Instead of automatic transformation of excel file data to database, User will first upload the excel file and will be given a time period after which he can check out the data at his side. Once the file is uploaded i manually transform the data by running a ssis package and there is no issue coming. One advantage of this approach is that if there is an issue with excel file like in structure or format, it can be corrected before transformation.

  24. After a couple of days looking for the solution I found this.
    I’m running SQL SERVER 2012 on my WINDOWS 8 laptop and most solutions pointed to the jet and odbc options when reading from an xls file.
    After uninstalling office, changing permissions, even changing the registry, I finally found Jim’s and your posts and adding that extra memory space solved everything.
    Thanks for sharing!

  25. Hello all

    I’m trying to fix similiar issue with Microsoft.ACE.OLEDB.12.0 driver and importing data from XLSX file. After few months of reading forums ( even Chinese!!! ) i’m still not able to make this work stable. Using parameter -g1024; or -g512; makes it work for limited period of time (1-2 days). Restarting SQL server fixes problem but i cannot restart production server everyday! I assume that if I can make it work for limited time so it cannot be related with permission problem ( also fixed to be 100% sure) . Any ideas?

    Details

    OS: Win XP 32-bit
    SQL Server 2008 Express R2

    1. Hi Pawel. Yea, it sounds like you’re still running out of mem2leave space. You need to use the included queries to monitor the memory space. Clear the memory by restarting then start monitoring as you import the Excel files. You’ll see it reach a critical mass where it sort of stays at the same level. Then determine how much free memory you have available. If you’re importing large spreadsheets or depending on SQL config you might need to allocate more memory. Just heed my warning in the article because allocating more space takes it away from the primary SQL memory area. You could run into trouble if you allocate too much. Hope that helps!

  26. I’m testing it for mor than 1 hour and my memory is always on the same level +/- 10 MB

    script results:

    Memory: Total Avail (MB) Memory: Max Free (MB)
    527 56

    I’m confused and it makes no sense for me.

    1. The memory pool will fluctuate wildly over different types of operations, especially Excel imports and SQL CLR operations. If you only have 10MB free that is not enough. I would set aside a maintenance period that you can take your production server down during. Then try bumping your mem2leave area to 768MB or 1024MB using the same method you used to put in the 512MB. Test the import operations and normal SQL usage. Also you must have enough RAM in the server to be able to do this as the main SQL memory area will rob memory if it has to, making your change ineffective. I’d recommend no less than 8-16GB RAM in a production server.

  27. Hi Nathan,
    My problem for linked server after providing the parameter on start up is solved, But I am facing the issue when I try to upload the file of size 120,236 KB. But able to upload the file of small size like “290KB”. Can you please suggest solution?

    1. Vijay,

      If you have a lot of memory on the server and your main SQL memory pool (Buffer Pool) is large enough, you could try allocating more memory to the Mem to Leave pool. However as stated in the article this will take memory from the Buffer Pool so be careful and test if before going into production. Either that or be ready to back it out fast.

      My hunch though is that you will not wind up being able to allocate enough to upload a file that size. I would recommend going another route such as splitting the file into smaller sections first or using SSIS to process it.

  28. HI Nathon,

    I read and executed all the above mentioned solution for loading external data. BUT still I am getting error. I want to import excel data.

    OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

    I am using SQL Server 2008 R2 and Office 2007 on windows 7(32 bit).

    Moreover, what should I do after execute the following code for importing data?

    EXEC master.dbo.sp_addlinkedserver @server = N’ROADINV_XLS’, @srvproduct=N’Excel’,
    @provider=N’Microsoft.ACE.OLEDB.12.0′,
    @datasrc=N’C:\Temp\example1.xls’,
    @provstr=N’Excel 8.0′

    I am looking for a solution.
    Thanks

    1. Once you add the linked server you just use it in the FROM section of a SELECT statement. As for why you are still getting this, there are several possibilities. Did you restart the SQL service after changing the setting? Is the file already open by something? Does the SQL service account have permissions to the location of the file? There are a lot of possible causes. My article names a few. Try adding the linked server via SSMS to eliminate any syntax issues. Let me know how it goes!

      1. 1. Restarted SQL server after reconfiguration
        2. The file is not accessed
        3. SQL Server Account has permission to the location
        4. Linked Server via SSMS –

        Above mentioned all tasks has been done, but same error is there.

      2. Did accessing the linked server created via SSMS work? How large is the file you’re trying to open? Are you using the query in my article or performance monitor to monitor the memory availability while trying to open the file? Also is there a password setup in the document? Is SQL server setup under a particular service account or running as local service?

  29. No, linked server did not create via SSMS and shows same error. File size is very small , only 34 KB. SQl server is running on my local PC and login with windows auth.

    1. K, I wasn’t talking about how you login to SQL or what account you setup for the linked server. I’m talking about the account that the SQL server service is using. Most likely there are one of three possible causes. The file is in use, the file is not readable/invalid format, or the account the sql server service is running as does not have permissions to the file or folder it is in. Other than that, make sure your local copy of Office is 32-bit and try to get this working through SQL Server Management Studio (SSMS) first and then script that to a query to eliminate syntax errors.

  30. This worked for me for a while. I currently don’t have my SQL server on a rebooting schedule but have noticed that after some time the available memory goes down and have to reboot the server again which frees up the memory. Is that normal? I also tried -g1000 which caused the problem to not show up as often but still need to reboot. Let me know your thoughts.

  31. May I simply say what a relief to discover an individual who genuinely knows what they’re discussing over the internet.
    You certainly understand how to bring an issue
    to light and make it important. More people must read this and understand this side of the story.
    It’s surprising you are not more popular since you most certainly have
    the gift.

  32. Hiya very cool blog!! Man .. Excellent ..
    Superb .. I’ll bookmark your blog and take the feeds also?
    I’m glad to search out so many helpful information right
    here within the submit, we want develop extra strategies on this
    regard, thanks for sharing. . . . . .

    1. It should be in the SQL Server Configuration Manager, SQL Server Services (on left) and the right-click on SQL Server (MSSQLSERVER) (on right) and select Properties. The last tab at the top should be Advanced. Select this and then toward the bottom you should see Startup Parameters listed. This is where you’ll make the change.

  33. OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” 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 “(null)”.

    after following your instruction I still got that error, excel file is not open, I am using windows 7 and sql server 2005 express edition, I did not experience this error in my windows server 2003 with sql server 2005 express edition.

    1. I would recommend checking the parameter was entered correctly, the service was stopped and started again and then that the file is not in use, permissions are correct and so on.

      I hope you’re able to get it working for you!

  34. Hi Nathon, Your solution was working last week 🙂 but I have to uninstall my sql server due to this https://support.microsoft.com/en-us/kb/956013

    every time I restart my pc I have to do this
    (mofcomp “C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof”)
    in the command prompt. because I cannot open surface area configuration.

    now your solution is working but I have to change the “built-in account” to Local System.
    but I have to use Network Service for my asp.net program.

    thanks a lot for your help

    1. Oops! Uninstalling a shared component because one product uninstalled is definitely a bug! Thanks for letting me know about this. Glad you got it figured out! Have a good one!

  35. I had one instance of SQL Express on my laptop, doday I have installed a second one and there is this problem on a new one.
    Tried all tricks. Old instance works perfectly. What can be a problem?

    1. Did you perform this update on the new instance to change the memory allocation? The process described in the article has to be performed on each instance of SQL server. Otherwise be sure to check permissions since the new instance might be running under a different account.

  36. any suggestion ? also i see that this issue was not there in windows 2003 but when we migrated from 2003 to 2008, issue was arrived. I put a fix of -512 where issue was gone for some time but now again arrived. Please help.

    1. Hi Atul. The article actually contained several possible causes for the symptoms. Have you checked that the SQL service account the instance is running as has access to the file, that the file isn’t in use, etc? All of those are more likely causes. The g512 option (make sure you have the g) is a last resort.

      1. Nathon.. Thanks for quick comments. In my case whats happening is, when issue arises, i restart the SQL service then issue gets resolved temporarily. After around 4 days issue starts encountering once again. So not sure what exactly is the cause. Do you see some suspect here?

      2. I’d say that could be a memory problem. I’d check to be sure you entered the memory change correctly and reboot the system. Let me know how that works out.

  37. Nathon, i just checked whether memory change is correctly implemented or not where it is implemented correctly and also server was rebooted when change was done.

      1. Sorry I wasn’t able to give a very thorough reply last time. I was in a meeting. What I’d do now is setup memory counters to track the memory of the SQL memory queues. Reboot the server and watch them over the next few days. If they consistently increase in memory usage and then when nearly full the external data reference fails, it’s likely a memory issue. Depending on the size of file 512MB might not be enough for you and you’ll have to adjust accordingly.

        If, however, the memory doesn’t get full you need to look elsewhere. It could be a problem with a .NET CLR having a memory leak since it uses the same memory queue or something else.

  38. Nathon, Also issue is something like.. we havent received such issue in windows server 2003 where last to last month we had upgradation to windows 2008 32 bit and since then issue started. Do you see something with this upgradation thing as well?

    1. There could be many issues with a new version. There could be differences with memory requirement, service account, permissions, etc. There could also be previous fixes that weren’t implemented on the new server.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s