Running SQL Queries On Multiple Servers At Once Using Registered Servers In SQL Server Management Studio

I just came across a situation where I was looking at long running queries across a few production servers, I wanted to quickly and easily run my query across them all to make sure that I wasn’t missing a problem on each server where a user had left a specific query for a looong time.

Thankfully, registered servers is here to the rescue! Many people using SSMS have never even used registered servers, and if they have, they dont know about this secret context menu hiding in the GUI.

What’s a registered server?

A registered server is effectively a way to remember that you have connected to a server, and allow easy access to connecting to it again. (There is probably a MUCH more technical explanation, but this one suffices. (Want more? Check the “BOL”:page)

In SSMS you can simply right click any active server and select “Register” to bring up the prompt to register the server. If you are running more than one set of servers that should be running different queries, then you may want to create additional server groups (when we get there).

In SSMS 2008, you can simply use the shortcut CTRL+ALT+G or select “Registered Servers” under the View menu.

Once that is complete, you should be able to right click any group you have created, and select “New Query”.

When you run your query, you should notice that an additional column exists now, with the label of the server that returns the query results.

Enjoy!

Learn More

Popular Music For My Classes

Once in a while I have a few questions from my classes about what music is playing over the speakers during down time or tests. This is some of the music I play for my classes, honed over a while.

This is some of the music I play for my classes, honed over a while.

Playlist 1:

|Name_____________ |Artist__________|Album_____________|
|Flashback|Kavinsky|1986 Vinyl|
|Testarossa (SebastiAn Remix)|Kavinsky|1986 Vinyl|
|Wayfarer|Kavinsky|1986 Vinyl|
|Dead Cruiser|Kavinsky|1986 Vinyl|
|Grand Canyon|Kavinsky|1986 Vinyl|
|Gettysburg|Ratatat|Classics|
|Tacobel Canon|Ratatat|Classics|
|Lex|Ratatat|Classics|
|Montanita|Ratatat|Classics|
|Kennedy|Ratatat|Classics|
|Wildcat|Ratatat|Classics|
|Swisha|Ratatat|Classics|
|Loud Pipes|Ratatat|Classics|
|Nostrand|Ratatat|Classics|
|Tropicana|Ratatat|Classics|
|Bar Rumba|Mo’ Horizons|Elemental Chill – Volume 4|
|The Crossing|Soundtrack|Gattaca|
|It Must Be The Light|Soundtrack|Gattaca|
|Irene & The Morrow|Soundtrack|Gattaca|
|Second Morrow|Soundtrack|Gattaca|
|Not The Only One|Soundtrack|Gattaca|
|The Truth|Soundtrack|Gattaca|
|Call Me Eugene|Soundtrack|Gattaca|
|Impromptu For 12 Fingers|Soundtrack|Gattaca|
|I Thought You Wanted To Dance|Soundtrack|Gattaca|
|Now That You’re Here|Soundtrack|Gattaca|
|The Arrival|Soundtrack|Gattaca|
|The Morrow|Soundtrack|Gattaca|
|Further And Further|Soundtrack|Gattaca|
|Only A Matter Of Time|Soundtrack|Gattaca|
|Traces|Soundtrack|Gattaca|
|The Departure|Soundtrack|Gattaca|
|Up Stairs|Soundtrack|Gattaca|
|God’s Hands|Soundtrack|Gattaca|
|A Borrowed Ladder|Soundtrack|Gattaca|
|Irene’s Theme|Soundtrack|Gattaca|
|The Other Side|Soundtrack|Gattaca|
|Becoming Jerome|Soundtrack|Gattaca|
|The One Moment|Soundtrack|Gattaca|
|Yourself For The Day|Soundtrack|Gattaca|
|Brulee|Ratatat|LP3|
|Flynn|Ratatat|LP3|
|Mi Viejo|Ratatat|LP3|
|Falcon Jab|Ratatat|LP3|
|Mumtaz Khan|Ratatat|LP3|
|Bird-Priest|Ratatat|LP3|
|Shiller|Ratatat|LP3|
|Gipsy Threat|Ratatat|LP3|
|Imperials|Ratatat|LP3|
|Dura|Ratatat|LP3|
|Shempi|Ratatat|LP3|
|Black Heroes|Ratatat|LP3|
|Mirando|Ratatat|LP3|
|Shempi|Ratatat|LP3|
|En Focus (feat. Trinidad)|Quantic|Mishaps Happening|
|Perception|Quantic|Mishaps Happening|
|Sound Of Everything (feat. Ali|Quantic|Mishaps Happening|
|Mishaps Happening|Quantic|Mishaps Happening|
|Furthest Moment|Quantic|Mishaps Happening|
|Trees and Seas|Quantic|Mishaps Happening|
|Use What You Got (feat. Sonny|Quantic|Mishaps Happening|
|Angels and Albatrosses|Quantic|Mishaps Happening|
|Prelude To Happening|Quantic|Mishaps Happening|
|Don’t Joke With A Hungry Man (|Quantic|Mishaps Happening|
|When You’re Through (feat. Spa|Quantic|Mishaps Happening|
|Scavenger|Redial|MOS Mashed 4|
|Toothache Epiphany|nonfinite|Northbridge|
|Seventeen Years|Ratatat|Ratatat|
|El Pico|Ratatat|Ratatat|
|Spanish Armada|Ratatat|Ratatat|
|Breaking Away|Ratatat|Ratatat|
|Lapland|Ratatat|Ratatat|
|Crips|Ratatat|Ratatat|
|Wildcat|Ratatat|Ratatat|
|Cherry|Ratatat|Ratatat|
|Crips|Ratatat|Ratatat|
|Desert Eagle|Ratatat|Ratatat|
|Germany To Germany|Ratatat|Ratatat|
|Lapland|Ratatat|Ratatat|
|Everest|Ratatat|Ratatat|
|Mr Oizo Autodrive T42|Kavinsky|Teddy Boy EP|
|Testarossa Nightdrive|Kavinsky|Teddy Boy EP|
|Transistor|Kavinsky|Teddy Boy EP|
|The Crash|Kavinsky|Teddy Boy EP|
|Arpanet Nightdrive (rework)|Kavinsky|Teddy Boy EP|
|Testarossa Autodrive|Kavinsky|Teddy Boy EP|
|Ghost Transistor|Kavinsky|Teddy Boy EP|
|The Grand Staircase|Port Blue|The Airship|
|In the Control Car|Port Blue|The Airship|
|Into the Gymnasium|Port Blue|The Airship|
|The Axial Catwalk|Port Blue|The Airship|
|Of the Airship Academy|Port Blue|The Airship|
|Up Ship!|Port Blue|The Airship|
|Sunset Cruiser|Port Blue|The Airship|
|Over Atlantic City|Port Blue|The Airship|
|The Gentle Descent|Port Blue|The Airship|
|Under the Glass Observation Dome|Port Blue|The Airship|
|An Enchanted Evening|Port Blue|The Albatross EP|
|City of Safe Harbors|Port Blue|The Albatross EP|
|Silver Blueberry|Port Blue|The Albatross EP|
|Of Japan|Port Blue|The Albatross EP|
|The Power Is On|The Go! Team|Thunder, Lightning, Strike|
|Friendship Update|The Go! Team|Thunder, Lightning, Strike|
|Junior Kickstart|The Go! Team|Thunder, Lightning, Strike|
|Huddle Formation|The Go! Team|Thunder, Lightning, Strike|
|Ladyflash|The Go! Team|Thunder, Lightning, Strike|
|Everyone’s a V.I.P. to Someone|The Go! Team|Thunder, Lightning, Strike|
|Panther Dash|The Go! Team|Thunder, Lightning, Strike|
|Get It Together|The Go! Team|Thunder, Lightning, Strike|
|Feel good by Numbers|The Go! Team|Thunder, Lightning, Strike|
|What Else Is There|NULL|Royksopp|
|Valkyrie|NULL|Redial|
|Valve|NULL|Redial|

Playlist 2:

|It Was …|Lemon Jelly|’64-’95|
|’88 aka Come Down On Me|Lemon Jelly|’64-’95|
|’68 aka Only Time|Lemon Jelly|’64-’95|
|’93 aka Don’t Stop Now|Lemon Jelly|’64-’95|
|’95 aka Make Things Right|Lemon Jelly|’64-’95|
|’79 aka The Shouty Track|Lemon Jelly|’64-’95|
|’75 aka Stay With You|Lemon Jelly|’64-’95|
|’76 aka The Slow Train|Lemon Jelly|’64-’95|
|’90 aka A Man Like Me|Lemon Jelly|’64-’95|
|’64 aka Go|Lemon Jelly|’64-’95|
|One|Ratatat|9 Beats|
|Two|Ratatat|9 Beats|
|Three|Ratatat|9 Beats|
|Four|Ratatat|9 Beats|
|Five|Ratatat|9 Beats|
|Six|Ratatat|9 Beats|
|Seven|Ratatat|9 Beats|
|Eight|Ratatat|9 Beats|
|Nine|Ratatat|9 Beats|
|The Birth and Death of the Day|Explosions in the Sky|All of a Sudden I Miss Everyone|
|Welcome, Ghosts|Explosions in the Sky|All of a Sudden I Miss Everyone|
|It’s Natural to Be Afraid|Explosions in the Sky|All of a Sudden I Miss Everyone|
|What Do You Go Home To?|Explosions in the Sky|All of a Sudden I Miss Everyone|
|Catastrophe and the Cure|Explosions in the Sky|All of a Sudden I Miss Everyone|
|So Long, Lonesome|Explosions in the Sky|All of a Sudden I Miss Everyone|
|Sleep Deprivation|Simian Mobile Disco|Attack Decay Sustain Release|
|glimpse|Helios|Caesura|
|Fourteen drawings|Helios|Caesura|
|Backlight|Helios|Caesura|
|A mountain of ice|Helios|Caesura|
|Mima|Helios|Caesura|
|Hollie|Helios|Caesura|
|One|Aimee Mann|Cinematic Treatment|
|Lex|Ratatat|Classics|
|Gettysburg|Ratatat|Classics|
|Wildcat|Ratatat|Classics|
|Tropicana|Ratatat|Classics|
|Loud Pipes|Ratatat|Classics|
|Kennedy|Ratatat|Classics|
|Swisha|Ratatat|Classics|
|Nostrand|Ratatat|Classics|
|Tacobel Canon|Ratatat|Classics|
|Tank !|Cowboy Bebop|Cowboy Bebop OST|
|Something|The Willowz|Eternal Sunshine of the Spotless Mind|
|Yo Pumpkin Head|Cowboy Bebop|Future Blues|
|Central Plains|Carbon Based Lifeforms|Hydroponic Garden|
|Tensor|Carbon Based Lifeforms|Hydroponic Garden|
|MOS 6581 (Album Version)|Carbon Based Lifeforms|Hydroponic Garden|
|Silent Running|Carbon Based Lifeforms|Hydroponic Garden|
|Neurotransmitter|Carbon Based Lifeforms|Hydroponic Garden|
|Hydrophonic Garden|Carbon Based Lifeforms|Hydroponic Garden|
|Exosphere|Carbon Based Lifeforms|Hydroponic Garden|
|Comsat|Carbon Based Lifeforms|Hydroponic Garden|
|Epicentre (First Movement)|Carbon Based Lifeforms|Hydroponic Garden|
|Artificial Island|Carbon Based Lifeforms|Hydroponic Garden|
|Refraction 1.33|Carbon Based Lifeforms|Hydroponic Garden|
|The Pulse|Digitalism|Idealism|
|The Bachelors Remix|RJD2|In Rare Form|
|Big Game|RJD2|In Rare Form|
|Explosive|RJD2|In Rare Form|
|Fuck A Soundcheck|RJD2|In Rare Form|
|Incorporate Anthem|RJD2|In Rare Form|
|Inhale Remix|RJD2|In Rare Form|
|Kill ’em All Remix|RJD2|In Rare Form|
|Mic Control|RJD2|In Rare Form|
|One Day|RJD2|In Rare Form|
|Rocket Science|RJD2|In Rare Form|
|Saliva|RJD2|In Rare Form|
|Seven Light Years|RJD2|In Rare Form|
|The Takeoff|RJD2|In Rare Form|
|Under the Hammer|RJD2|In Rare Form|
|Unlimited|RJD2|In Rare Form|
|Up to No Good Again|RJD2|In Rare Form|
|Uprock|RJD2|In Rare Form|
|Weatherpeople|RJD2|In Rare Form|
|Nervous Tension|Lemon Jelly|Lemonjelly.KY|
|A Tune For Jack|Lemon Jelly|Lemonjelly.KY|
|His Majesty King Raam|Lemon Jelly|Lemonjelly.KY|
|The Staunton Lick|Lemon Jelly|Lemonjelly.KY|
|Homage To Patagonia|Lemon Jelly|Lemonjelly.KY|
|Kneel Before Your God|Lemon Jelly|Lemonjelly.KY|
|Page One|Lemon Jelly|Lemonjelly.KY|
|Come|Lemon Jelly|Lemonjelly.KY|
|Elements|Lemon Jelly|Lost Horizons|
|Space Walk|Lemon Jelly|Lost Horizons|
|Ramblin’ Man|Lemon Jelly|Lost Horizons|
|Return to Patagonia|Lemon Jelly|Lost Horizons|
|Nice Weather for Ducks|Lemon Jelly|Lost Horizons|
|Experiment Number Six|Lemon Jelly|Lost Horizons|
|Closer|Lemon Jelly|Lost Horizons|
|The Curse of Ka’zar|Lemon Jelly|Lost Horizons|
|So Easy|Rv?yksopp|Melody A.M.|
|Eple|Rv?yksopp|Melody A.M.|
|Sparks|Rv?yksopp|Melody A.M.|
|In Space|Rv?yksopp|Melody A.M.|
|Poor Leno|Rv?yksopp|Melody A.M.|
|A Higher Place|Rv?yksopp|Melody A.M.|
|Rv?yksopp’s Night Out|Rv?yksopp|Melody A.M.|
|Remind Me|Rv?yksopp|Melody A.M.|
|She’s So|Rv?yksopp|Melody A.M.|
|40 Years BackCome|Rv?yksopp|Melody A.M.|
|Remind Me|Rv?yksopp|Melody AM|
|Femme d’Argent|Air|Moon Safari|
|Zdarlight (Voyage Mix)|Digitalism|My House Is Your House, Vol. 1 Disc 1|
|First Breath After Coma|Explosions in the Sky|The Earth Is Not a Cold Dead Place|
|The Only Moment We Were Alone|Explosions in the Sky|The Earth Is Not a Cold Dead Place|
|Six Days at the Bottom of the Ocean|Explosions in the Sky|The Earth Is Not a Cold Dead Place|
|Memorial|Explosions in the Sky|The Earth Is Not a Cold Dead Place|
|Your Hand in Mine|Explosions in the Sky|The Earth Is Not a Cold Dead Place|
|Intro|Carbon Based Lifeforms|The Path|
|Behind the corner|Carbon Based Lifeforms|The Path|
|Rain|Carbon Based Lifeforms|The Path|
|Rise to tomorrow|Carbon Based Lifeforms|The Path|
|Hold|Carbon Based Lifeforms|The Path|
|Machinery|Carbon Based Lifeforms|The Path|
|And contact|Carbon Based Lifeforms|The Path|
|Sinful things|Carbon Based Lifeforms|The Path|
|Dreamshore forest|Carbon Based Lifeforms|The Path|
|Submerged|Carbon Based Lifeforms|The Path|
|Contaminated area|Carbon Based Lifeforms|The Path|
|Last breath|Carbon Based Lifeforms|The Path|
|Station blue|Carbon Based Lifeforms|The Path|
|Or plan B|Carbon Based Lifeforms|The Path|
|Eple (Black Strobe Remix)|Royksopp|The Remix Album|
|Remind Me (James Zabiela Eighties Ingeborg Mix)|Royksopp|The Remix Album|
|Poor Leno (Silicon Souls Dub Remix)|Royksopp|The Remix Album|
|49 Percent (Angello & Ingrosso Remix)|Royksopp|The Remix Album|
|What Else Is There (Vitalic Remix)|Royksopp|The Remix Album|
|Sparks (Murk Downtown Miami Remix)|Royksopp|The Remix Album|
|So Easy (So B.H.Q. Remix By Derrick Carter)|Royksopp|The Remix Album|
|Remind Me (Someone Else’s Radio Remix)|Royksopp|The Remix Album|
|Only This Moment (Alan Braxe And Fred Falke Remix)|Royksopp|The Remix Album|
|49 Percent (Mandy Remix)|Royksopp|The Remix Album|
|Eple (Fatboy Slim Mix)|Royksopp|The Remix Album|
|Poor Leno (Sander Kleinenberg Mix)|Royksopp|The Remix Album|
|Remind Me (Ernest Saint Laurent Moonfish Mix)|Royksopp|The Remix Album|
|What Else Is There (Jacques Lu Cont Radio Mix)|Royksopp|The Remix Album|
|What Else Is There (Thin White Duke Mix)|Royksopp|The Remix Album|
|Beautiful Day Without You (Rex The Dog Remix)|Royksopp|The Remix Album|
|Day One|Explosions in the Sky|The Rescue|
|Day Two|Explosions in the Sky|The Rescue|
|Day Three|Explosions in the Sky|The Rescue|
|Day Four|Explosions in the Sky|The Rescue|
|Day Five|Explosions In the Sky|The Rescue|
|Day Six|Explosions In the Sky|The Rescue|
|Day Seven|Explosions In the Sky|The Rescue|
|Day Eight|Explosions In the Sky|The Rescue|
|Abiogenesis|Carbon Based Lifeforms|World of Sleepers|
|Vortex|Carbon Based Lifeforms|World of Sleepers|
|Autumn in Ganymede|NULL|Cowboy Bebop|
|Blues Sax|NULL|Cowboy Bebop|
|Go Go Cactus Man (Whistling Song)|NULL|Cowboy Bebop|
|Tank! (Luke Vibert Remix)|NULL|Cowboy Bebop|

Mounting ISO Images in Mac OS X Without Expensive Software

I was speaking with a colleague today about ISO files/images in Mac OS X, and when he expressed his disappointment with existing software.
I said I would show him some cheap and easy ways to mount ISO files and how to backup any CDROM using Disk Utility.

Mounting an ISO on your Mac is accomplished using the hdiutil command.
To attach an image file,

  1. you will first need to open up your Terminal.app
  2.  type  hdiutil attach -readonly filename where filename is the full unix path of your file (just to be safe). When I was testing this my command looked like “hdiutil attach -readonly /Users/ck/Downloads/cdrom-1.33.iso”.
  3. If you run into problems or find your image type says not supported, generally because it is a .cdr file, try hdiutil attach -imagekey diskimage-class=CRawDiskImage filename.

This should mount the image in your Finder sidebar as you would expect a normal CDROM would. If we are concerned with creating images with Disk Utility, that is as simple as dragging the inserted CD onto the Disk Utility application, or selecting it within Disk Utility and selecting New Image.

Looking for the Excel 2008 Analysis Toolpak for Mac?

I also spent a good twenty minutes looking for the Excel 2008 Toolpak addon which Microsoft advertises in their knowledge base, unfortunately this link is broken and there is no place to download and install said addon.

Do not despair! The functionality you seek is actually included in Excel, you just need to go looking for it. If you check your formula bar/builder, you will find that the statistical tools you crave(like the nice correlation tool) are right there.

Enjoy!

MySQL Gem and Rails 3.0beta4 issues

If you are running OS 10.6 (Snow Leopard) and you are having problems installing the mysql gem via bundler, there may be a simple fix.

While fooling around with bundler/macports, I was having an issue where bundler would throw an exception:

ERROR:  Error installing mysql:
ERROR: Failed to build gem native extension.
/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin/ruby extconf.rb
extconf.rb:10: command not found: /opt/local/bin/mysql_config5 –cflags
ERROR:  Error installing mysql:        ERROR: Failed to build gem native extension.
/System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/bin/ruby extconf.rbextconf.rb:10: command not found: /opt/local/bin/mysql_config5 –cflags

It took me a good 5 minutes of tracking down the problem to find out that /opt/local/bin/mysql_config5 was a symlink to /opt/local/lib/mysql5/bin/mysql_config, and that for some reason, while the file was there and filled with delicious code, it wasn’t executable.
One sudo chmod +x later, the mysql gem installs like a charm.

Finding services advertised with Bonjour via Flame

Have you ever had an issue with someone sharing an iTunes library, and you want to quickly track down the IP of the offending user?

Here is a quick little guide on getting that done.

dns-sd is a service discovery application that can make tracking down users sharing various information quite easily. It also has a great API for accessing various information about local DNS information, and setting local DNS advertising quite easily.

First, an example:

dns-sd -B _daap._tcp
This command checks for bonjour iTunes shared libraries (daap protocol, tcp, .local subnet).
Output:
Browsing for _daap._tcp
Timestamp   A/R Flags if Domain Service Type Instance Name
9:31:12.844  Add  3  4 local.  _daap._tcp.   local’s Library_PW
9:31:12.844  Add  2  1 local.  _daap._tcp.   local’s Library_PW
local:~ local$ dns-sd -L "local’s Library_PW" _daap._tcp
Lookup local’s Library_PW._daap._tcp.local
local’s Library._daap._tcp.local. can be reached at
local.local.:3689
(interface 1)
Flags: 1
txtvers=1 iTSh
Version=196609
MID=************
Database ID=D18C43A55BFAC99B
dmv=131078
Version=196616
OSsi=0x1F8
Machine Name=local’s Library Media
Kinds Shared=3145839
Machine ID=************
Password=1

With these commands, it gives you the hostname, which you can always resolve by pinging it. This is a slightly edited output for readability. (spaces and newlines are escaped in the shell, but you see all the slashes)

How is this better than other solutions like Flame or Bonjour Browser?
Well, with this approach, we can actually script the entire event into one bash script, without too much work.
A minute of checking the man pages for sed and cut yields:

#!/bin/bash
# create semi-random filenames for writing and reading to
tempfoo=`basename $0`
TFILE=`mktemp /tmp/XXXXXX` || exit 1
TFILE2=`mktemp /tmp/2XXXXXX` || exit 1
# grabs browseable list of bonjour shared libraries
dns-sd -B _daap._tcp 1>$TFILE &
# grab the process identifiers for the dns-sd process WHICH NEVER STOPS
TFILE_PID=$!
sleep 1
# takes the last library in the list and formats it for dns-sd -L
res=$(tail -n1 $TFILE | awk '{gsub(/[ ]+/," ")}1' | cut -d' ' -s -f6-)
# read the protocol and then name in, allows us to use different protocols in the future
read -r protocol name << "$res"
# look up specific libraries
dns-sd -L "$name" "$protocol" 1>$TFILE2 &
# grab the process identifiers for the dns-sd process WHICH NEVER STOPS
TFILE2_PID=$!
# we have to wait a second because dns-sd -L can take a while to respond
sleep 1
res2=$(cat $TFILE2 | awk '{gsub(/[ ]+/," ")}1')
# for some reason cut wasnt respecting the spaces, but in another var it worked fine
ip=$(echo $res2 | cut -d' ' -f10| cut -d'.' -f1-2)
if [[ "$ip" == "" ]]
then echo "No computers are sharing their libraries"
else ping $whoa1
fi
# kill the dns-sd processes
# remove the temporary files
kill $TFILE_PID
kill $TFILE2_PID
rm $TFILE
rm $TFILE2

…and by a minute, I meant more like an hour. I commented most of the material, and the source can also be found at https://gist.github.com/ConstantineK/d2179abf2f31c041c1e1.

A lot more went into this than initially expected, but that is always the case when it comes to bash.