Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I was modifying my PowerShell (PoSh) script to download SQL Saturday files recently to not re-download files. However when I did this, I also realized that I didn’t necessarily want the script to run too long.

One of the challenges of downloading the data is that I don’t know how many events exist. We don’t keep that number handy, and it changes regularly. One of the things I decided to do was run my process in a loop.

While ($i -lt 9999) {

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I decided to use 12 as my number of missed. No good reason, but that’s what I picked. I started by putting a variable outside of the loop.

$missedXML = 0

While ($i -lt 9999) {

Then I increment this variable in the CATCH section of my error handler.

Catch 
{ 
  # if we can't load the file, assume we're done for now. 
  $missedXML++ 
  Write-Host "error with  #" $i 
}

Finally, I set up an IF loop at the bottom of the loop. If I’ve missed 12 times, I break the loop by setting the counter to the last value.

$i = $i + 1

if ($missedXML -ge 12) { 
 $i = 9999 
}

I tested this with some debugging information and what I found was that when I got to 494, I started missing files. As soon as I hit 12, the loop ended.

Enhancement complete.

SQLNewBlogger

This post came about when I started working on the script. I made the modifications from the previous post and decided to also fix the extra loops with this technique.

This post took about 10 minutes to write.

References

No resources needed here. I’ve got enough PoSh knowledge to handle this task myself.

Filed under: Blog Tagged: powershell, SQLNewBlogger, syndicated