[Presenting] pics from #SQLSatCoS

[Cooking] No bake "puppy chow" (peanut-butter-chocolate-chex)

One of my wife's sickening-sweet-but-it's-like-crack recipes.

Produces: 20 cups

1 1/8 cup butter
4 1/2 cup semi sweet chocolate chips.
1 1/2 cup peanut butter
2 lbs powdered sugar
20 cups rice crispies or chex cereal

Melt all but sugar in microwave. Dump the cereal in plastic bags (double layered, trust us on this), add chocolate, mix.  Once all coated, add sugar "a large bit at a time", so that you're coating bits of it.  Keep adding until no longer sticky.

[Powershell] getting a cmdexec step to fail with useful errors when running a PoSH script

TL;DR: put $ErrorActionPreference="Stop" at the top of your script

I feel like a dunce. 

I couldn't get a Powershell script to fail, when it had errors, when invoked as a "Operating system (CmdExec)" step in SQL Server Agent.  The first script I wound up rewriting as a PsExec (aka SQL Powershell Provider) script, but this second one I already had working in the ISE.


#save this one line as c:\blah.ps1
get-content c:\narf\poit.txt

And a job step as "Operating System (CmdExec)" (not scripting it out since it's 50 unnecessary lines):
powershell "c:\blah.ps1" 

I know the file is bad - in fact, the directory doesn't even exist!
Hence, it crashes in the ISE:
get-content : Cannot find path 'C:\narf\poit.txt' because it does not exist.
At \c:\temp\blah.ps1:4 char:1
+ get-content c:\narf\poit.txt
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\narf\poit.txt:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
 
However, when run via SQL Agent, it succeeds.  GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet.  Nothing.  Every single one of them succeeded.

Then I remembered that by default, even though it had an error, by default errors always continue.  ($ErrorActionPreference="Continue".  So I added this line at the top:

#save these TWO lines as c:\blah.ps1
$ErrorActionPreference = "Stop"
get-content c:\narf\poit.txt

Lo and behold, now it fails.  DOH!

So, since we're here, let's try using some of the blocks I found on the internet and see what they return.

• https://social.technet.microsoft.com/Forums/windowsserver/en-US/066eab18-1105-4462-ae70-8efeb8510ccc/sql-job-not-failing-when-powershell-script-runs-and-fails?forum=winserverpowershell
• http://stackoverflow.com/questions/9111752/how-to-show-error-in-powershell-step-called-from-sql-server-2008-r2-job
•  http://www.sqlservercentral.com/Forums/Topic1662248-1351-1.aspx

• Just running the code, nothing fancy:

Executed as user: thebakingdba. Get-Content : Cannot find path 'C:\narf\poit.txt' because it does not exist.  At c:\this_will_fail.ps1:4   char:12  + get-content <<<<  c:\narf\poit.txt      + CategoryInfo          : ObjectNotFound: (C:\narf\poit.txt:String) [Get-C      ontent], ItemNotFoundException      + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo      ntentCommand.  Process Exit Code 1.  The step failed.

• if I use a TRY..CATCH block, slightly different:

try
{
get-content c:\narf\poit.txt
} catch {
write-error $_
[System.Environment]::Exit(1)
}

Message:
Executed as user: thebakingdba. c:\this_will_fail.ps1 : Ca  nnot find path 'C:\narf\poit.txt' because it does not exist.  At line:1 char:75  + c:\this_will_fail.ps1 <<  <<       + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorExcep      tion      + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio      n,this_will_fail.ps1.  Process Exit Code 1.  The step failed.

• Thie results of this TRY..CATCH block is identical to running it without the TRY..CATCH:

} catch {
$output = $_.ErrorDetails
throw $output
}

• This fourth example is where it gets interesting, but for my needs not terribly useful.  More info, but it's more "internals".  

} catch {
$ex = $_.Exception
throw "$ex.Message"
}

Executed as user: thebakingdba. System.Management.Automation.ItemNotFoundException: Cannot find path 'C:\narf\p  oit.txt' because it does not exist.     at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(String pat  h, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletProvider pr  ovider, CmdletProviderContext context)     at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPath(St  ring path, CmdletProviderContext context, Boolean allowNonexistingPaths, Cmdlet  Provider& providerInstance)     at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFromMona  dPath(String path, Boolean allowNonexistingPaths, CmdletProviderContext context  , CmdletProvider& providerInstance)     at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSPath(S  tring path, CmdletProviderContext context)     at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String[] pa  thsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult, CmdletPr  oviderContext currentCommandContext).Message  At c:\this_will_fail.ps1:1  8 char:6  + throw <<<<  "$ex.Message"      + CategoryInfo          : OperationStopped: (System.Manageme...ontext).Mes      sage:String) [], RuntimeException      + FullyQualifiedErrorId : System.Management.Automation.ItemNotFoundExcepti      on: Cannot find path 'C:\narf\poit.txt' because it does not exist.         at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(Strin      g path, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletP      rovider provider, CmdletProviderContext context)         at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPa      th(String path, CmdletProviderContext context, Boolean allowNonexistingPat      hs, CmdletProvider& providerInstance)         at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFro      mMonadPath(String path, Boolean allowNonexistingPaths, CmdletProviderConte      xt context, CmdletProvider& providerInstance)         at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSP      ath(String path, CmdletProviderContext context)         at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String      [] pathsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult      , CmdletProviderContext currentCommandContext).Message.  Process Exit Code 1.  The step failed.

• Another variation, this one just throwing an error, and trying to throw the error level: (It doesn't give the level)

 } catch {
THROW 'Throw Error: no file!'; %errorlevel% #the quotes don't seem to matter; w/ and w/o both wrong
}

Executed as user: thebakingdba. Throw Error: no file!  At c:\this_will_fail.ps1:2  3 char:6  + THROW <<<<  'Throw Error: no file!'; %errorlevel%      + CategoryInfo          : OperationStopped: (Throw Error: no file!:String)       [], RuntimeException      + FullyQualifiedErrorId : Throw Error: no file!.  Process Exit Code 1.  The step failed.

• One that a coworker developed that I hadn't known of:

}catch{
    throw $_.Exception.Message
    exit 1
    Break
}


Executed as user: thebakingdba. Cannot find path 'C:\narf\poit.txt' because it does not exist.  At c:\this_will_fail.ps1:2  6 char:7  +     throw <<<<  $_.Exception.Message      + CategoryInfo          : OperationStopped: (Cannot find pat...does not ex      ist.:String) [], RuntimeException      + FullyQualifiedErrorId : Cannot find path 'C:\narf\poit.txt' because it d      oes not exist.  Process Exit Code 1.  The step failed.

[Presenting] SQLSaturday Dallas, September 24th, 2016!

The first SQLSaturday I ever presented at!  This time around I'm covering CIM and WMI and WQL.  Come learn why you should care, but also come get your brain melted by Bob Ward - something for everyone!

Man, this ought to be a monster of a SQLSat, with those speakers.  Now to cajole them into doing a BBQ Crawl.  Who's up for Pecan Lodge at 10:30-10:45 on Friday?

http://www.sqlsaturday.com/563/eventhome.aspx