TIL - Nov 25 2016, xp_dirtree appends null bytes to filenames in SQL 2000

Nov 25, 09:13 AM

I was performing some final minor fixes on Find-DbaOrphanFile (helps cleanup files unattached to your server in default directories and additional folders you specify) and ran into an issue parsing the filenames returned.

In the process of changing the code to fix some other issues, IO.Path.GetFileName was added to properly enumerate the paths returned, and when testing it in the dbatools lab (Thanks @ctrlb!!!) it was failing on SQL 2000.

After a few attempts at parsing out slashes and various .trim commands I broke out the command in the PowerShell debugger (which I will detail in another post) and I was thinking of converting everything to numeric representations to double check their character values, but there was no need, the console had done the work for me:

The Find-DbaOrphanedFile (now with an additonal ltrim and rtrim in SQL instead) command now works!

As a little aside, why even test SQL 2000?

One of the first things dbatools strove to do was migrate effectively between different versions of SQL Server, and we want to support SQL 2000 up to the most recent SQL version if possible, we do this because we want to offer the cool tools as far back as possible, we know that many people will be using old versions for many years to come.

It hurts sometimes and some of the code can get a bit hacky, but I think most of SQL 2000’s differences are interesting and long term forcing ourselves to manage different versions of SQL Server is reducing the into adding new and fundamentally different databases, such as all the interesting things that are coming with vNext.

And as always, this is one of the many dangers of using undocumented stored procedures :)

Constantine Kokkinos