Hi

Running SQL Express 2008, creating a linked server to a Access 97 DB.

Execute the following SQL:

WHILE 1 = 1
	BEGIN
		IF EXISTS (SELECT 1 FROM [master].[dbo].[sysservers] WHERE srvname = 'MyLinkedServer')
				BEGIN
					EXECUTE "sp_dropserver" MyLinkedServer, "droplogins"
				END

		BEGIN	
			EXECUTE sp_addlinkedserver 'MyLinkedServer', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\Test.mdb'		
			
			SELECT
				MyField
			FROM
				MyLinkedServer...MyTable
			WHERE 
				MyField = 'XXX'
		END
	END

Runs perfect n times.  And then, every nth time it will error with:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServer" returned message "Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServer".

I'm actually trying to debug this issue for a .net program which throws the same error except it actually stops SQL Express creating a linked server until the service is restarted or the PC is rebooted.  I've taken .net out of the equation to try and nail issue and what is causing it.

If I then execute the above SQL again after the error it works perfectly, until the next time.

Anyone have any ideas what is going on here?

What happens if you replace the Access file with a piece of junk?

That is, does it give the file name in the error message or a blank string?

If that gives the file name, this indicates that when things go south something loses the grip of the file name. I would suspect that it is the OLE DB provider.

What happens if you run this loop, without dropping and recreating the linked server?

Have you checked to see if is a time out.  Run SP_configure in SSMS and find out what your remote query timeout value is.  The default is 600, which is in seconds.  If this appears random it could be influenced by network transfer rates, connection issues and other issues.  You can set the remote query timeout value to 0 which is unlimited to test the theory.

sp_configure 'remote query timeout',0

go

reconfigure with override

go

Thanks for the replies.

If I replace the MDB with a junk file I get, so I do get a name.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServer" returned message "Unrecognized database format 'C:\msdn2.txt'.".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServer".

If I run it without dropping and recreating it works.  Which is what I might do in the .net program, only create it if it doesn't already exist.  And if it bombs creating I'll have the program restart the service.  But saying that I can't make it crash today with the SQL in my original post.  

Timeout was set at 600.  This runs instantly every time in the loop so I don't think it's timing out.  Changing to 0 did not fix it.

If it is the OLE DB Provider is there anything I can do?  I have a workaround already for this.  But it's incredibly annoying and I just can't let it go!  I'd like to know why.

What is the output of "SELECT @@version"?

It is difficult to make a guess of where things to wrong. The effect of dropping and recreating the server is that the query plan is flushed and recreated. Maybe something goes wrong here? One way to test this would be to add the hint OPTION(RECOMPILE) at the end of the query.

Hi

@@Version = Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)   Apr 22 2011 11:57:00   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) 

Last week the error was being thrown regularly on my PC, it's still happening on our field laptops.  But now on my PC I just can't make it happen again, so it's hard to know if OPTION(RECOMPILE) will make a difference.

It'd be helpful if the error message wasn't so generic.

I think I'm just going to have to accept that this will happen sometimes and restart the service when it does.

There is also reason to install Service Pack 3 for SQL 2008 R2. You have the RTM version (with some minor update) which is grossly outdated. I'm not saying that the problem is due to a bug that has been fixed, but if you would decide to opan a case with Microsoft, you first need to have a supported version, and currently you don't.