Very Computer

We would like to automatically block or kill other processes using a set
of databases. We want to do this at very infrequent times where the
entire data in these databases are going to be converted.

Unfortunately the connected workstations are widespread and often, the
users do not end the connected applications. So we want to be able to
kill their process in SQL server.

We have tried creating a process which uses the KILL command in T-SQL,
but it will not permit a variable to be used. IE "KILL 10" works but

For operational reasons, we cannot have a manual process (IE The SQL
Enterprise Manager) kill the processes manually. This procedure needs to
be automated.

Does anywone have an Idea of how we can termminate database connections,
or how we can use the KILL command with a variable?

Thank you.

Louis

Create this sp in the master and run it when you want to 'exit' people...

CREATE PROC usp_KillUser

AS
SET NOCOUNT ON
/* Check to make sure DB is valid ???*/

DECLARE c_Users cursor for
 SELECT spid
   FROM master..sysprocesses (NOLOCK)

OPEN c_Users

BEGIN

  BEGIN


  END

END -- WHILE

I talked with one of the folks from UPS about this at the "Ask the Experts"
night at Tech-Ed last week.  The short answer is to set the DBO ONLY option in
your database, then use a cursor to kill all of the offending users.  The
syntax would look something like:

EXECUTE sp_dboption 'myDb', 'DBO', true
GO

DECLARE zKill CURSOR FOR SELECT
   'KILL ' + Convert(CHAR(4), spid)
   FROM master.dbo.sysprocesses p (NOLOCK)
   WHERE p.dbid = Db_Id('myDb')

OPEN zKill

   BEGIN


   END

CLOSE zKill
DEALLOCATE zKill

Give this a shot if you haven't found a working answer.

-PatP

====================
I detest SPAM, and boycott the SPAMmers.  Send me ads for your competitors!

Please respond via the newsgroups rather than via email.  If you MUST respond
via email, remove ALL of the digits from my email address