none
Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

    Question

  • I have been trying to figure out for some time why Linked Servers do not continue to run.  I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider.  I then run some queries to that access those linked servers without any problems.  (One currently is called 'THE')

    After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers.  No data, queries, tables, or anything is accessible to me anymore.  I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

    The only solution I have to fix this currently is to restart the SQL Server Database service.  I have tried deleting and recreating the Linked server before doing this to no avail.  I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success.  After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

    Any help with this is greatly appreciated.

    Mark.

    P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

    Tuesday, December 05, 2006 3:22 AM

All replies

  • This issue has been reported fixed with Sql Server 2005 SP1. 

    You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

    -Curtis

     

    Tuesday, December 05, 2006 11:19 PM
  • I was unable to find any documentation that said this problem was fixed.  Can you please direct me to KB article that describes this fix as our company documentation requires this information. 

    Any information that you can provide would be helpful.

    Thank You

    Mark

    Wednesday, December 06, 2006 3:55 PM
  • hi mark,

    I  think a sharing violation took place in your access DB.

    as we all know access database is not as roboust as sql server,

    another user could have get in and use the Access database

    locking and preventing sql server to use it.

     

    To prevent this from happening make sure that no ther user is accesing the access mdb.

     

    regards,

    joey

     

     

     

    Thursday, December 07, 2006 2:30 PM
  • I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied.  It did appear to be fixed after applying SP1, however it has shown up again.

    Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

    Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied.  This sounds to me like a very bad sign for anyone who uses linked servers.

    Monday, December 11, 2006 7:02 AM
  • Does anyone know or have an idea what causes this.  I never had this problem when I used SQL Server 2000.
    Friday, December 15, 2006 9:45 PM
  •  Seradex wrote:
    Does anyone know or have an idea what causes this.  I never had this problem when I used SQL Server 2000.

    I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

     

    Wednesday, December 20, 2006 10:50 AM
  • My 2005 server has just done this for the first time!  I have found it happens about every other month between Sql 2000 servers.  Every sp that has "promised" to fix it has not.  I have also found that on the server you loose one, you loose them all.  I have multiple connections to different types of data sources and they all stop simultaneously. 

    The comment about record locking is along the right track I think.  I have found that OLE DB connectivity can fail at network level and can cause it.

    Thursday, December 21, 2006 11:25 AM
  • This is a bug using the Sql Management Studio.

    I've found a workaround, described at

    http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html

    at the end.
    Wednesday, December 27, 2006 2:29 PM
  • Thank you for the idea.  I will try this ASAP.

    Does MS have any plans to release a fix for this?  (I hope so, especially if it actually fixes the problem.)

    Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

    HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
    DisallowAdHocAccess = 0

    He suggests following these steps:

  • Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.
  • Close the Sql-Management-Studio, open RegEdit and search for the specific key. If you have more than one instance, use the right internal name (MSSQL.1 / MSSQL.2 etc.).
  • Change the value to 0
  • Restart your MS-SqlServer. Using Reconfigure doesn't help.
