Professional Web Applications Themes

Kill process - Microsoft SQL / MS SQL Server

Hello everyone. I'm trying to kill process on selected SPIDs only. Select works, killing doesn't. It complains on syntax near 'kill'. Please advice on how to make it work. Appreciate it! Here's the code: -------------------------- declare sp_id int declare dbname varchar(50) DECLARE SPID_DBNAME CURSOR FOR select spid, dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end from master.dbo.sysprocesses where spid >= 0 and spid <= 32767 /*Open Cursor*/ OPEN SPID_DBNAME /* Perform the first fetch and store the values in variables.*/ FETCH NEXT FROM SPID_DBNAME INTO sp_id, dbname /*Check FETCH_STATUS to see if ...

  1. #1

    Default Kill process

    Hello everyone.

    I'm trying to kill process on selected SPIDs only. Select
    works, killing doesn't. It complains on syntax
    near 'kill'. Please advice on how to make it work.

    Appreciate it!

    Here's the code:
    --------------------------
    declare sp_id int
    declare dbname varchar(50)

    DECLARE SPID_DBNAME CURSOR FOR

    select spid,
    dbname = case
    when dbid = 0 then null
    when dbid <> 0 then db_name(dbid)
    end
    from master.dbo.sysprocesses
    where spid >= 0 and spid <= 32767

    /*Open Cursor*/
    OPEN SPID_DBNAME
    /* Perform the first fetch and store the values in
    variables.*/

    FETCH NEXT FROM SPID_DBNAME
    INTO sp_id, dbname

    /*Check FETCH_STATUS to see if there are any more rows
    to fetch.*/
    WHILE FETCH_STATUS = 0
    BEGIN
    if (dbname = 'RoomTrackerMigration')
    begin
    kill sp_id
    end

    /*This is executed as long as the previous fetch
    succeeds.*/
    FETCH NEXT FROM SPID_DBNAME
    INTO sp_id,dbname
    END
    CLOSE SPID_DBNAME
    DEALLOCATE SPID_DBNAME

    Oleg Guest

  2. #2

    Default Re: Kill process

    You have to use Dynamic SQL which using KILL with a variable sp_id. Thus you
    have to do:

    EXEC('KILL ' + spid)

    Another option which is generally suggested in this newsgroup for killing
    all the connections is :

    ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    -- After killing all you can set it back to multi-user.
    ALTER DATABASE db SET MULTI_USER

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Kill process

    Thanks a lot!
    It works now. Just one little thing. You should add a ' ' in between.
    Like this:

    EXEC('kill' + ' ' + sp_id)

    Thanks again!

    O.B.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    O.B. Guest

  4. #4

    Default Re: Kill process

    Actually, Anith had his space in the first string between the final el and
    the quote mark. 'Kill ' not 'Kill'.

    Screen resolution can be a killer sometimes. :)

    Russell Fields
    "O.B." <NP> wrote in message news:%2342OZ7MQDHA.2636TK2MSFTNGP10.phx.gbl...
    > Thanks a lot!
    > It works now. Just one little thing. You should add a ' ' in between.
    > Like this:
    >
    > EXEC('kill' + ' ' + sp_id)
    >
    > Thanks again!
    >
    > O.B.
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Russell Fields Guest

  5. #5

    Default Re: Kill process

    My bad. :o)
    Thanks again!

    O.B.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    O.B. Guest

Similar Threads

  1. Replies: 0
    Last Post: October 18th, 09:18 PM
  2. How? Granting elevated privileges to a web service / kill a process
    By Lee Gillie in forum ASP.NET Web Services
    Replies: 1
    Last Post: May 13th, 12:48 PM
  3. Win32::Process Kill Process in Windows ME
    By Stefan Mueller in forum PERL Modules
    Replies: 3
    Last Post: April 15th, 07:35 PM
  4. Replies: 1
    Last Post: February 3rd, 09:30 AM
  5. can't kill child process - program hangs up
    By Shawn Corey in forum PERL Miscellaneous
    Replies: 0
    Last Post: July 26th, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139