PoshCode Archive  Artifact [2ec4394ff1]

Artifact 2ec4394ff163c787fb99e29b09a788952a48394a0f8f9a457a8bb7fa8abe8f46:

• File Killing-db-connections.ps1 — part of check-in [db545f127f] at 2018-06-10 12:56:47 on branch trunk — This powershell function is a simple one that will kill any currently open connections on the specified server and database. There is no requirements other than PowerShell (obviously!! ;) ). (user: caveman_dick size: 1417)

# encoding: ascii
# api: powershell
# title: Killing db connections 
# description: This powershell function is a simple one that will kill any currently open connections on the specified server and database. There is no requirements other than PowerShell (obviously!! ;) ).
# version: 0.1
# type: function
# author: caveman_dick
# license: CC0
# x-poshcode-id: 1283
# x-archived: 2009-08-25T01:00:27
#
#
function KillDBConnections([string]$serverName, [string]$DBName)
{   
    $ConnectionString = "Data Source=$serverName;Initial Catalog=master;Integrated Security=SSPI"
    
    $connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
    $command = New-Object System.Data.SqlClient.SqlCommand;
    
    $command.Connection = $connection;
    $command.CommandType = [System.Data.CommandType]::Text;    
    $command.CommandText = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('$DBName')";
    
    $connection.open();
    $reader = $command.ExecuteReader();
    $stringBuilder = New-Object System.Text.StringBuilder

    while ($reader.Read())
    {
        $stringBuilder.AppendFormat("kill {0};", $reader.GetValue(0));
    }
    
    $reader.Close();
    
    $command.CommandText = $stringBuilder.ToString();
    
    if($command.CommandText)
    {        
        $command.ExecuteNonQuery();
    }
    
    $connection.Dispose();
}