February 20, 2009 at 9:51 am
I just wanted to start off with expressing my gratitude for such a great web site. I have always been able to find the answer I was looking for on this site. Unfortunately that day has come to an end. 🙁
I have recently upgraded one of my SQL servers from SQL Server 2000 to SQL Server 2008. On this server I have several linked servers. Most of them are links to other SQL Server 2000 servers, but I also have a couple of linked servers to Access Databases, a couple of linked servers to Excel, and one linked server to a C-Tree Plus database. I was able to create all the linked servers without a problem, and they all functioned correctly...so I thought.
Fast forward six weeks. A couple of times a week all the linked servers using the jet driver stop working. To get them to work again I have to stop and restart SQL Server Service. After restarting the service all linked servers are now functioning again, at least for a few days.
I have searched and found similar problems, but no one had a fix.
Troy
February 20, 2009 at 10:28 am
When you said "stopped working" What error do you get ?
* Noel
February 20, 2009 at 11:37 am
Here is the error message:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "INY10014" 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 "INY10014". (Microsoft SQL Server, Error: 7399)
This is the same error message you would receive if the excel worksheet was currently in use, except when this happens it isn't in use. Every linked server that uses JET has this error at the same time. Before the upgrade when this server was still SQL Server 2000, the linked servers were set up this way for several years without this occurring.
February 20, 2009 at 2:19 pm
Is the OS the same version ?
* Noel
February 20, 2009 at 3:07 pm
The Sql server 2000 was running on MS Server 2000, and now SQL Server 2008 is running on MS Enterprise Server 2008.
February 20, 2009 at 3:11 pm
tpratte (2/20/2009)
The Sql server 2000 was running on MS Server 2000, and now SQL Server 2008 is running on MS Enterprise Server 2008.
is it: Windows 2008 x64 Enterprise Edition ?
* Noel
February 20, 2009 at 3:52 pm
32 Bit OS
Quad core processor
8GB RAM with AWE enabled
February 20, 2009 at 4:07 pm
Can you Try? http://support.microsoft.com/kb/814398
If some temporary access is needed you could be receiving this error because of insufficient permissions to a temporary folder.
I am sure you are having a configuration issue; it is just hard to figure out which 😉
* Noel
February 20, 2009 at 4:13 pm
When I first set up the new server, I had run through those steps to get the linked servers working. I agree that it has to be something in the configuration. But what I don't understand is how a restart of the sql server service fixes it temporarily.
February 20, 2009 at 7:45 pm
This problem is related to the new data access in Windows 2008 and Vista in regular development you could just manually reference MDAC and your code will run. I found you have to create DSN and reference the ODBC driver in Windows 2008 to resolve this problem.
The reason is the OLE DB is for SQL Server and other RDBMS, the other option is to use Access 2007 and use the new ACE driver.
Kind regards,
Gift Peddie
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply