Error Message when SQL Lock

BHT

Hi All,
Is there anyway to prompt a user with an error message when try to lock table(s) already lock by another user in Navision Application?
I'm using SQL 2000 & Navision 3.70.

Thanks.

BHT.

kine

No standard way.

BHT

Hi Kine,
can I have a clue to generate a code for this ?
Tks.

kine

You cannot do it through C/AL code... there is problem, because SQL is locking records or pages, not whole table. And if you want test if you will be locked by another user, you cant without trying it, because system do not know, which records will you need to lock...

BHT

Hhhh....
Then no way to me for alert user when trying post a transaction.
My client have a very high frequency of posting activity.
As the result they always press Ctrl+Alt+Del when see the screen not responding.
Anyone have idea then, how to tell user to wait until the previous posting routine done.
When using native database, there's no problem to end user. They alert with error message. Now using SQL everyone problem....

Please anyone help me...

BHT.

kine

Partial solution is to optimize the application and the HW of the server - with target to do the posting as fast as possible. For example disabling not needed keys, optimize keys for usage with MS SQL, disable flowfield levels, change PK (it is not easy) etc. see Performance troubleshooting guide...

Urmas

You can also query the SQL sp_locks virtual table (using for example Automation what comes with MDAC), but it may be not the best solution as the locks table gets huge while Navision does the transactions. Anyway you may try it (and let us also know afterwards )

BHT

Thanks Kine,
All references from MBS already tried. No use. The size of database is 180GB currently (only 7 month) and increase rapidly.
1 Shipment posting need 5 minutes to complete. The spend time growing from day to day.
Previously need 15 minutes to complete. after changing the hardware become 5 minutes.

I'll explore what Urmas suggest. Hope solve the problem.

Thanks.

Urmas

5 minutes for 180 GB database seems a bit too long. I have here a database of a size og 100 GB and the posting of Shipment+Invoice (we use immideate posting to all legers) takes about 10-20 seconds.
Maybe you should test your SQL configuration?
Also - I havent checked the code for 3.7 posting routine, but in earlier versions there was an Navision bug in posting - Navision tried to update some tables before it started updating G/L and stumbled upon a lock creatd by other users. This created a huge amount of lock records in SQL server, what again brought the server almost into standstill.
Locks use a lot of SQL server memory, so you may want to investigate the case, if the locks table contains a lot of lines.

PaddyMullaney

Hi

There also some issues with the Posting Routines, the keys they use and the order they set filters or ranges.You need to get them so they filter by most unique value first to take account of the fact that SQl works quicker this way, but this should be approached with caution.

The performance trouble shooting guide can only take you so far, we have a 30GB database, and have looked at a lot of the guide, but do have issues, especially around posting, and the next stage will be to consider these sorts of issues.

kine

For our customer was best speedup adding 2GB RAM (had only 2GB) - do not forget, that there are some triggers on the tables in MS SQL server which need to be compiled. Do not forget to all swtiches you must set to use more than 2GB RAM... do not forget to limits of combination of OS and MS SQL edition (See MS SQL books on-line)

Urmas

One more thing
I just remembered that I have read somewhere about a possibility of mapping existing SQL tables into Navision ones somehow (so they can be used in CAL code). As the sp_lock will result in recordset, it may be smart to map it into CAL table, so you do not need to initialize the Automation every time you make a posting.

PaddyMullaney

SQL Locks. If you go to database information and sessions, the Blk By information is displayed so it maybe that the lock information is displayed on one of the Navision virtual tables.

kine

Yes, if you can make such a view in MS SQL, you can Link NA table to such a table... see Linked table somewhere on this forum...