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 |
|
sdhusodo
Starting Member
4 Posts |
 Posted - 2011-11-18 : 19:29:58
|
| I have a simple data-warehouse to where I transfer some tables from production database. For large tables such as "Sales Invoice Detail", I transfer daily transactions incrementally, while for the smaller tables for example "item" table (170,000 rows), I truncate the table on the data-warehouse and insert the whole rows. One of the views I have, joins "sales invoice detail" with "item".Now, I can't truncate "item" table since the view has a reference to it. Using "delete" will take a long time to finish. I could add a script to drop the view and recreate it after finish transferring, but it will remove all permissions to the view.Is there a way that I could disable and enable a view? or is there any better way to deal with this truncate/transfer/view process?Thank you for any help. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-18 : 22:32:48
|
| Don't think a view can be disabled. But drop/create is pretty easy. Script out the permissions you want on it and add that to the script too.If you're real lucky and everyone with a login is allowed to see it, can just add GRANT SELECT On MyView TO PUBLIC; |
 |
|
|
sdhusodo
Starting Member
4 Posts |
Posted - 2011-11-20 : 18:27:24
|
| Thanks Russell. For this view, everyone has the permission to select.The main concern is that we have more than one view using item table, which means I have to drop/create all views related to item table. Am I right? |
|
|
|
johntech
Yak Posting Veteran
51 Posts |
Posted - 2011-11-21 : 08:06:42
|
| Answer Wiki:Pretty much the only way to do this would be to create a scheduled job which deletes the view, and then deletes itself after running.However randomly deleting objects from someone elses server is grounds for never doing work with them again.I can guarantee that if you ever tried that on one of my SQL Server word would spread very quickly that you had done so, and you'd end up with a hard time trying to find work.http://itknowledgeexchange.techtarget.com/itanswers/sql-script-to-disable-views-after-a-demo-period/ |
|
|
|
|
|
|
|
|
Disable and Enable Indexed View