Professional Web Applications Themes

OSQL and kill all connections - Microsoft SQL / MS SQL Server

How can I disconnect and kill all connections from osql ? Thanks Pat...

  1. #1

    Default OSQL and kill all connections

    How can I disconnect and kill all connections from osql ?

    Thanks
    Pat


    Patrick Guest

  2. #2

    Default Re: OSQL and kill all connections

    --This will kill per given database name.
    --create this sp in the database that you want to take offline and you can
    execute the stored procedure from isql or osql utility.


    CREATE PROCEDURE usp_KillUsers dbname varchar(50) as

    SET NOCOUNT ON

    DECLARE strSQL varchar(255)
    PRINT 'Killing Users'
    PRINT '-----------------'

    CREATE table #tmpUsers(
    spid int,
    eid int,
    status varchar(30),
    loginname varchar(50),
    hostname varchar(50),
    blk int,
    dbname varchar(50),
    cmd varchar(30))

    INSERT INTO #tmpUsers EXEC SP_WHO


    DECLARE LoginCursor CURSOR
    READ_ONLY
    FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = dbname

    DECLARE spid varchar(10)
    DECLARE dbname2 varchar(40)
    OPEN LoginCursor

    FETCH NEXT FROM LoginCursor INTO spid, dbname2
    WHILE (fetch_status <> -1)
    BEGIN
    IF (fetch_status <> -2)
    BEGIN
    PRINT 'Killing ' + spid
    SET strSQL = 'KILL ' + spid
    EXEC (strSQL)
    END
    FETCH NEXT FROM LoginCursor INTO spid, dbname2
    END

    CLOSE LoginCursor
    DEALLOCATE LoginCursor

    DROP table #tmpUsers
    go



    "Patrick" <all-in-1.com> wrote in message
    news:phx.gbl... 


    Ammar Guest

  3. #3

    Default Re: OSQL and kill all connections

    Hi Patrick,

    Same as you can do it from Query yzer:
    ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    This works on SQL Server 2000 only, but for SQL Server 7.0 you can use a
    script
    like
    DECLARE sql varchar(4000)
    WHILE 1=1
    BEGIN
    SET sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10)
    FROM sysprocesses WHERE dbname = <database name>
    AND spid >= 50)
    IF sql IS NULL BREAK
    EXEC (sql)
    END

    "Patrick" <all-in-1.com> wrote in message
    news:phx.gbl... 


    Jacco Guest

Similar Threads

  1. Replies: 0
    Last Post: August 8th, 09:46 AM
  2. OSQL - Rows affected
    By Aaron in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 30th, 02:11 AM
  3. OSQL
    By RG in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 06:51 PM
  4. parameter passing from osql
    By Arijit Mahapatra in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 08:50 AM

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