Voice of the DBA

Category: Blog

Powershell Quick Parameters for Scripts
I was working on a script recently to manage a particular process and wanted to make it generic by allowing the user to pass in a parameter. I have seen lots of examples, especially those that work with SQL Servers, using text files and other items as parameters, but in this case I wanted an easy, quick, command like parameter.

This post looks at what I chose to check parameters. I had a couple requirements.
- display message if no parameter is passed in.
- display some help if /? is passed in.
I know that my cmdlets should contain help from the PoSh command line, and I’ll get to that. For now, I’m managing things the way I was taught when I wrote C. A /? should get me help.

$Args

I did a little research on parameters and found a few things, but decided to use the $args variable. This is an array of undeclared parameters. I grab the first value (the only one I care about like this.

$instance = $args[0]

Note the [0]. As with many things in Computer Science, we’re zero based arrays.

I could allow for other parameters, but this gets me what I want.

Testing

The test for /? is easy. That’s like this:

if ($instance -eq "/?") {
write-host "Please enter the instance you wish to detach all databases from as a parameter."
}

If this is equal to my help request, write something out.

Next I needed to add another test. In this case I found that I could easily look for NULL variables, or blanks, with the !. As in this:

if (!$instance -or $instance -eq "/?") {
write-host "Please enter the instance you wish to detach all databases from."
}

That worked well and lets me remind myself if I’ve forgotten to pass in a parameter. The one thing I experimented a few times with was the OR clause. I tried these, none of which worked:
- if (!$instance OR $instance -eq "/?") {
- if (!$instance) or ($instance -eq "/?") {
  if (!$instance) -or ($instance -eq "/?") {
A little experimenting got me to remember that PoSh is fairly consistent, and the plain -or should work inside the parenthesis.

Everything Else

When I first ran this without a parameter, my script froze. That’s because I hit the IF clause, wrote out the message, and then executed.

Fortunately I’ve done this type of stupid programming before, so I added this:

if (!$instance -or $instance -eq "/?") {
write-host "Please enter the instance you wish to detach all databases from."
}
else {

The rest of my script fits in the else clause.

Reference
- Automatic Variables – https://technet.microsoft.com/en-us/library/hh847768.aspx
- https://rkeithhill.wordpress.com/2007/01/06/checking-for-a-null-or-empty-string/
February 19, 2015
Parsing SQL Saturday Data – Looping Through And Loading All XML Files
After my last post on parsing the XML, I decided to continue forward and get ready to put the data in a database. For that, I’m really looking for this data:
- event ID
- session title
With this, I can easily insert data into a table. I’ll have separate tables for the events themselves and the speakers, but for now, I can easily showcase the titles of the sessions.

With that in mind, I decided to start expanding my efforts and building a series of loops that get all the data from the XML documents.

Looping through all files

The first thing I needed to do was loop through all the files I’d downloaded and get the documents loaded. I decided to use a DO loop for this, since I should be doing this at least once each time. Eventually I’ll add logic to avoid downloading files I’ve downloaded already.

Here’s the basic code:

$loop = 1

$loopend = 450
$doc = New-Object System.Xml.XmlDocument

do {
#start large loop

# get the filename
$sourceURL = $baseURL + $i + ".xml"

# do other stuff

$i++

#end outer loop
} while ($i -lt $loopend)

This is the basis for looping through all the file names, based on my downloads. A quick test shows this is building all the filenames I need.

Loading files

The next step is to actually load each XML file in and start querying it. I changed from the parsing code to use a loop since I’ll need to insert each item separately and I don’t think the code I had from the previous article will work. At least, I haven’t found a way.

If you know of one, let me know.

I used the Test-Path method to be sure that the XML exists, as there was at least one lost event in my initial download. I think that’s fixed now, but in any case, I added this code:

#test the path first. If it exists, load the XML
if (Test-Path $sourceURL) {
$doc.Load($sourceURL

#trap the event number. This will be the ID I use in the database table.
$event = "SQL Saturday #" + $i

That seems to work fine, and with with $event variable, I know which event the sessions are associated with.

Next Steps

That’s all I wanted to put here, giving me a nice, simple way of going through a series of files in a pattern. From here I’ll add more detail to the inner loop that gets the session titles out of the XML document and displays it.
February 18, 2015
The Vacation Conundrum

Would I want unlimited time off? It’s an interesting question. I think I might like something more like a minimum time off, or perhaps an allowance, with the tracking and offer to work with employees that need more. There are problems with unlimited vacation, and it’s often because of poor management and social pressure of colleagues.

Or, of course, workaholic syndrome, which is common in the technology industry.

Really, I’ve never had an issue with technology, personally. I work mostly the stated hours, but because I’m productive, I never have had problems with managers if I come late or leave early. To be fair, that’s my experience, and if everyone acted as I did, perhaps that would be problematic.

At Red Gate, I’ve been able to take days off for something, usually skiing, as I want. Actually, I usually only take a partial day off, and have to work part of the day to be sure the SQLServerCentral newsletter is prepped and going out. Or answering email, dealing with site issues, etc. I’ve even taken meetings on the road, or halfway to the mountains, pulling over to chat.

The last few years I’ve not wanted to deal with that and typically book a day off in our HR system and plan to be away. If things break, I ignore them. Or someone else needs to be ready to handle them since I’ve taken vacation and I’m not going to be pushed into working when I have booked time off.

The downside of this, which is more specific to my job, is that I have daily things that need handling, like the SSC site. As a result, if I take a day off, that usually means I’m putting in more time before the holiday to prep things. I’ve never done that as a DBA or developer. I had deadlines, but I didn’t work extra because of vacation, unless I had put myself behind.

If someone else sends an unrealistic schedule, I’m not killing myself, or skipping vacation.

It’s a tough question, and I need to think more about it. The pieces linked above are interesting and worth reading.

February 17, 2015
Desktop Repairs – Power Supply Issues

My desktop didn’t restart after vacation a few weeks ago. That wasn’t what I wanted to have happen with a lot of work to get done before some travel. Needless to say I wasn’t thrilled when it happened.

I was worried that something major had died. However I’d replaced the main boot drive recently and I had the old one. If I could get power.

I emailed Glenn Berry, who’s my go to hardware consultant. He suggested I check the power supply since I couldn’t get anything to happen. On his advice, I went to the local Microcenter and bought a new 650W power supply. Here’s the old one, with a stock CPU cooler. I bought a new one of those as well, since I find the heat alarm going off during some video editing.

I didn’t remove the old power supply at first. I actually disconnected it and connected the new one to the motherboard from outside the case and powered things on. Sure enough, everything came on. Lights, drives, etc.

I then powered things down again and set about replacing the power supply. It’s actually easy, and I’m surprised I’ve never had to do this before in my life. Probably because I’ve tended to replace machines so often.

In any case, it was a matter of removing 4 or 5 screws from the back of the case, carefully slipping out the old PS and then putting the new one in there.

The CPU cooler was trickier. The tabs push through the motherboard and hook, but they are somewhat fragile. I was worried, and sure enough, the first time they weren’t tight. The cooler needs to be held tight to the CPU to pull off heat. It was loose and I got heat alarms once I started using the machine.

However I kept messing with it, slowly trying to get the tabs in there and eventually they are holding fairly tight. I still get heat alarms during heavy duty video processing, so I think this cheap $25 cooler isn’t good enough. Or I don’t have enough thermal grease. Either way, I’ll probably replace this soon.

Here’s everything together.

And a happy Steve.

February 13, 2015