Thursday, October 2, 2014

[Extended Events] converting results of rpc_completed , originally VARBINARY, into XML

Maybe there's a better way to do it.  After futzing with various combinations for an hour, I gave up.

What I'm trying to do:
we have a stored procedure called from a custom app.  Pulls off a Queue, then runs the stored procedure using the contents of the Queue (which is XML).

When I have 1, it's really easy:

declare @msg varbinary
set @msg = 0x300000000134985709sdg8fs0d9f8fa0s7df90 (which would be hex in the real world)
select convert(xml, @msg)

However, I needed to do that for several hundred rows.

So,  the conversion from the native to varbinary(max) was failing - if you CAST or CONVERT it, it actually seems to CHANGE it... which causes the field to no longer be proper XML.  And so the CAST to XML fails.

And thus.... the fix is..... Dynamic SQL (yeah, I cringed too)

[...code to extract the varbinary string removed - multiple CTEs]
create table finished_trace (data_value xml)

DECLARE @min int, @max INT, @sql NVARCHAR(MAX)
SELECT @min = MIN(event_number) , @max = MAX(event_number) FROM #t3
WHILE @min <= @max
    SET @sql = NULL
    SELECT @sql = 'declare @msg varbinary(max)
    set @msg = ' + data_value + '
    select convert(xml, @msg)' FROM #t3 WHERE event_number = @min
    INSERT INTO finished_trace (data_value)
    EXEC (@sql)
    SET @min = @min + 1

Wednesday, September 17, 2014

[Event Notifications] my current slide deck

Thought I'd share this - the entire folder I run off of is shared, but here's the slide deck, embedded.  It should get new changes as I make them. 

Tuesday, September 16, 2014

[Event Notifications] adding a new box to your environment

Short and sweet - just realized I don't already have this on here.  This makes several assumptions in my environment, and you can modify as needed . Yes, it's ugly.  Yes, security holes due to xp_cmdshell and the like.  You've been warned.

Practically: you run this on the box that already has the repository.  You tell it the name of the new server.  It connects to that server, makes sure each side can reach the other, then enables Event Notifications on that new server.  It ends by making a change, waiting several seconds, then selecting from your repository table.  If it shows, you're golden.  If not, time to roll up sleeves and figure it out.

Monday, September 15, 2014

[Event Notifications] Tackling Database Lifecycle Management and Database Drift (via Redgate)

First, I'd like to thank Bill Fellows, Megan Longoria, and the rest of the Kansas City crew for an amazing weekend at SQLSaturday #300, aka #SQLSatKC.  We hit 2 BBQ places with them, another BBQ place and Boulevard Brewing with some other attendees, then went to an amazing baseball game.  Saturday was a full day with some awesome training (everything from "What's a DBA" to collecting ultra-detailed memory-consumption information), even more awesome BBQ, and ended at the nearby amusement park. 

My presentation went quite well, aside from all 3(!) VMs deciding to BSOD on me 15 minutes before I went on.  Good number of attendees (always a concern for me, personally).  Had some good questions, I like the audience participation & humor I've added, and had a good conversation on it afterwards. Overall, a definite win.  Now I need a new presentation so I can come back next year!


Normally I wouldn't just link to someone else's article, but they are more eloquent than me.  A good article on why you should track database changes in production (like I do with SQL Watchdog, aka "Master of All I Survey", aka "Evil Overlord").  That being said, I love the term "Database Drift", and I'm glad to see more companies looking into solving this issue.

Tuesday, September 9, 2014

[Event Notifications] Getting auto-TFS checkin set up for TFS 2013 (SQLWatchdog)

We recently upgraded to TFS 2013, so here are the steps (written as I do it) to get it running.

Aside: Since I'm using Powershell to run all this, why do I use the TF.cmd application instead of the Cmdlets?  Simplicity.  The Cmdlets appear to need the FULL version of a TFS client installed, plus you have to grab and install the cmdlets... and there are bugs if you don't use the right (x32 vs x64) version of the cmdlets with the right version of powershell.  So I built it around the TF CLI (Command-Line Interface).  The original post was for 2008, the new one (plus script below) are for 2013.

Upgrading from 2008 to 2013 blows, since the config and cache can be screwed up.
What I had to do was completely drop the workfold and the workspace (PROTIP: first, list your workfold and workspaces and SAVE THOSE DETAILS), delete the entirety of the cache folder, delete everything from my EN_Workspace folder on disk, then rebuild from scratch.
One other oddity: before, from the root of my installation (c:\tfs_cli) I could run the script, and it could properly list the folders underneath it.  That appears to have changed for some reason; my Powershell script now CDs into the subfolder before it proceeds.  Not a major change... but took several hours to diagnose, since I was also fighting with other issues on the setup.  Caveat Emptor and all that.

Also for the TFS newbies: the default path you use for a LOT of things is:
http://myservername:8080/tfs/DefaultCollection .

