Acu4GL for MSSQL Spurious Lock Errors

0 Likes

Problem:

There are sometimes lock errors reported by the Acu4GL interface when no other users are in the application.  Those errors include file status 99 and 9D,512.

Resolution:

Spurious lock errors are likely if there have been ungraceful program exits which can strand entries in the lock tables.

To address this Acu4GL provides a way to execute stored procedures immediately upon connecting to the SQL Server.  That is by creating a stored procedure named “sp_AcuInit” within the "master" database (or the database where the stored procedures were installed).  Whatever is in that stored procedure is executed automatically upon connection.

Acu4GL can be set to clean up the lock tables on each connection by executing the following SQL within the Microsoft SQL Query Analyzer: 

(Note that you can use either master or the database the application runs against.)

 

use master

go

create procedure sp_AcuInit

as execute lockdb..sp_AcuRemoveUnusedLocks_1 

go 

grant execute on sp_AcuInit TO public

go

print 'sp_AcuInit installed'

go

 

IMPORTANT:  While most of the query is not case sensitive, the stored procedure names ARE case sensitive!  Using copy/paste is highly recommended.

In this example lockdb is the name of the lock database.

The entry “sp_AcuRemoveUnusedLocks_1” mentioned in the first step is an existing Acu4GL stored procedure that removes records in “AcuLocks” and “AcuOpenTables” based on the ProcessID.

If ALL of the stored procedures are installed in master database (master was specified as the lock database)  then you don't need to qualify the procedure to execute:

CREATE PROCEDURE sp_AcuInit

AS execute sp_AcuRemoveUnusedLocks_1 

GO  

Comment List
Anonymous
Related Discussions
Recommended