Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raky
Aged Yak Warrior
767 Posts |
 Posted - 2010-01-21 : 08:17:08
|
| I have three main tables in my database. These 3 tables are get truncated and will be loaded with data from another database through an sql job which runs everyday( At same time on every day ). While this job is executing no one should execute DML operations like insert\Delete\Update on these three tables until this job completes successfully. How can i deny the permissions to these tables during the time when this job is running. I need to Give a message in my application like 'Please wait for some time as updates are going. Wait for some time and try again'.How to do this?Note: Updates from Application side on these 3 tables should not be done while this job is executing but the job will perform dml operations on these 3 tables. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-21 : 08:26:25
|
| You could set a flag in the database at the start of the "data refresh" job, and clear it at the end, and then have your application check that flag. If you "data refresh" job fails the database will be left with that flag set until someone fixes it manually (probably no bad thing if only half the data was imported)The "data refresh" job could change the database to single-user mode (and force the change, terminating open connections), which would prevent access, but your application may throw an error, rather than be able to catch that state.You can have the "data refresh" job put the data into new tables, and then (atomically, and "instantly") drop the old tables and rename the temporary import tables. This would mean that users would see the old data up until the instant that the new data was made available. Needs twice the disk space for the MDF file though. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-21 : 08:30:12
|
| You could look at : DENY SELECT ON myTable TO user .Also apply to the other DML operationsJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
|
|
|
Deny access to a table for a period of time