Steps to get going:
  • Install Java.  Got it from Oracle, which doesn't appear to have the same cruftware that Sun's does. Version 7-something
  • Grab Team Foundation Everywhere 2013. Reading the docs, it works for TFS 2010, 2012, 2013, and Online.
  • Unzip into one massive folder (I called mine "c:\TFS_CLI\APP_2013"), then try running (since I'm on Server 2012, in Powershell):
    • ".\tf.cmd" 
    • it works - success!  Sorta.
  • Found this page which seems to make the below setup easier: 
  • For the rest of this, I'm doing it as the Service account that SQL Server is running under on the box that will run the job. Oddly enough, it wasn't set that way when I built it on 2008, but it continued to work even though my password changed.  Note that nowhere in here do you actually put in a password.
  • Type ".\tf eula", then accept ("y"). 
  • muddle through the web site (tfsservername:8080/tfs) and add the SQL Service account as an admin to the branch I'm using (YourENProject).
  • Using the MS page above:
    • .\tf workspace -new TF_Workspace -collection:http://mytfservername:8080/tfs/DefaultCollection
      • the collection URL is pretty standard - it should work in 2010,2012,2013.
    • .\tf workfold -map $/YourENProject -workspace:TF_Workspace c:\TFS_CLI\EN_Workspace
      • This line has several names.  Let's break them out.
      • $/YourENProject is the "Branch" in TFS.  Call it something like $/ProductionEN or something like that.  I created the branch using SSDT before I did any of this, but you can create one using the TF.exe
      • TF_Workspace is the workspace you created in the step above.
      • c:\TFS_CLI\EN_Workspace - the physical folder on your EN server (NOT the TFS server).  I named them differently to make it more obvious what each part of the command is doing.
    • now try and check something in.
      • Get to the workspace
        • cd c:\TFS_CLI\EN_Workspace
      • new-item test.txt -type file
      • &cmd /c "c:\tfs_cli\app_2013\tf add test.txt"
      • &cmd /c "c:\tfs_cli\app_2013\tf checkin /author:memyselfandi"
      • I use that weird syntax to catch errors and return as text into Powershell, so it can properly eat and pass on errors as necessary, as opposed to having PS choke on it.
      • Pull and update something: this is where I had massive problems. (use script)
    • Finally verify it worked (I used my "real" TFS client on my desktop).
Now, set up the SQL Script (SP), the Powershell script, and a job that calls the SP.



Friday, September 5, 2014

[Event Notifications] SQLWatchdog! (Coming to KC in a week)

I'm pleased as punch - presenting at SQL Saturday #300, Kansas City, in a week.

The title is "Master of All I Survey", because I wanted a grandiose title that would get butts in seats.  I was the target audience during 2 different PASSes where it was presented, yet didn't go to it because the title/abstract didn't sound relevant.  Sad to say, since I was the target audience!  Queue the "better" title. 

Alas, I seem to be the only one in love with it, so I changed it to "SQLWatchdog", based on a coworker's suggestion (HI, JASON).  It gets the point across - a Watchdog for SQL. 

If you look in this blog, you'll see it referenced as a couple of things, but the tag I try to use is "Event Notifications", so you can find them at

And as the KC SQLSat group appears to know how to have a good time, I'm ALSO going to fill my belly with BBQ, share it with the world after hitting roller coasters, and even sneak in a game.  A wonderful weekend to be sure.

See you there!

Thursday, August 28, 2014

[Event Notifications] saves the day again...

Got the morning ERRORLOG email courtesy of EN.  And I got several hundred messages from ERRORLOG.  During a patch, the Agent XPs were disabled but not reenabled.  So the Agent service was up, but didn't do anything.

Ironically enough, it wasn't just the ERRORLOGs that saved me - it was SQL Sentry, which hit (and failed) the XPs once a minute.  Which, yay, caused an ERRORLOG message, but didn't actually TELL me it wasn't working.  (I've submitted a bug fix for that).

Just another of those "my god, how do I live without this" posts.

ALL my EN code is in this share:

Additionally, my Gists (source code of the code I use in production) are at:

Wednesday, August 27, 2014

[SQLSaturday] Thanks for coming to see me at Oklahoma City!

Thanks again for all the generous hospitality, and the 28(!) people who came to my session.  It seemed like everybody enjoyed it!  I got a bunch of good reviews, and a couple suggestions I'll implement for SQL Saturday 300 (This. Is. Kansas City!) but feel free to let me know what I could have done to improve things - I know the room was warm and I saw people flagging near the end.

Matt Brimer did an amazing job, and I will definitely return.  Great venue, most of the speakers were good, food was awesome.  All around, a fantastic SQLSat.

Slides and everything are available off the SQL Saturday Web site, at:

Note that that's not only all my slides, but the scripts as well.  I have a newer, complete version of the Repository - I'll have that put up on here in the next day or so (updated: , see "EN Full Repository Setup").  That should be everything required to run the code on your servers.  Installers, new databases, everything.


Tuesday, August 19, 2014

[Interesting] Linked Server is cached for a session

Since I spent 20 minutes trying to diagnose it this morning...

Say you have a session open.  That session invokes a query that hits a linked server.

Now change the linked server.  Drop and recreate, point it at a different server.

New sessions get the new definition, but existing sessions use the old definition.

Monday, August 18, 2014

[Presenting] Last minute - SQLWatchdog at the Fort Worth SQL Server User Group, Wednesday, August 20th

Just had a last-minute invite, and it seemed an awesome opportunity to present & hone it one more time before my forthcoming SQL Saturday presentations (OKC this weekend, KC next month) for more details.  It'll be Wednesday, August 20th, 2014, at 7pm.

Yes, this is the presentation I did there almost 2 years ago, expanded, fleshed out, and with Cat Pics. Because if there's one thing a presentation needs, it's Cat Pics.

See you there!