How to verify whether the SQL server instance is correct or not using powershell?

powershell, powershell-2.0, sql-server

Solution

function SQL-Ping-Instance 
{
    param (
        [parameter(Mandatory = $true)][string] $ServerInstance,
        [parameter(Mandatory = $false)][int] $TimeOut = 1
    )

    $PingResult = $false

    try
    {
        $SqlCatalog = "master"
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $ServerInstance; Database = $SqlCatalog; Integrated Security = True; Connection Timeout=$TimeOut"
        $SqlConnection.Open() 
        $PingResult = $SqlConnection.State -eq "Open"
    }

    catch
    {
    }

    finally
    {
        $SqlConnection.Close()
    }

    return $pingResult
}

if (SQL-Ping-Instance $srvInstance)
{
    $Qresult= Invoke-sqlcmd -query $SelectQuery -ServerInstance $srvInstance
    $Qresult = $Qresult| % { $_.$columnName+"`n" }
    LogWrite "$Qresult`n"
}
else
{
    LogWrite "Couldn't contact $srvInstance"
}

Problem

We are using Invoke-sqlcmd cmdlet to execute SQL query using powershell. If SQl server instance is given as wrong it is throwing exception. Though i have captured the error in try catch still it throw exception in console as "Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server" ``` try { $Qresult= Invoke-sqlcmd -query $SelectQuery -ServerInstance $srvInstance $Qresult = $Qresult| % { $_.$columnName+"`n" } LogWrite "$Qresult`n" } catch { Write-error "Error occured when executing sql $SelectQuery" LogWrite $Error[0] } ``` How to verify whether SQL server instance is available and it is running before executing any query?

Original source