Using PowerShell to Kick off SQL Server Agent Jobs
We’re in the process of a few upgrades around our office and one of those will require a full shutdown mid-business day. To expedite the shutdown, I wanted a quick way to run our backup jobs (hot backups), move them, and down the boxes.
Remember, I’m lazy–walking around our server room smacking rack switches takes effort. I want to sit and drink coffee while the world comes to a halt.
Here’s a bit of what I came up with. The script handles four things:
- accepts a server name parameter (so I can iterate through a list of servers),
- checks to see if the server is online (simple ping),
- checks to see if it’s a SQL Server (looks for the MSSQLSERVER service),
- iterates through the jobs and, if the -run switch is passed, kicks them off.
As with most, the couple of helper functions are more than the actual script itself.
param (
[string]$server = $(Read-Host -prompt "Enter the server name"),
[switch]$run = $false
)
# little helper function to ping the remove server.
function get-server-online ([string] $server) {
$serverStatus =
(new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
Write-Host "Server Online: $serverStatus"
return $serverStatus
}
# little helper function to check to see if the remote
# server has SQL Server running.
function is-sql-server([string] $server) {
if (get-server-online($server)) {
$sqlStatus = (gwmi Win32_service -computername $server |
? { $_.name -like "MSSQLSERVER" }).Status -eq "OK"
Write-Host "SQL Server Found: $sqlStatus"
return $sqlStatus
}
}
# uhh, because someday I may need to add more to this?
function cannot-find-server([string] $server) {
Write-Host "Cannot contact $server..."
}
if (is-sql-server($server)) {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
$jobs = $srv.JobServer.Jobs | ? {$_.IsEnabled -eq $true}
$jobs |
select Name, CurrentRunStatus, LastRunOutcome, LastRunDate, NextRunDate |
ft -a
if ($run) {
$jobs | % { $_.Start(); "Starting job: $_ ..." }
}
}
else {
cannot-find-server($server)
}
Things left to do–someday…?
- Allow picking and choosing what jobs get kicked off…
It’s not fancy, but it’ll save time backing up and shutting down a couple dozen SQL instances and ensuring they’re not missed in the chaos. 
Getting buildNumber for TeamCity via AssemblyInfo
I’m a proud psake user and love the flexibility of PowerShell during my build process. I recently had a project that I really wanted the build number to show up in TeamCity rather than the standard incrementing number.
In the eternal words of Jeremy Clarkson, “How hard can it be?”
On my local machine, I have a spiffy “gav”–getassemblyversion–command that uses Reflection to grab the assembly version. Unfortunately, since I don’t want to rely on the local version of .NET and I’ve already set the build number in AssemblyInfo.cs as part of my build process, I just want to fetch what’s in that file.
Regular expressions to the rescue!
Here’s the final psake task. I call it as part of my build/release tasks and it generates the right meta output that TeamCity needs for the build version. Here’s a gist of the source: http://gist.github.com/440646.
Here’s the actual source to review:
task GetBuildNumber {
$version = gc $base_directory\$solution_name\Properties\AssemblyInfo.cs | select-string -pattern "AssemblyVersion"
$version -match '^\[assembly: AssemblyVersion\(\"(?<major>[0-9]+)\.(?<minor>[0-9]+)\.(?<revision>[0-9]+)\.(?<build>[0-9]+)\"\)\]'
"##teamcity[buildNumber '{0}.{1}.{2}.{3}']" -f $matches["major"], $matches["minor"], $matches["revision"], $matches["build"]
}
Enjoy!
Using xUnit 1.5 with .NET 4.0 RTW
After a bit of tinkering, I finally managed to find the sweet spot for getting xUnit 1.5 to run (without errors) with projects targeted at the new .NET 4.0 Framework.
After initial solution conversion, if you run your tests with xunit.console.x86.exe (or the 64-bit version, I’m assuming), you’ll face the following helpful error:
System.BadImageFormatException: Could not load file or assembly 'Assembly.Test.dll'
or one of its dependencies. This assmbly is built by a runtime newer than the
currently loaded runtime and cannot be loaded.
File name: 'J:\projects\Framework\build\Assembly.Test.dll'
at System.Reflection.AssemblyName.nGetFileInformation(String s)
at System.Reflection.AssemblyName.GetAssemblyName(String assemblyFile)
at Xunit.Sdk.Executor..ctor(String assemblyFilename)
at Xunit.ExecutorWrapper.RethrowWithNoStackTraceLoss(Exception ex)
at Xunit.ExecutorWrapper.CreateObject(String typeName, Object[] args)
at Xunit.ExecutorWrapper..ctor(String assemblyFilename, String configFilename,
Boolean shadowCopy)
at Xunit.ConsoleClient.Program.Main(String[] args)
What? BadImageFormatException? But the bitness didn’t change!
@markhneedham blogged last year how to fix the problem: updating the config files for xunit to “support” the new version. That worked then, but the version numbers have changed.
Here’s what the new configuration files need to include:
<startup uselegacyv2runtimeactivationpolicy="true"> <supportedruntime version="v4.0.30319" /> </startup>
The useLegacyV2RuntimeActivationPolicy attribute ensures that the latest supported runtimes are loaded. For my projects, this seems to keep Oracle.DataAccess.dll and System.Data.Sqlite.dll (both x86 libraries) happy.
The supportedRuntime element denotes the current version of .NET 4.0 (30319 is the RTM build).
After that, everything runs like a champ!
xUnit.net console test runner (32-bit .NET 4.0.30319.1) Copyright (C) 2007-9 Microsoft Corporation. xunit.dll: Version 1.4.9.0 Test assembly: Assembly.Test.dll Total tests: 397, Failures: 0, Skipped: 0, Time: 7.508 seconds
Crazy Way To Set Display Order From an Enum
I’m sure someone has a better way to do this and I’m all ears; however, this seems to ‘work’ and performs well enough (YAGNI fully applied).
We have an Enum of our different “school levels”–elementary, middle, high schools, special schools, etc. Since the Enum’s values correspond to the data values in our student system, their display/alphabetical orders do not match the common order (e.g. High School = 4, Middle School = 5).
I came up with a simple attribute that’s added to the Enum to “specify” display order and it’s worked like a champ for the past year or so.
[Description("Elementary")]
[DisplayOrder(1)]
Elementary = 2,
[Description("Middle")]
[DisplayOrder(2)]
Middle = 5,
Not at all fancy.
However useful it is at looping and providing context in code, what about using it for ordering OTHER “level” information from another source? So far, I haven’t found a clean way.
The “Solution”?
Use the index of our already-ordered Enum list. In this instance, our model’s SchoolLevel property matches the Description of the Enum.
var levelsInOrder = Enum<level>.ToList();
// AutoMapper mappings, etc.
model.Data = data
.OrderBy(x => levelsInOrder
.FindIndex(z => z.Description() == x.SchoolLevel));
Is it perfect? No, but it works. I could probably even refactor the Enum list and FindIndex call out for a bit more clarity.
model.Data = data.OrderBy(x => x.GetDisplayOrder(x.SchoolLevel));
...
private int GetDisplayOrder(string level)
{
return Enum<level>.ToList().FindIndex(x => x.Description() == level);
}
Is there a better way?
Using .less To Simplify BluePrintCSS
For the past few projects, I’ve used BluePrintCSS and really liked the experience. It forced me both to conquer my CSS layout fears (tables no more) and standardize a few of my formatting techniques that we use on our internal and external applications. Good deal all around.
The one caveat that I really… really didn’t like was how I had to name things.
The clean class codes and IDs that I had…
<div class="page">
<div class="header">
<div class="title">
<h1>${H(ApplicationName)}</h1>
</div>
[...]
</div>
</div>
Turned into long, drawn out classes…
<div class="container">
<div class="span-24">
<div class="prepend-1 span-12 column">
<h1>${H(ApplicationName)}</h1>
</div>
</div>
</div>
Without the BluePrintCSS guide or the CSS files available, you couldn’t look at the classes and tell much of what was going on… and it wasn’t descriptive like ‘header’ and ‘title’.
Welcome To .less (dotless)
I stumbled onto .less (aka dotless, dotlesscss, that shizzle css thingy) back in November and thought “hey, that’s cool… that’s how CSS should work” and didn’t give it much more thought. Shortly after fav’ing it in github, I noticed they pushed an update targeting BluePrintCSS operability. Cool–I’ve GOT to try this out.
Getting Started with .less
The instructions on the home page (right side of the screen) is all you need. Clone, compile, update web.config and start go!
The Benefits
So what’s the big hype? This:
1. Import your BluePrintCSS file into your .less file (for me, it’s site.less).
@import “screen.css”;
2. Simply reference any of the BluePrintCSS class styles as part of your custom styles.
#header {
#title {
.span-10;
.column;
}
#menucontainer {
.span-14;
.column;
.last;
text-align: right;
}
}
#left-content {
.span-18;
.column;
}
#right-boxes {
.span-6;
.column;
.last;
}
When dotless’ HttpHandler hits your .less file (or your use dotless.Compiler), it translates those referenced styles into their actual CSS tags.
#header #title{width:390px;float:left;margin-right:10px;}
Nice. Plain and simple (and miraculous).
Lessons Learned
Some “lessons learned” so far:
1. Order matters. Referencing a style before you’ve ‘created’ it will bork the interperter. So @imports always go at the top and if you’re referencing within the same .less file, keep things in order.
2. Pre-compiling is fun. For now, I’m pre-compiling my .less files without using the handler and simply sending the css file up to our web server. This is easily taken care of with either a MS Build task or psake task. Here’s how an example of a quick MS Build task that references the dotless.Compiler in the solution’s “tools” directory.
$(SolutionDir)Tools\dotLess\dotless.compiler.exe -m $(ProjectDir)content\css\site.less $(ProjectDir)content\css\site.css
3. .less files need to be ‘Content’. Since VS2008 is stupid, .less files (like .spark views, etc) need to be explicitly set to have a Build Action of ‘Content’ so that the publishing process sends them up to the web server. If you’re publishing via psake or another automation tool, then ignore this. 
That’s it for now. Hit up the project site, peruse and clone the github repo, and join the discussion for .less and (finally) start applying some DRY to your CSS.
Tip: Excluding Auto-Generated Files from SourceSafe
Being an avid git user outside the workplace, I’m used to setting up .gitignore files for my ReSharper and pre-generated sludge that find their way into my projects. However, until today, I never found a clean way of handling pre-generated files with Visual SourceSafe.
Seems the answer was just in a menu that I never used (imagine that…).
Situation
For now, rather than having dotless generate my files on the fly, I’m using the compiler to compile my .less into .css files. Since I’m using the built-in publishing features (which, I am replacing with psake tasks–more on that later) for this project, any files not included in the project are skipped/not copied. That’s a bummer for my generated .css file.
The answer is to include the file in the project; however, when checked in, dotless.Compiler crashes because it can’t rewrite the file (since it’s read-only).
Solution
Exclude the file from source control. Sure, that sounds good, but how using SourceSafe?
1. Select the file, site.css in this case.
2. File > Source Control > Exclude ‘site.css’ from Source Control.
Yeah, seriously that easy. Instead of the normal lock/checkmark, a red (-) appears by the file (which is fine) and everything compiles as expected.
I’ve used SourceSafe for years now and never saw that in there… it doesn’t look like I can wildcard files or extensions like .gitignore (or even folders–the option disappears if anything but a single file is selected), but for a one-off case like this, it works just fine.
Stop using MS Paint for your demos – Get Balsamiq Mocks!
Ever had your boss walk in and want a ‘mockup’ of a product the next day? In the past, I’d whip something up either in Visio (*cough* or Paint *cough*) and give a basic sketch of how screens and flow would work.
Over the past year, I’ve read a lot of rave reviews around Balsamiq Mockups, but hadn’t had a chance to try it out.
I emailed Balsamiq to inquery how their licensing structure worked. I was interested in purchasing a personal copy (budgets are tight here at the office—cuts in public education are running deep) and curious if I could still use it for “work-related” activities. To my surprise, I had nearly an instant response thanking me for my interest and providing me a free license to use here at work (educational). That’s freaking AWESOME and greatly appreciated and further motivates me to pick up a copy to use for consulting and personal projects.
So, next comes usage. Up until now, I’ve tinkered with the ‘web demo’ version of Balsamiq. I was pleased that, on moving to the desktop version, the layout, tools, and functionality remained almost identical. No lost time or learning curve.
How It Saved The Day
Last week, I was hit up by the opening situation. “Hey, we need a demo of how you’d do this… and we need it later this afternoon to present to the laywers, {big boss A}, and {big boss B}…”
For something to hit that level, I’d usually opt to bypass the Big Chief and Crayon as well as Paint and simply mock up a UI on the web. Unfortunately, for this, the time wasn’t there; however, Balsamiq was up to the task.
Rough (very rough) specs to screens, screens transformed into flow, and added into a PowerPoint for a quick presentation, acceptance by all parties involved, and hero fanfare. We’re now a week later and using those mockups to generate our UI screens—and the customer loves how well things translate without any ‘surprises’. Excellent.
UPDATE: I just pulled down the 1.6.46 version of Balsamiq and it exports to PDF. Sweet..
Features I Love
Linked Screens – A bit of a counter to one of the ‘Things I Wish It Did’ is the ability to link mockup screens together with ‘links’. It’s great for demos to provide a true flow of how things fit together.
In this example, we’re showing a simulation of the login screen. You can see the ‘link’ icon on the Login button. In a demo, clicking the Login button takes us to our next screen—just like it would (if authenticated right ) in our app.
Sticky Notes – On a few screens, showing business logic is a bit challenging. That’s where I love sticky notes comments. I’m a huge fan of whiteboarding and sticky notes and am thrilled I can bring that into my mocks. It’s also great when exporting and being able to keep track of comments and ideas as the team is working through a mock.
Easily Populated Controls – Data grids, buttons, tabs—the common elements of a user interface and all easily populated with test data. No longer do I need to draw lines in Paint or try to copy/paste screen clips out of Excel to get a decent looking grid view—a bit of text and commas and a snazzy grid appears!
Things I Wish It Did
Master Pages – Most UI layouts have the same headers/footers. You can easily reproduce this by ‘cloning’ the current mockup; however, when the customer walks in and wants to move the logo from the left side to the right side, you now have 10… 20… 100 individual screens to move it on. I’d love to be able to designate a screen as the ‘parent’.
… wow, that’s about all I can come up with!
Comic Sans Makes Me Cry… and How to Fix it
One thing that really threw me about Mockups was the fact that it used Comic Sans. Seriously? Thankfully, I’m not the first to ask this and they’ve provided a stellar walkthrough on how to use whatever font you desire.
My configuration file looks like:
<config>
<fontFace>Segoe UI</fontFace>
<rememberWindowSize>true</rememberWindowSize>
<useCookies>true</useCookies>
</config>
I opted for Vista/Windows 7’s clean Segoe UI font. Having my mockups look like my scribbles on paper wasn’t that important to me. Professional earns more points than crayons on a Big Chief tablet.
Conclusions
I’ve found myself, since picking up Balsamiq Mockups, simply using it for everything. Sitting in planning meetings and sketching out reports, screens, even data flows. With a little creativity, you can sketch out almost anything. For the things you can’t, I’m ‘crayoning’ it using my Bamboo tablet and drawing out what I want–then importing it as an image. Sweet.
If you haven’t check out Balsamiq, give it a run–it’s an amazing tool.
Creating Local Git Repositories – Yeah, it’s that simple!
We’re in the process of evaluating several source control systems here at the office. It’s a JOYOUS experience and I mean that… sorta. Currently, we have a montage of files dumped into two SourceSafe repos.
At this point, most of you are either laughing, crying, or have closed your web browser so you don’t get caught reading something with the word ‘SourceSafe’ in it. That’s cool, I get that.
As part of my demonstrations (for the various toolings we’re looking at), I wanted to show how simple it was to create a local GIT repository.
Since I’m discussing ‘local’, I’m taking a lot of things out of the picture.
-
Authentication/authorization is handled by Windows file permissions/Active Directory. If you can touch the repo and read it, then knock yourself out.
-
Everything is handled through shares, no HTTP or that goodness. Users are used to hitting a mapped drive for SourceSafe repos, we’re just emulating that.
So what do we need to do to create a Git repository? Three easy steps (commands).
1. Create your directory with the .git extention.
mkdir example.git
2. Change into that new directory.
cd example.git
3. Initialize the repository using Git.
Our new Git repository is now initialized. You can even see that PowerShell picks up on the new repository and has changed my prompt accordingly.
Now that we have example.git as our ‘remote’ repository, we’re ready to make a local clone.
git clone H:\example.git example
Now we have an empty clone (and it’s kind enough to tell us).
All of our Git goodness is packed into the standard .git directory.
To test things out, let’s create a quick file, add it to our repo, and then commit it in.
First, let’s create/append an example file in our clone.
(note: this is called ‘so lazy, I don’t even want to open NotePad’)
Now, we can add and verify that our newly added ‘example.text’ file shows up:
Finally, commit…
git commit -a -m “this is our first commit”
… and push!
git push origin master
The last step is to ensure that our new ‘remote’ repository can be reproduced. Here’s a quick single line to clone our repository into another new folder, list out the contents, and verify the log has the commit we made in the first repo.
It’s amazing how quick and easy it is to setup local Git repositories. From here, we can look at the file system/Windows for authentication/authorization and focus on our migration AWAY from Visual SourceSafe.
PowerShell: Recreating SQL*Plus ‘ed’ Command
Aside from PowerShell, I spend a bit of time day-to-day in Oracle’s SQL*Plus. I’m slowly replacing my dependency on SQL*Plus with PowerShell scripts, functions, and such, but there are some core things that it’s just easier to bring up a console and hack out.
One thing I really love about SQL*Plus is the ‘ed’ command. The ‘ed’ command dumps your last statement into a temp file, then opens it in Notepad. Make your changes in Notepad, save, and rerun (in SQL*Plus, it’s a single slash and then Enter), and the frustration of lengthy command line editing is solved.
So, this is a work in progress—my attempt to simulate the same process in PowerShell.
Example Situation
Let’s say, for example, we have a long command we have been hacking away at, but are getting tired of arrowing back and forth on:
$count = 0; sq mydb “select id from reports” | % { $count++ }; $count
(Yes, yes, it’s not ‘very long’, but that’s why this is called an example!)
So, a simple row counter.
The ‘ed’ Command
To recreate the ‘ed’ experience, I need two things:
- Access to the command line history via get-history.
- A temporary file.
touch c:\temp.tmp
get-history | ?{ $_.commandline -ne “ex” } |
? { $_.commandline -ne “ed” } |
select commandline -last 1 |
%{ $_.CommandLine} > c:\temp.tmp
np c:\temp.tmp
Stepping through the code:
- Create a temporary file. I used c:\temp.tmp, but you could get a bit fancier and use the systems $TEMP directory or something.
- Get-History. This returns an ascending list of your command history in PowerShell by ID and CommandLine. We want to ignore any calls to our ‘ed’ or ‘ex’ functions and grab the “last 1”—then insert it into our temp file.
- Finally, open up the temp file in NotePad (np is an alias on my PowerShell profile for NotePad2).
So now that I have my ed command, let’s try it out after I’ve already ran my simple row counter.
Cool. We can now make changes to this and SAVE the temp file. The next step would be to jump back to PowerShell and execute the updated command.
The ‘ex’ Command
Since the slash has special meaning in a PowerShell window, I simply named this script ‘ex’ instead. Ex… execute… surely I can’t forget that, right?
get-content c:\temp.tmp |
% {
$_
invoke-expression $_
}
The ‘ex’ command is pretty simple.
-
Get the content of our temporary file from get-content.
-
For each line (since get-content returns an array of lines), print out the line as text and then invoke (run) it using invoke-expression.
Invoke-Expression takes a string as a command (from our text file) and returns the results. For more information on Invoke-Expression, check out MSDN.
Wrapping It Up
Now that we have our two commands—ed and ex—we’re ready for those “oh, this is short and easy” commands that turn into multi-page monsters—without fighting tabs and arrow keys.
As I said, this is a work-in-progress. I’ll update the post as new/better ideas come up.
PowerShell : TGIF Reminder
Jeff Hicks’ TGIF post yesterday was a fun way to learn a bit of PowerShell (casting, working with dates, etc) and also add something valuable to your professional toolbelt—knowing when to go home.
I tweaked the date output a bit to be more human readable, but also moved it from just a function to part of my UI. I mean, I should ALWAYS know how close I am to quittin’ time, right? Especially as we joke around our office during our ‘payless weeks’.
# Determines if today is the end of friday! (fun function)
function get-tgif {
$now=Get-Date
# If it’s Saturday or Sunday then Stop! It’s the weekend!
if ([int]$now.DayOfWeek -eq 6 -or [int]$now.DayOfWeek -eq 7)
{
”Weekend!”
}
else {
# Determine when the next quittin’ time is…
[datetime]$TGIF=”{0:MM/dd/yyyy} 4:30:00 PM” -f `
(($now.AddDays( 5 – [int]$now.DayOfWeek)) )
# Must be Friday, but after quittin’ time, GO HOME!
if ((get-date) -ge $TGIF) {
”TGIF has started without you!”
}
else {
# Awww, still at work–how long until
# it’s time to go to the pub?
$diff = $($tgif – (get-date))
”TGIF: $($diff.Days)d $($diff.Hours)h $($diff.Minutes)m”
}
}
}
NOTE: My “end time” is set to 4:30PM, not 5:00PM—since that’s when I escape. Change as necessary.
The code comments explain most of it. As you can see, I added in one more check—let me know when it’s simply the weekend. I also removed the Write-Host calls, since I simply want to return a String from the function. I could use the function, as necessary, with formatting, and add it to other scripts and such. For example:
Write-Host $(get-tgif) -fore red
The next step was tapping into the $Host variable. Since I use Console2, my PowerShell window is a tab rather than the whole window. Console2 is aware of PowerShell’s $Host.UI methods and adheres to the changes.
To add get-tgif to my prompt’s window title:
$windowTitle = “(” + $(get-tgif) + “) “
$host.ui.rawui.WindowTitle = $windowTitle
Easy enough. Now my window title looks like (ignore the path in there for now):
But that only sets it when you log in… and I want to update it (and keep that path updated as I traverse through directories). To do that add a function called ‘prompt’ to your PowerShell Profile. Prompt is processed every time the “PS>” is generated and allows you a great deal of customization. See the post here for further details on how I’ve customized my prompt to handle Git repositories.
So, move those two lines into our prompt function, and our TGIF timer now updates every time our prompt changes… keeping it pretty close to real time as you work.
function prompt {
$windowTitle = “(” + $(get-tgif) + “) ” + $(get-location).ToString()
$host.ui.rawui.WindowTitle = $windowTitle[…]
}
This could be tweaked to any type of countdown. I’m sure a few of those around the office would have fun adding retirement countdowns, etc.
Happy TGIF!
