1
\$\begingroup\$

I am currently working on a function to check for active SQL Server connections. While the function is functional, I find its implementation a bit verbose. Could you provide suggestions or improvements on how to write it more succinctly and efficiently?

function get-activeConnection
{
    param(
    [parameter(Mandatory)]
    [string]$computerName
   )
try
{
$ErrorActionPreference="stop"
$output=[PSCustOmobject]@{
            error  = 0
            count  = 0
            message = ''
}                
try
{
    $SMOServer = New-Object Microsoft.SQLServer.Management.SMO.Server $computerName        
    $ConnectionStr = "Server=$computerName;Database=Master;Integrated Security=True"
    $Query = "EXEC sp_who2" 
    $Connection = new-object system.Data.SQLClient.SQLConnection
    $outTable = new-object "System.Data.DataTable" 
    $Connection.connectionstring = $ConnectionStr
    $Connection.open()
    $Command = $Connection.CreateCommand()
    $Command.commandtext = $Query 
    $result = $Command.ExecuteReader() 
    $outTable.Load($result)
    $output.count = ($outTable | where{($_.dbname) -isnot [System.DBNull] -and 
($_.dbname -notin ("master","msdb","model","tempdb","SIT_AdminDB"))}).Rows.Count
    $Connection.close()
}
catch
{
    throw
}
return $output
}
catch
{
    $output.error  = -1
    $output.count  = 0
    $output.message = $Error[0].Exception.Message
    return $output
 } 
}

$computername = "sqlserver1"
$a = get-activeConnection -computerName $computerName 
$a | select *
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.