Lock in SQL when deleting table by DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Lock in SQL when deleting table by DTS

Hello everybody.
First of all, i am french so please forgive my english [:I]
Does anybody know how I can delete a SQL table by DTS if someone is locking the table.
I have to do that because we use DTS to replicate the database from AS400 in SQL. So we drop tables ,create them and fill them from the AS400 one by one with DTS.
But sometimes, some users stay connected to SQL and the DTS blocked on the table and stay block until the morning. Anybody got an idea ? Thanks for your help. JCB
You can use assorted methods to find out who is connected to a table and then kill the connection. Once its killed, you will be able to drop the table. You can either kill all non-system processes (even if they are not locking a specific table), or go a little bit further and only kill sessions locking tables you need.
Sp_who/sp_who2 will quickly return who is connected. Sp_lock will return who is locking what and how. You can check the results of either in dts and issue the appropriate kill statement. There are some user written sprocs out there on the web with detailed examples. I might be able to find a couple if I search through my links. chris
Refer to this LINKhttp://www.sql-server-performance.com/q&a37.asp for relevance of script to kill the users connected/idle. And other than this I think there is no issue on scheduling the DTS package for database update. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Perfect, just what I need [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />].<br /><br />Thanks you very much for your help.<br /><br />JCB
]]>