Sql-server – powershell to connect to SQL and display a friendly message when it cannot connect

powershellsql server

We check our SQL servers using powershell. The script goes through all servers in a text file and sends the output to a text file when it can connect. But when it cannot connect, it displays error in the PS window and jumps to the second server in list. Problem with this is,when we schedule this script to run (unattended), we don't get a report of any failure in the text file.
What I really want it to do is….try to connect to a SQL server, if it can, then send output to file and if it can't then write something like "Failed to connect to servername" to the file.
Following is the script I am using as an example

function checkSQLRestart([string] $Hostname )
{

    $con="server=$Hostname;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append 

}

I tried using things like 

if($da -eq -1)
{
     write-host "failed to connect"
}

But it didn't work.

Any help please?

Best Answer

You need to try/catch the exception.

$OutputFile="C:\output.txt"
$HostName="MyServer"

try
{ 
    $con="server=$HostName;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("

            declare @ServerRestartDate datetime
            set @ServerRestartDate = (select crdate from master..sysdatabases where dbid=2)
            if(@ServerRestartDate>GETDATE()-1)
                print @@servername +' was restarted in last 24 hours. Restart date/time = '+cast(@ServerRestartDate as varchar(20))

                                        ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null
    $svr
    $dt|Format-table -AutoSize |Out-File $OutputFile -Append
}
catch [Exception]
{
    // Do something with the exception 
    Write-Host $_.Exception.ToString()
}
Related Question