| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-04-02 : 13:52:33
|
| I'm building a conversion query that i have to test frequently. Each time i have to test it, because it builds db's and tables, i have to use the activity monitor to kill any processes which are still accessing the db's the last time they were created. This is only run on my local machine so there is no chance for me to foul anyone else's work. However, it would be great if i could script that kill into the beginning of my query so i don't have to go through the extra steps of running the activity monitor and killing each process. Is there any code which exists that i can include in my query to take care of that upon execution?thanksjames |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-02 : 13:54:21
|
| why should you create db and tables each time? isnt it matter of truncating and repopulating table each time if you want it to be cleared off all previous data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-02 : 14:09:19
|
| Only in testing the conversion script is the script recreating the db. I need the testing of the script to be able to start with a clean slate so i know it's executing properly from beginning to end. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-02 : 14:10:22
|
| i will need the script to create everything from the ground up on the client's server when it comes time to migrate the data. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-02 : 15:46:46
|
quote:
Originally posted by WJHamel
Only in testing the conversion script is the script recreating the db. I need the testing of the script to be able to start with a clean slate so i know it's executing properly from beginning to end.
i dont thing dropping and creating a db itself is good idea for that.what is changing with regard tp db each time other than the table data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-03 : 14:37:43
|
| As i continue working on this conversion, there are changes to table structures that happen and it is quicker at this stage of the process to script the drop db at the beginning of the query rather than dropping or deleting it "manually" each time i make a change. Before i can do that, i have to kill any processes via the activity monitor that are accessing that db or the drop db will not fire off. I've already discovered that the KILL command can be used in a query, but for it to work, i need to know the SPID for the process. Since that changes each time that process is started, it will be of no use to me. Hence, i think i'm stuck going the long way round with this for now.no worries.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-04 : 18:24:28
|
| for getting spids of active processes you can usesp_who2 'active'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-04 : 18:32:04
|
| You can also run this:ALTER DATABASE myDB SET READ_ONLY WITH ROLLBACK IMMEDIATE;DROP DATABASE myDB;That will forcibly disconnect any active sessions from that database, which is easier than killing SPIDs one by one. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-05 : 11:04:21
|
| Cool. Thanks rob and visakh. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 11:45:55
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
line in query to invoke "Kill Process" similar to