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:

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

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.