Tuesday, November 1, 2016

[SQLSentry] modifying the email sent to them.

This wasn't quite in the documentation, but bears mentioning. 

SQLSentry can do many things when a job runs long; send an email, run code, kill the job, etc.  In this case, I kill the job and let it restart, but I want people to know this happened so we can make sure the process continues unabated.  However, by default the message would've appeared at the very bottom of the message. 

Yet I wanted something at the top telling them why this occurred. Patrick at SQLSentry was kind enough to figure it out.  First, add the event. 

Then you'll edit the message within their Message Editor.  The "

Details as per Patrick:

Saturday, September 17, 2016

[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.

Friday, September 9, 2016

[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:

get-content c:\narf\poit.txt
} catch {
write-error $_

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:
    throw $_.Exception.Message
    exit 1

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.

Wednesday, September 7, 2016

[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?


Wednesday, August 10, 2016

[statistics] Get the last 4 stat update time for every index on every table in a database.

Quickie, based off an earlier post. (http://thebakingdba.blogspot.com/2012/02/tuning-statistics-when-were-they.html)

Get the last 4 stat updates for every statistic based on an index. The filter is on the auto_created; flip that to get all the system-created (aka _WA_Sys_00000003_53D770D6) stats, or index-based.

Wednesday, August 3, 2016

[Powershell] some basic blocks of code written this week.

Update 2016/08/10 since I need an anterior brain, and this blog is it, I'm now making this post my "living powershell" document.  I'll update it as I run into problems.  Today: $a vs "$a" vs $a.name (see end)

Mike Fal, a Powershell DBA extraordinaire, would probably shake his head at these.  But given the hard lessons learned this week, I thought I'd share.  I had to rewrite a bash script in Powershell, and some of the things I hadn't done before, so there was definitely a learning curve. And we'll see how the error trapping is - Idera has a webinar covering error-handling in Powershell next week.


First up, a basic powershell function that writes to a file. The encoding isn't strictly necessary here, but I use the code elsewhere.  For whatever reason, standard out-file adds a couple of low-ascii characters at the beginning of the file, which other utilities such as psftp choke on.    (http://stackoverflow.com/questions/15439120/powershell-generating-unknown-character-when-echoing-to-file)  Doesn't matter here, but later I write to a file that I then feed to psftp as a batchfile. 

"mget TESTAREA/$FL" |out-file -append -encoding ascii $CMDS 
 I should just have the function accept a parameter.  And for whatever reason, when it writes to the log, it doesn't include newlines.

#Write whatever is in "$logmsg" to the log file $LOGFILE
function logstamp(){
    $loginfo = get-date -format "yyyyMMdd.HH:mm:ss"  #MM is MONTH, mm is minute
    "$loginfo $logmsg" |out-file -append -encoding ascii $logfile
    $logmsg="" }


I needed to parse out a Unix filename from a file.  The original "ls" looked like this:

-rw-rw-r-- 1 psabcderfg data 646621 Jul 19 16:25 myfile16071901.Z

The unix command to parse it would
cat $fl |awk '{print $9}'

And the powershell equivalent is:
$newfiles = get-content $fl | foreach {$_.split(" ")[8]}
(the number is 8 instead of 9 because powershell arrays start at 0)
(2016/09/07 but I've run into times when it IS 9.  WTF?)

A basic unix2dos-style file change. Found at https://sus42.wordpress.com/2013/01/21/unix2dos-using-powershell/

get-content $thisfile |% {$_.replace("`n", "`r`n")} | out-file -encoding ascii -filepath $thatfile -Force

Since it outputs to a new file, the timestamps won't match, but you could use set-filetimestamps -doc $doc -date $date to change them.


This next part is weird but also very handy.  Because of the way Powershell handles messages returned from applications, as well as standard error, you have to do use a trick to get out all the messages.  I needed this because I wanted all the messages from the application, 7zip here, but also stuff like psftp.

$logmsg = cmd /c 7za.exe e -aoa $thisfile 2>&1 | %{ "$_" }
    if ($LASTEXITCODE -ne 0)
         { logstamp; $logmsg = "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE"; logstamp; throw "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE" }
    else { logstamp; $logmsg = "7-zip Command to extract $thisfile finished successfully"; logstamp }

First, we're invoking it with cmd instead of start-process or just a dot invocation.  That's to make sure we get the messages out, but also to prevent powershell from choking if the app throws an error.  Next up is getting all the messages out from the app and stderr, which is the code from "2>&1" until the end of the line.


So now, we have a couple different things to look at.  $logmsg contains the entirety, both error messages from 7zip, and standard error.

$LASTEXITCODE returns the result code from the APP CALLED.  When it couldn't find the file to zip, $lastexitcode returned was 1.  Otherwise was 0 (success).

Next is $error[0].TargetObject, which I believe is the stderr from the app.

Was running into a problem invoking cpio.  I was having to make a unix-formatted file, and the way I'd check is by opening it in notepad.  Except...

$ldclist = get-childitem * -include *out.Z

I wanted to write those out to a file.  So I thought I'd do this to test:

That does indeed give me the directory info on each file.

But I just want the name of each file and didn't think about $myvarname.myfieldname.
So I ran this:
"$ldclist"|out-file -append -encoding ascii listoffiles

Which, superficially looks right.  My script auto-converted it to unix-format, and then when I open it in notepad, it's all on one line.  Denoting LFs, right?  Nope.  Using "$myvar" means it returns it as a list, space-separated.

Just to make it more fun, let's try this:
    $ldclist.name|out-file -append -encoding ascii a
    "$ldclist.name"|out-file -append -encoding ascii b

Those should be close, right?  We now know one would return the space-separated list (the quotes converting it to a string, IIRC), and the other the correct list?  Nope.  The first one returns just the base filenames.  The second one, in the quote, returns the full filename including path, space-separated.


Additionally, problems arise when trying to do something like this:
$logmsg = cmd/c 7za.exe -e -aoa $thisfile -o"$newdirectory"

It just doesn't work.  It actually dumped them into a folder called $newdirectory.
I wound up having to do the below, though I still couldn't use double-quotes. I know there's gotta be a way, but at this point I give up.
$command = "7za.exe -e -aoa $thisfile -o$newdirectory"
$logmsg = cmd /c $command 2>&1 | %{ "$_" }

2016/09/29 hey, a new installment! I have a ton more ,but this one drove me up a wall today, so here we go.

This time - powershell helping with small arrays.
I was trying to make sure I can hit a server and pull back some server info.
One thing I wanted to look at was the number of drives the server had.  Which worked great - on some servers.  On the others, blank.  Not 0, just... blank.

Here's the command I was running:
$drives = (gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}).count

What's different?  Well, some of the servers only have 1 drive.
Turns out, if there's only 0 or 1 members in what's returned, it assumes it doesn't need to be an array.  Which means that count, for instance, won't work on it properly.

$drives = gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}|measure

So the "measure" counts the number of items, even if it's only 1.  
Then when I query it, I use $drives.count, which gives me the proper number/.

[Presenting] SQL Saturday San Antonio just around the corner! #SQLSatSA

Just a friendly reminder.  SQLSaturday San Antonio 2016 is next weekend (occurs August 13th), at Texas A&M University San Antonio!  


I'm looking forward to this.  I was trying to pick courses to take, and it's difficult.  Courses on Hekaton, Clusters, Performance Tuning, 2016 features, Plan Cache... even one on Service Broker, one of my favorite hidden features!  Some "big names" there, too - Kalen Delaney, who also has a pre-con!  Speakers all the way from Pittsburg, Seattle, Albuquerque and more.

I'll be speaking on the same thing I've done a bunch - Event Notifications.  Same speech, a few new tricks and tips, but mostly it's about DBA empowerment.  If you don't know what code was changed 3 weeks ago, what hope do you have for tracking down "why did this stop working"?  Be the Overlord your end-users need. 

Thursday, July 28, 2016

[WAT] Fun with table variables

Run this code.  Did you expect it to act a certain way?  Why?  Because table variables.  Thanks to (crap, who was it? *sigh*) who pointed this out in his tempDB talk a year or so ago.

Update 2016/08/15 - I heard a good use for this at #SQLSatSA!   Use it as part of an ETL.  When it fails and rolls back, you know which batch you were on.  : D

DECLARE @blah TABLE (fielda VARCHAR(20))

UPDATE @blah
SET fielda = 'c'


Friday, July 8, 2016

[Powershell] Killing that one instance of SSMS

WMI in action!  I was running SSMS 2016 and it locked up on me.  So while I waited for it to become responsive I started up SSMS 2014. Still locked, but which do I kill in taskman? (The easy answer, is, of course: change the open query and then look in Applications, not Processes). I didn't think of that, so used WMI.  You could also get fancy and figure out which is the oldest instance of SSMS and do it that way.

Pretty simple: Tell it the path (which is 130 for SSMS 2016), and kill that.

$processes = Get-WmiObject -query "select * from Win32_Process where executablepath like '%130%ssms%'"

foreach ($process in $processes) {
  $returnval = $process.terminate()
  $processid = $process.handle

if($returnval.returnvalue -eq 0) {
  write-host "The process $ProcessName `($processid`) terminated successfully"
else {
  write-host "The process $ProcessName `($processid`) termination has some problems"