T-SQL Tuesday #21 – A Day Late and Totally Full of It #tsql2sday

T-SQL Tuesday

Adam Machanic is taking his bouncing baby boy out for a night on the town, figuratively speaking.  He is not only the father of this blog party creature but he is also the host this month which happens to be its 21st birthday.  Please click on the T-SQL Tuesday image above (the graffiti art is his too) for information about the topic this month.  Also check out his page A Year of Tuesdays: T-SQL Tuesday Meta-Roundup.

CrapWe all do it.  We write crappy T-SQL scripts from time to time but rarely on purpose.  The reasons range from lack of knowledge to lack of vision and are often explained as, “It made sense at the time”.  The code somehow passed the initial sniff test but now it stinks and you realize something needs to be done.  The included T-SQL script is semi-fictitious in that although it was never run as a script each shameful piece existed in some script which was.



If you happened to glace down at the crap below then (welcome back and) you probably noticed several “undocumented” procedures.  We will call those problem Number-Two.  If you familiar with the term “undocumented” then you may also know that Kalen Delany defines it as, Undocumented means unsupported and not guaranteed.  Whether or not you can actually find the object referenced in Microsoft documentation is not determining factor.  Using something undocumented is risky because the object is not guaranteed to be accurate, to be maintained or to be available.  Now armed with knowledge of the term undocumented by adding knowledge of the Undocumented SQL Server 2000 functions we can start to polish this turd.



Ready for Number-One?  The bigger concern with the T-SQL script is that it is using extended procedures.  I have no interest in starting a flame war on the validity of extended procedures (or CLR).  In accessing this script, my oversimplified opinion is that code needing access outside of the database engine is an application component and should therefore not reside within the data layer.  Using that as the measuring rule, I would have to strike my own knuckles 6 times for the script below.


Second Take

In a second take of the same logic from the T-SQL script, here is noticeably improved PowerShell script.

Write-Host "DebugPreference=$DebugPreference";  $DumpDirectoryList = @(); $DumpDriveList = @(); $SourceFolder = ""; $DestinationFolder = ""; $FileMatch = "SQLDump*"; $ComputerManagementNamespace = Get-WmiObject -namespace "root\Microsoft\SqlServer" -query "SELECT Name FROM __Namespace WHERE Name LIKE 'ComputerManagement%'"; $ComputerManagementNamespace = $ComputerManagementNamespace.Name;  $DumpDirectoryList += ` ( Get-WmiObject -namespace "root\Microsoft\SqlServer\$ComputerManagementNamespace" -query "SELECT PropertyStrValue FROM SqlServiceAdvancedProperty WHERE PropertyName = 'DUMPDIR'" ` | Sort-Object -property "PropertyStrValue" -unique ` | Select-Object -property "PropertyStrValue" ` | ForEach-Object {$_.PropertyStrValue} ` ); Write-Debug -message "DumpDirectoryList=$DumpDirectoryList";  $RoomiestDrive = ` ( Get-WmiObject -namespace "root\cimv2" -class "win32_logicaldisk" ` | Sort-Object -property "FreeSpace" -descending ` | Select-Object -property "DeviceID" -first 1 ` ); $RoomiestDrive = $RoomiestDrive.DeviceID.Substring(0,1) Write-Debug -message "RoomiestDrive=$RoomiestDrive";  $DumpDirectoryList ` | Where-Object {$_.Substring(0,1) -ne $RoomiestDrive} ` | ForEach-Object ` { $SourceFolder = $_; Write-Debug -message "SourceFolder=$SourceFolder"; $DestinationFolder = "$RoomiestDrive$($_.Substring(1,($_.Length - 1)))"; Write-Debug -message "DestinationFolder=$DestinationFolder"; if (!(Test-Path -path $DestinationFolder -pathtype "Container")) { Write-Debug -message "Creating $DestinationFolder"; New-Item -path $DestinationFolder -itemtype "Container"; } Write-Debug -message "FROM $SourceFolder$FileMatch TO $DestinationFolder"; Move-Item -path "$SourceFolder$FileMatch" -destination $DestinationFolder; };


You probably already celebrate your code improvements.  If you also want a reason to celabrate your code mistakes then take a look at the suggestions of Andy Warren in Celebrating Mistakes-Part 1 and Celebrating Mistakes-Part 2.  Celebrating helps to cement the lesson learned, it takes the counterproductive sting out of failure and it acknowledges you have room for improvement.  You should of course use good judgment when celebrating mistakes.



Adam Machanic

Kalen Delaney

Sathish Cg

Andy Warren



declare @k nvarchar(4000)  

declare @o nvarchar(4000)  

select @k = ‘SOFTWARE\Microsoft\Microsoft SQL Server\’+upper(‘mssql’+replace(replace(left(convert(varchar(128),serverproperty(‘ProductVersion’)),charindex(‘.’,convert(varchar(128),serverproperty(‘ProductVersion’)),charindex(‘.’,convert(varchar(128),serverproperty(‘ProductVersion’)))+1)-1),‘.’,‘_’)+‘.’,‘_0.’,‘.’)+isnull(convert(varchar(128),serverproperty(‘InstanceName’)),‘mssqlserver’))+‘\CPE’  

exec xp_regread ‘HKEY_LOCAL_MACHINE’,@k,‘ErrorDumpDir’,@o out  

declare @c nvarchar(4000) 

select @c=replace(‘dir “‘+@o+‘\sqldump*”‘,‘\\’,‘\’)

exec xp_cmdshell @c

if @@rowcount < 8 goto quitter

declare @d table (char(1), s int)

insert @d exec xp_fixeddrives

if (select top 1 d from @d order by s desc) <> (select convert(char(1),@o))


declare @n nvarchar(4000)

set rowcount 1

select @n=d+right(@o,len(@o)-1) from @d order by s desc

set rowcount 0

declare @p table (int, d int, p int)

insert @p

exec xp_fileexist @n

select @c=‘mkdir “‘+@n+‘”‘

if not exists (select null from @p where d=1)

exec xp_cmdshell @c

select @c=‘move “‘+@o+‘\sqldump*” “‘+@n+‘”‘

exec xp_cmdshell @c



print ‘no’


21 Tribute


About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.