Results 1 to 9 of 9

Thread: Number of Connection ?

  1. #1

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    Start the SQL Server Enterprise Manager. Expand your server, and look at Management--> Current activity --> Process Info. All current connections are listed. If you right click a connection, you can select Kill process to kill the process. Of course you need the proper privileges for this.

  2. #2
    Lively Member
    Join Date
    Mar 2001
    Location
    Le Havre
    Posts
    66
    I want this to do this in a program not in the enterprise manager
    Vix
    VB6 SP4
    NT4 SP6 & W2K Server SP1
    SQL Server 7 & 2000
    )--( )--( )--(

  3. #3

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    Well, I have never done this, but you could add a reference to Microsoft SQLDMO. The SQLServer class has an EnumProcesss and a KillProcess method. I assume these will do the job.

  4. #4

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    You got me interested, and I put something together.
    Add two commandbuttons (cmdEnum and cmdKill) and a listbox (List1) to a form. Add a reference to Microsoft SQLDMO object library (I assume you have installed the SQL Server components) and add this code. You have to change the code in the form load event; set the servername, login name and password.
    Code:
    Option Explicit
    Dim svr As SQLServer
    
    Private Sub Form_Load()
        Set svr = New SQLServer
        svr.Connect "MyServerName", "sa", ""
    End Sub
    
    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
        svr.Close
        Set svr = Nothing
    End Sub
    
    Private Sub cmdEnum_Click()
    Dim qr As QueryResults
    Dim iCnt As Integer
        List1.Clear
        Set qr = svr.EnumProcesses
        For iCnt = 1 To qr.Rows
            List1.AddItem qr.GetColumnString(iCnt, 3)
            List1.ItemData(List1.NewIndex) = qr.GetColumnLong(iCnt, 1)
        Next
        Set qr = Nothing
        'EnumProcesses will return a resultset containing:
        'spid (smallint, SQL Server process ID)
        'status (nchar(31), Execution state, such as running or sleeping)
        'loginname (nvarchar(129), Name of the SQL Server login)
        'hostname (nchar(129), If applicable, network name of the client workstation)
        'program_name (nchar(129), If applicable, name of the client application)
        'cmd (nchar(34), Abbreviated indicator of current command. AWAITING COMMAND when no command is current)
        'dbname (nvarchar(129), Database currently in use by process)
        'cpu (integer, Cumulative CPU time for process)
        'memusage (integer, Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process)
        'blocked (smallint, When nonnull, process ID blocking a request of the process ID listed by the row)
    End Sub
    
    Private Sub cmdKill_Click()
        If List1.ListIndex <> -1 Then
            If MsgBox("Are you sure you want to kill this process?", vbYesNo) = vbYes Then
                svr.KillProcess List1.ItemData(List1.ListIndex)
            End If
        End If
    End Sub
    Click the cmdEnum button to fill the listbox. Select an item in the list and click the cmdKill button. I would suggest to be carefull with testing this, since you could get some angry users.

  5. #5
    Lively Member
    Join Date
    Mar 2001
    Location
    Le Havre
    Posts
    66

    Thank You

    Thank you very much, it works perfectly

    Another Question do you know how i can send a message to the hostname that i would kill ??
    Vix
    VB6 SP4
    NT4 SP6 & W2K Server SP1
    SQL Server 7 & 2000
    )--( )--( )--(

  6. #6

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    Sure, but I got the code at home. I will post it later.

    BTW, what OS are the workstations running? Can they receive standard network messages (for instance send with: NET SEND ...)?

  7. #7
    Lively Member
    Join Date
    Mar 2001
    Location
    Le Havre
    Posts
    66

    How ?

    I don't know NET SEND

    How do you use it ?

    The WorkStations are NT4 or 2000
    Vix
    VB6 SP4
    NT4 SP6 & W2K Server SP1
    SQL Server 7 & 2000
    )--( )--( )--(

  8. #8

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    Ok, that will do. I will post the code when I get home (about 17.30 CET).

    To use NET SEND open a command window (dos box) and type:
    NET SEND computername message to send

  9. #9

    Thread Starter
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926
    It took a bit longer, but I played around a bit with it to combine it with the previous code.

    Just download the zip I added.
    Attached Files Attached Files

Posting Permissions

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



Click Here to Expand Forum to Full Width