T-SQL results to .txt file , send alert if null? From powershell script...

Question

Disclaimer: i still suck at writing scripts please be gentle if this is a dumb question! :)

What i'm doing is pretty simple. I am using a SqlServerCmdletSnapin which gives me the ability to run t-sql queries in powershell 1.0 and return it to either the console or a out-file. I am currently sending it to a .txt file, my code so far is here:

$sqlcheck = Invoke-Sqlcmd -Query "select * from client" -ServerInstance "TESTDB1\TESTSQL1" | out-file -filepath "C:\MyFolder\Client_Result.txt" 

The above returns a line of sql (example):

1 CAAT PI 2003-08-05 13:34:00 PI 2003-08-05 13:34:00

So this sends the results to the .txt file. What im trying to accomplish is from the results that print to the .txt file, if it returns nothing or blank, than that's a failure and it should send an e-mail.

I already wrote an e-mail function for this but what i'm stumbling on is how i'm actually going to check for empty results or i guess $null results. I'm probably using the completely wrong logic but, im writing something like this:

$sqlcheck = Invoke-Sqlcmd -Query "select * from client" -ServerInstance "TESTDB1\TESTSQL1" | out-file -filepath "C:\MyFolder\Client_Result.txt" 
if($sqlcheck -is [Array]){
if($sqlcheck[1] -match "OK"){
echo "Status is OK" > C:\MyFolder\Result.log
}
}
else{
Send-Email
}

Obviously not working! Someone please slap me with some knowledge. :)

Thanks!

Answer 1

There are two problems.

First, out-file doesn't output anything. So in $sqlcheck there will be allways $null. You could split it to two statements:

 $sqlcheck = Invoke-Sqlcmd -Query ...
 $sqlcheck | out-file ...

Second, indexes begin with 0, so $sqlcheck[1] is a mistake. It should be $sqlcheck[0].

You can also skip checking the type and use matching for all the items in $sqlcheck (either simple object or array of objects). Generally it is safer to use the @(<item>) syntax as Keith proposes.

if ($sqlcheck -and $sqlcheck -match ...) { ... }

For some experiments look at

if ($null) { 'true' }
if (@($null)) { 'true' }
if (,@($null)) { 'true' }
if (('a','b','c') -match 'c') { 'true' }

Answer 2

In addition to what stej mentions, rather than explicitly check for -is [array] you can use the array subexpression operator @() to ensure the result is an array.

$sqlcheck = @(Invoke-Sqlcmd -Query "select * from client" `
                            -ServerInstance "TESTDB1\TESTSQL1")
if ($sqlcheck.Count -eq 0) {
    Send-MailMessage ...
}
else {
    $sqlcheck > C:\MyFolder\Client_Result.txt
}

In this scenario, $sqlcheck will always be an array either 0, 1 or n elements in it. Also note that PowerShell 2.0 has a built-in Send-MailMessage cmdlet.