Tuesday, January 02, 2007 6:00 AM
  •  Seradex wrote:
    Does MS have any plans to release a fix for this?  (I hope so, especially if it actually fixes the problem.)

    Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

    HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
    DisallowAdHocAccess = 0

    Now I've found the 'bug' as explicit code.

    The master database contains a stored procedure called from the Sql-Server-Management-Studio

    [sys].[sp_MSset_oledb_prop]

    At the end, there is the code:

    -------------------------

    if 1 = @property_value

    begin

    declare @val int

    set @val = @property_value

    exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @regpath, @property_name, REG_DWORD, @val

    end

    else

    begin

    exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @regpath, @property_name

    end

    -------------------------

    @property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

    The procedure can be found using the following query:

    Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
    on a.id = b.id where b.text like '%DisallowAdHocAccess%'

     

    Wednesday, January 03, 2007 8:19 PM
  • I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server.  I tried the fix above but it still will not run.  I can run a query against the Linked server using MS SS Management Studio without problem.

    Anyone have any suggestions?

    BTW:  I have SP1 installed and have connection be made without a security context checked.

    Closing and re-opening VS2005 does not help.

    I copied the stored procedure source code to a window in MS SS Management Studio and executed it.  It ran ok there. 

    Tuesday, January 30, 2007 9:59 PM
  • I tried applying this fix as described and it does not seem to resolve the problem.

    The Linked Servers on our SQL 2005 Server still stop working after a short amount of time.  I applied the fix a while back, but was unable to test until recently.  I first thought I had forgotten to restart the SQL service, so I restarted the service.  After that it only worked for a short time like before.  Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

     

    This is not good.  Are any Microsoft techs looking at this issue, because it needs to be fixed soon? 
    It is not good that the SQL Server service needs to be restarted each time this problem occurs.

    Tuesday, March 13, 2007 7:16 PM
  • I met the same problem, at last I have it done:

    1: don't use mapped drive, use network path like: //machine/sharefolder/filename

    2: put machine name to the trust site in IE

    Thursday, March 22, 2007 7:16 PM
  • I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?
    Tuesday, April 17, 2007 4:21 PM
  • That doesn't really work for me because the Access Database is local to the SQL Server.

     

    Are they ever going to fix this problem?

    Thursday, May 10, 2007 2:51 PM
  • I figured out why I still have the problem.  I set up the WORKGROUP information file some time ago so we could attach to secured databases, but we ended up never using it.  Once I renamed system.mdw, I was able to access Linked servers properly with a simple refresh of the linked server list.

    I think that this is a problem for anyone who has the system.mdw file correctly configured and that microsoft should correct this problem.

     

    I will check this in a few weeks to ensure that it is still functioning and update this thread accordingly.

    Tuesday, July 03, 2007 5:26 PM
  •  

    I am sad to report that it has stopped working once again, and still there have been no updates to this problem from MS.  This is quite disappointing that something that had no problems in SQL 2000 once properly setup, does not work to any reasonable expectations in 2005.
    Wednesday, September 12, 2007 11:52 PM
  •  

    I see no one has responded to this thread in ages.  Do any Microsoft techs have any comment on this?  (Do they even read these?)

    Just so everyone knows, the SQL Server I am using is SQL Server 2005 SP2.

    Monday, December 10, 2007 5:01 PM
  • Just wanted to say thanks for this thread. Good info, but nothing helped me out either. And restarting the server is not an option for me. I'm also wondering if anyone found anything. I am using SQL Server 2000, Win2003 Service Pack 2. It worked fine for a long time, but since last week have been getting the following when attempting to access a linked server to an access database (local to the server).

     

    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 
    [OLE/DB provider returned message: Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

     

    any more ideas would be great!!

    Wednesday, January 09, 2008 3:38 PM
  • Hello,
     
    We have the same problem but with the MS OLE DB Provider for ORACLE:
     
    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the error.].
     
    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
     
    The linked servers are working one minute... then they stop working.
    Connectivity to the Oracle Database is assured (tnsping, sqlplus everything works).

    A restart to the MSSQLSERVER service is needed in order for the linked servers start working again.

    As far as we can tell, this started to happen when the DeLL System Management software automatically updated.
    We've uninstalled the DSM software, and scheduled an sql server job to use a linked server every 10 minutes, reporting failure.

    Since then it only happened once for about an hour, we're not sure if it was a network failure (it was Friday night Wink)...
     
    We still don’t consider this problem solved.
    Regards

    Hugo

    • Proposed as answer by Medo Saleh Tuesday, November 23, 2010 7:12 AM
    Wednesday, January 30, 2008 12:28 PM
  • Hello everybody , I faced the same problem when I tried to query an access file, which is a linked server.

    I tried lot of things, but it didn't work.

    finally I tried the "Compact and Repair Database", which is an option available in Microsoft Access to fix problem that may exist in access databases.

    I also noticed that this problem happens in access databases, especially if the database is huge (of course the access database).

    And guess what ?, it did work, try it. It's very easy.

    1. Open Microsoft Access (Without choosing any file to open).
    2. From the tools menu, open "Database Utilities", and then "Compact and Repair Database".
    3. Choose your access database file you want to repair and click "Compact".
    4. In the "Compact Database Into", provide a name for the compacted database, and click "Save".

    Use the compact version of the file.

    I hope that will solve your problem.

    good luck

    Tuesday, November 23, 2010 7:36 AM
  • I still do not consider this problem as solved either.  It is funny that I never had this problem with SQL Server 2000, and have had it continuously with SQL 2005, 2008, and 2008R2.  Note that other types of linked servers eg SQL continue to work even though all of the Access linked servers are down.  Also, none of the Access databases I were using for linked servers were corrupt.  They were actually very small database with only a small handful attached tables and a few real tables with no data in them which was used as an intermediary between SQL and another datasource.  I was not actually accessing the attached tables, but instead was transferring data from the attached tables into the real tables and then accessing the real tables from SQL Server.
    Note that when I was testing the problem, I was leaving the data in the real tables and only tried to access them from SQL Server.

    This is why I moved from using SQL linked servers to Access linked tables and pushing the data instead of pulling it.  It is also true that when one access linked server stops working, they all do.

    I really wish they would fix this problem.

    Seradex.

    Sunday, January 02, 2011 5:51 AM