Problem with server link, database fails with error 7399

[arturner]

Hi All,

We have a problem that has been annoying the hell out of us for the last 3 day, I have searched these forums and scoured my books for help, I have found many helpful resources, which have not fixed the problem.

Under the following conditions we have problems

User on computer A (Windows 2000 Pro) is running SQL enterprise manager/query analyzer, managing a SQL server on a Windows 2000 Server - computer B

The user creates a Linked Server, specifying a Microsoft Access database on an NTFS share on computer C (which is the same machine as computer A)

The user is logged into computer A using the same local windows account that the SQL Server is running under on computer B.

Computer B is registered in the computer A Enterprise Manager or Query Analyzer using Windows Authentication

The NTFS share on computer C is configured to give full control to that same windows account

We get the following error in enterprise manager when we try to open tables on the linked database:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\DPC002\Scada\Scada.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I made sure that the passwords on the machines' windows accounts are matching, and have also configured the environment variables on computer B to give everyone full access to the c:\winnt\temp folder as suggested by Microsoft.

If the user moves to the SQL Server computer (computer B) and attempts the same operation, the user is able to view the tables without an error. If the user copies the remote database to the same computer as SQL Server (computer B) and attempts the same operation from computer A, the user is able to view the tables without an error.

We run a Novell network, and only use Windows servers in a workgroup, and not domains, do you think that this is the problem?

Thankyou very much in advance.

Andrew Turner

 

[SQLRickster]

It could be that SQL EM or agent is holding a lock on the access mdb. The error is being generated by JET or Access. This could mean that SQL or a SQL agent is holding a lock on the database. Try this: When you get the above error. Explore the folder that contains the Access mdb. If there is a file with the same name as the access mdb, but has .ldf the database is being locked by something and my guess is SQL. Jet does not allow multiple instances of a table to be opened by more than one source (table Locking). Access does not support row level locking like SQL and Oracle.

Hope this helps!
SQLRickster.