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 |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-07-12 : 22:19:26
|
| All Experts,Please let me know if there is a work around for this and let me know how i can proceed with this....I have 3 tables....A, B, C. All 3 have same schema.I have a view ABC which pull the data form all 3 tablesselect * from A with (nolock)UNION ALL select * form B with (nolock)UNION ALL select * from C with (NOLOCK).So on 1st of every month...i will be renaming C to D, B to C, A to B and create new table A with the same schema and drop D. The view ABC is gonna be the same. So when i am doing this...there will be select statements on VIEW because of which i am unable to rename the tables(need to wait until the select statements are finished.) So before i start renaming the tables, is there a way that i can place a lock on the view so that no statements can be run on it and i can rename the tables.Thanks in Advance. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-12 : 22:30:10
|
| I'll drop the view at the start of your rename routine and recreate it at the end. |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-07-12 : 22:39:58
|
| Thanks!! i will try doing that or renaming the view before renaming the tables.Also, ours is an OLTP environment, so the inserts in table A will be a record for milli second. How can i rename table A and create new one with the same name in this situation? Can we hold some lock on table A before renaming it? |
|
|
|
|
|
|
Obtain a lock on a table and view