Monthly Archives: March 2017

SQL Saturday Richmond (#610)

On Saturday, March 18, 2017, I spoke at my very first SQL Saturday.  I have been an attendee, involved in organizing and volunteered at many over the years, but this was the very first time I was a speaker.  My session, What is Power BI?

I have presented this session twice before, once to my local user group and once at the Triad Developers Conference, so I was fairly comfortable with my content.  Richmond had 245 attendees registered with 5 different session tracks.  I had about 25 people in my session.  Of those 25, I only saw one nod off, but it was the first session of the day (8:30am), so I’m going to chalk that one up to not enough caffeine prior to the session.  There were some great questions and I had several people approach after the session with more detailed questions and to tell me how much they enjoyed my session.  Some were so excited they would be able to take action when they got back to work on Monday based on my session.  It really doesn’t get any better than that.

With my session in the rear view, I was excited to attend other sessions.  I was able to make it into two other sessions, both of which were fabulous.  The organizing team for SQL Saturday Richmond did a fabulous job.  The event appeared to run like clockwork, I think maybe they’ve done this before Winking smile

One thing I really liked about this event is that they did not have a typical speaker dinner and gift, they did a speaker event.  It was at G-Force Karts which was so exciting for me because I’d never driven a go kart before.  I’ve always fancied myself a race car driver in another life (much to Martin’s dismay) so this was my opportunity to see if it was true.  All I can say is, “Yes, it’s true.”  I had so much fun.  I wish more SQL Saturday organizers would consider doing something like this.  A nice dinner is always appreciated and a gift is a thoughtful gesture, but the memories I made with my #SQLFamily at G-Force Karts are something I will NEVER forget.

I just want to thank the organizers of SQL Saturday Richmond, all the volunteers, sponsors and spouses who made this event happen.  It was truly amazing and something I will remember my entire life.  Well done.

SQLSatRichmondGoKart

Photo courtesy of Doug Purnell (Blog |Twitter)

Check Those Settings

Recently, I was tasked with “enhancing” a third party application.  This third party application (TPA) outputs a bunch of files to a file share in a way that makes sense to the application, but makes no sense to a human. 

The Task

Make a copy of these files in a new location that makes sense to humans.

The Rules of Engagement

  • Do not modify any of the existing files or file structures created by the TPA.
  • Do not modify any of the TPA database objects.
  • Do not add any objects to the TPA database.

First thing that popped into my head was, “I can do that in PowerShell in less than 5 minutes.”  Kind of like the old game show Name That Tune, my confidence level was high.  Little did I know what was in store for me.  Less than 5 minutes turned into more than 5 hours.

In order to make the files and file structure make sense to a human, I had to use a stored procedure from the TPA to decode some bits.  Easy enough in PowerShell, just use my favorite command-let from dbatools.io, invoke-sqlcmd2.  But wait, one minor detail, I am not allowed to install any snap-ins or any other tools on the server where this would run.  In fact, I can’t even use the latest version of PowerShell, I am stuck with using PowerShell 2.0 <sigh>.

After I dusted off my PowerShell 2.0 documentation, I got my script written and started testing.  I processed several folders and their files before I received the following error while running my PowerShell script:

Invoke-Sqlcmd : String or binary data would be truncated.
The statement has been terminated.
At line:127 char:36
+ … MyResults = Invoke-Sqlcmd -ServerInstance $ServerName `
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Interesting.  I added some Write-Host statements for troubleshooting and found the offending entry.  Like any good programmer, I tested my stored procedure call in SQL Server Management Studio (SSMS) to make sure it really was a SQL Server error and guess what?  It worked just fine!  No errors what so ever.  WTH?!  This is where my tunnel vision sets in.  If it works in SSMS but not in PowerShell, then PowerShell must be the problem, right?  Well, sort of.

After repeatedly running the same piece of code and expecting different results (yes, like I said, tunnel vision), I threw my hands up and quit for the day.  I had restless dreams that night.  I was being chased by a giant SQLString Truncator (a very rare dinosaur from esoteric era).  I woke with a start at 4:30am, I had to be missing something.  All I can say is, thank goodness for Twitter and #SQLHelp.  I tweeted my issue and got immediate responses from some very smart folks, but nothing that resolved my issue, until I read between the lines of a tweet from Robert Davis:

SQLSolderTweet

That’s when the light bulb went on and Robert sent his follow up tweet:

SQLSolderTweet2

I copied all my settings from SSMS and added them to my PowerShell script.  One by one I commented them out until I was left with just one.  Low and behold that SQLString Truncator was really one of those pesky ARITHABORT Biters.

Lessons Learned

  • As soon as that tunnel vision kicks in, you need to stop what you are doing and take a break.
  • Ask for help, don’t keep beating your head against the wall.
  • Most importantly, don’t forget about your settings.  They can make all the difference in the world.
  • ARITHABORT Biters are much harder to catch in the wild than SQLString Truncators.

Triad Developers Conference – My Debut

I did it!  I did my first “real world” presentation this morning at the Triad Developers Conference in Winston-Salem.  What I mean by “real world” is not a PASS audience.  These were total strangers off the street that I didn’t know, well, there were some familiar faces and even a friend or two, but for the most part total and complete strangers that had varying backgrounds, not all technical in nature.

The feedback I received was very positive and even helpful, so I can make this presentation even better when I present it in Richmond, VA next weekend at SQL Saturday #610.

Huge thank you goes out to the organizers, volunteers and sponsors who made this event happen.  And a special thank you goes out to Doug Purnell (Blog | Twitter) for recommending me in the first place.