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.
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
|||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
|||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
|||
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.
|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||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".
|||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.
|||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.|||
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.|||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%'
|||
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.
|||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.
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
|||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 ?
No comments:
Post a Comment