Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

On our production server, I linked to an unsecured Access 2003 mdb file which worked nicely for a few days.

EXEC sp_addlinkedserver 'NUWDB', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'f:\sqldata\nuwdb\nuwbe.mdb'
EXEC sp_addlinkedsrvlogin 'NUWDB', FALSE, NULL, 'Admin', ''

Nothing has been touched on the production server but last night I started getting an error when attempting any jet data access. The error occurs not just for the mdb file in question but any jet links including xls files and brand new mdb files. This error has frightenly persisted a reboot and service restart. The error is;

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "nuwdb" 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 "nuwdb".

Service restart didn't fix it, reboot didn't fix it and recreating the link didn't fix it. The link to the mdb file works on our development server without issue. There is nothing in the SQL error log or windows event logs.

This isn't the first time it has happened and we managed to work around it without using linked-servers in the past but this time working around it isn't an option.

Hours of googling suggested setting "memory to reserve" so I add -g512 to the SQL service startup parameters, restarted and bingo, she works again. However, it is still working after removing this option. I'm thinking -g512 was unrelated and I've got an intermittent problem that's out of my control.

Does any know how to read dbcc memorystatus so I can confirm or deny a memory problem? Does anyone have any other clues about what is going on?

TIA Luke

share|improve this question
    
The error sounds like a problem accessing the MDB file via the file system. I'd check permissions and whether or not other processes are perhaps interacting with the same file. Could be there's a locking issue (try deleting the LDB file, for instance). –  David W. Fenton May 18 '11 at 20:46
    
It isn't permissions because it works then stops. I hadn't considered a locking issue - the system links to a copy of the database so the SQL server should have exclusive access but I'll look into it. –  Luke Rohde May 22 '11 at 21:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.