MS Jet databse engine cannot open the file.... 1

[aker]

Hi.
{
SQL 2000 running on Windows 2000 Server SP2 Jet 4.0;
Windows NT 4.0 Domain/File Server located the Clipper 5.2 dBases and program;
Clients running Windows 98 with mapped drive to NT4.
}

This was originally posted in the SQL forum but no-one responded. Maybe they were offended by the dBase aspect

I have encountered a small problem. I created a Data Transformation Services using the SQL Wizard to Import Clipper database files to SQL. Normally this would work when the dbase files were in use but not today. Nobody has exclusive rights to the files - opened in shared mode.

To check, I created a new DTS with the characteristics:
Source: Dbase III;
Destination: Microsoft OLE DB Provider for SQL Server.
In 'Select Source Tables...' I select a file, then Transform. Immediately appears the message:

Error source: Microsoft JET Database Engine
Error Description: The Microsoft Jet database engine cannot open the file <my file>. It is already opened exclusively by another user, or you need permission to view its data.
Context: Error calling OpenRowset on the provider


Things I have tried:

Security and Sharing permissions of databases - Full;
Permissions of SQL database - Full;
TEMP to %SYSTEMROOT%\TEMP
TMP to %SYSTEMROOT%\TMP

Any other suggestions much appreciated. Temporarily I create a copy of files and import them from there no problem.

Other info: Upgraded some programs from 5.01a to 5.2 and installed SP2 on SQL2000 recently.

Regards.

 

[GriffMG]

I think this may be my hobby-horse!

Opportunistic file locking in NT

Long explanation too boring for words, but basically by default NT lies to you. It says it is opening files in shared mode, but if it thinks it look a bit faster in the
file servering role - it hands them over as EXCLUSIVE.

It is supposed to have a 'break-locks' function which is intended to ask workstations to give back files opened with the op lock and reopen them, but it doesn't seem very reliable.

How do you fix it?

You need to merge a couple of files into registries on the server and worktations.

Server:

REGEDIT4

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters]
"EnableOplockForceClose"=dword:00000001
"EnableOplocks"=dword:00000000

Workstations:

REGEDIT4

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkStation\Parameters]
"EnableOplockForceClose"=dword:00000001
"EnableOplocks"=dword:00000000
"UseOpportunisticLocking"=dword:00000000

Hope this helps!
Regards

Griff
Keep [Smile]ing

 

[aker]

GriffMG.
Thanks for the speedy reply. I applied your alterations yesterday but had to wait until today to see if they would work (server restart during the night).
They didn't.
Once upon a time I played around with Opportunistic locking and none of the clients are using it. No, I'm pretty sure the problem lies with the Windows 2000 Server and/or SQL 2000. As I said, it was working a while ago, but since then I have made so many changes to the system that I have no idea what the problem is. To test, I installed another version of SQL 2000 on Windows XP Professional and as expected, the DTS worked like a dream.

Before you start, yes, I did alter the HKEY_LOCAL_MACHINES\System\CurrentControlSet\Services\MRXSmb\Parameters on the 2000 Server too.

I'll try reinstalling SQL 2000 first, and failing that, go the whole hog and reinstall 2000 Server.

Regards.