Can I Prevent Table Read During an Update

[ChuckGann]

Hi Everyone,

We are using SQL 2000 and DTS to populate some tables with summary data from other SQL tables. As part of this, we delete records in the destination table and then re-populate from the other tables. Our problem is that users can run reports on this summarized data at any time, and if they order a report during the update it may return invalid data.

Is there a way to suspend reporting access to the destination table until the processes are complete?

Thanks.

 

[SQLSister]

Well first I would schedule these type of processes in the middle of the night when users aren't running reports.

If they must run during the day, I would create a process in your user interface that checks to see if the job is running and willnot send a select while it is running. Offhand I would probably set a flag in a table to running or not running as the first step of the job and set it again as the last step. Then the user interface simply checks the value and then proceeds or doesn;t proceed.

"NOTHING is more important in a database than integrity." ESquared

 

[genomon]

You could also try using the locking hint WITH(rowlock) on your update SQL.

"No matter what happens, somebody will find a way to take it too seriously."

 

[SQLSister]

actaully I think the locking hint would be TABLOCKX because the whole table would be locked, but that would time out your users so I think the approach of telling them they can't access the data right now after the user interface determined the job was running might be a better approach from the user perspective rather than the transaction just running until it times out. Look up locking in BOL if you really need to see the options available.

"NOTHING is more important in a database than integrity." ESquared