Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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()
}
share|improve this answer
    
Thank you veryyyyyyyyyyyy much! –  Manjot Oct 19 '11 at 22:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.