Voice of the DBA

Tag: syndicated

  • Ending My Loop in PowerShell Early

    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.

  • Changing Scales and Creating Disappointment

    I got an email recently that notified me that session feedback from the Summit was available for my talk. I’d had a lot of people in the room, and was curious how things went. I think the session was OK, a little off on time, a few too many questions I tried to answer, and perhaps a bumpy flow.

    However when I got my scores, I had a 2.85 for the session overall, with various aspects of the talk being rated from 2.5 to 2.9.

    Well, I sucked.

    That was my first thought. I’ve been getting rated, and evaluating speakers on a 5 point scale for quite a few years at PASS events. I was surprised, and disappointed, and then a bit embarrassed that I hadn’t delivered a good talk at the Summit. Since I hadn’t delivered that talk in public anywhere prior to the conference, I thought I had made a big mistake. Apparently my practice that week in my hotel room had been for naught.

    However then I saw this note in another email: One of the changes this year was to move from a five point rating scale to a three-point scale.

    Hmmm, I missed that in my email somewhere, and didn’t notice this as I filled out a couple of session evaluations.

    I don’t think there’s anything wrong with changing the scale. Personally I like the 3 point scale, but it wasn’t a change I noticed. The first communication with speaker feedback didn’t mention this.

    Scale matters. Many of us know that by manipulating scales, we can make data look different, We can prove a point that might not be supported by a different presentation of the data on another scale.

    Our clients and business users come to know and expect the various ways we present data. They will start to internalize scales and interpret data based on their expectations. We can change scales, but we need to make it clear and visible that we have changed scales.

    Personally I would have appreciated the results being reported as:

    Overall Session Score: 2.85/3.00

    instead of

    Overall Session Score: 2.85

    That little extra information can mean a lot. Keep this in mind as you make fundamental changes to the way you present data.

  • Python Command Line Calls

    There was a time I worked in a company any we lived in the command line. This was in the early 90s, prior to Windows v3.1 being released and we primarily used DOS on a Novell network.

    We also had paper phone books on every desk for the 1,000+ people on the property. However, as you might guess, the phone books went out of date, and were only updated a couple times a year. We might get a new sheet, but mostly people learned to cross out a name and write a new extension in for the people they dealt with regularly.

    However updates to the master list happened regularly, every few days. These were done in a small Lotus 1-2-3 file that an administrative assistant maintained. As a network person, I knew where this lived, and arranged a text export of this every night with a scheduled task.

    Why text? Well, I’d come from a university where we had our phone details in a text file and would grep the file to find information. In DOS, I knew we could do the same thing with FIND. However, rather than write the find command with parameters, I put a batch file on our global share that called FIND with the parameters needed and the path to the phone book. I called this 411.bat. When I needed a phone number, I could type

    411 “Andy Warren”

    I’d get Andy’s name, location, and phone number back. It was a trivial piece of programming for me, but the rest of the network team, all non-programmers, were thrilled. I even added a /? check to return help information to the user.

    With my playing with Python last week, I decided to do this for myself as well. I took my Python program to send tweets and changed it to send a tweet when the program was called, and to send the parameter as the tweet. The code looked like this::

    import sys
import tweepy
def send_a_tweet(tweettext):
consumer_key = "X1GWqgKpPP4OuqWJZ7hw6"
consumer_secret = "QW3EkMHlyzF69AHn8DjyWyRG5CAQ0wjK9RqUZ2"
access_token = '146009-MqpeZFslj0VGgTSik1fq5klvJpqc1x6HAsiu'
access_token_secret = '8wIZlPbIqXtczc2LOsJgjMP3dCRRw5ajMvkjEspF'

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)

api = tweepy.API(auth)

api.update_status(status=tweettext)

if __name__ == '__main__':
send_a_tweet(sys.argv[1])

    I placed this in a “\tools” folder that I have in my path. I also added a “tweet.cmd” file in this folder with this code:

    python c:\tools\send_tweet.py %1

    Since Python.exe is in my path as well, I can do this:

    2015-11-06 15_25_19-Command Prompt

    And I’ll see this on my timeline. I guess you’ll all be able to see this as well.

    2015-11-06 15_25_26-Steve Jones (@way0utwest) _ Twitter

     

    Why bother? Well, it was partially playing around. As I have been learning Python, I have mostly been playing in an IDE, solving small problems, but not really doing things useful. I also like the idea of command line tools, since I find them quick. Tweetdeck is big and bloated, and if I wanted to send a tweet from my desk, this is a quick way to do it. I could do a “readtweets” as well, and may.

    However I also learned how to call Python programs with a command line, which is a good step to starting to build more useful programs that I can customize. This is also the start of me being able to schedule a program, and perhaps build more automation into my life with Python.

    Mostly, however, it was just fun.

  • Test if a File Exists with Powershell

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

    Awhile back I wrote a PowerShell script (PoSh) to download the SQL Saturday XML files used for the Guidebook application. These contain a bunch of the information that I wanted to collect, transform, and keep around.

    However the script wasn’t great. It basically looped a set number of times and re-downloaded the files. Not the more efficient solution, especially if I want this to run regularly.

    One of the enhancements I wanted to make was check if the file exists, and if not, then download it. However, if it does exist, then I’ll skip the file. I know this means I don’t get updated files if schedules change, which is possible, but in that case, I can just delete the file from my desktop and go from there.

    I made a quick search, and found a few links to the Test-Path cmdlet. Essentially you give this a file path and it returns true or false. Almost exactly what I need. This is what I added to my code:

    if (Test-Path $DestinationFile) {

    #do something

    }

    However I want to take action if the file doesn’t exist. In most languages, I’d add a ! in front to signify "not". However that doesn’t work in PoSh, just like > doesn’t mean greater than.

    Another search showed me the -NOT operator. That’s what I need, but I can’t do this:

    if (-NOT Test-Path $DestinationFile) {

    #do something

    }

    Instead, I need to have a single expression for -NOT, which means more parenthesis. Not a big deal. I used this code:

    if (-NOT (Test-Path $DestinationFile)) {

    #do something

    }

    That worked great and now I only download the files I need. If I want to re-download (for newer events), I just delete those files and re-run the script.

    SQLNewBlogger

    This post came about when I started working on the script. It actually took longer to write this than find the solution, add it to code, and test it. That process was about 5 minutes.

    This post took about 10 minutes to write. I also had a second post based on similar modifications to the script, so I’m did that in another 5 minutes.

    References

    A few of the links I used: