How To update a Work Table across many user

This article explains how to share a work table across multiusers. It also address locking strategies.

If for example you have to use a reporting Software, like Crystal reports, sometimes you will need to have a Working table to display results. Crystal will not read data definition from a temporary table. But how do you share this table with multiple users without problems?

My solution was to add a reportid field. Each user running the report will add his own unique Reportid field and delete all his records once the report is produced.
All this can be accomplish within a stored procedure linked to the report.

There is also another issue that arise when updating data.
If you use SQL default locking mechanism you can read data that is being changed.

If in your table you add a timestamp you can retrieve that column and make sure the data your are reading is up to date. All you do is compare the timestamp you inserted with the one you are reading. If they are the same then the data is accurate.


If they are not the data you are reading has been updated.

 

Share this article!

Follow us!

Find more helpful articles: