A while ago I blogged about using xp_cmdshell to execute a PowerShell script in SQL Server and return a result set. At that time I was using PowerShell V1, but now with PowerShell V2 I can clean this up a little. The improved version uses the built-in PowerShell V2 cmdlet ConvertTo-XML with the –AsString parameter. Because SQL Server understands XML we can parse the XML using XQuery. Keep in mind this is still hacky and just as I mentioned last time its far better to execute T-SQL in PowerShell rather than use or more accurately misuse xp_cmdshell. Anyways here’s a improved version:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- To allow advanced options to be changed. EXEC SP_CONFIGURE 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC SP_CONFIGURE 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO /* #COPY disk.ps1 to Public folder param ( [string]$ComputerName = "." ) Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" | foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd")) add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2)) add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2)) add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } | Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree */ CREATE TABLE #output (line VARCHAR(255)) INSERT #output EXEC xp_cmdshell 'powershell -Command "C:\Users\Public\disk.ps1 | ConvertTo-Xml -NoTypeInformation -As string"' DELETE #output WHERE line IS NULL DECLARE @doc VARCHAR(MAX) SET @doc = '' DECLARE @line VARCHAR(255) DECLARE xml_cursor CURSOR FOR SELECT line FROM #output OPEN xml_cursor FETCH NEXT FROM xml_cursor INTO @line WHILE @@FETCH_STATUS = 0 BEGIN SET @doc = @doc + @line FETCH NEXT FROM xml_cursor INTO @line END CLOSE xml_cursor DEALLOCATE xml_cursor DROP TABLE #output SELECT item.REF.VALUE('(Property/text())[1]', 'datetime') AS UsageDT ,item.REF.VALUE('(Property/text())[2]', 'nvarchar(128)') AS SystemName ,item.REF.VALUE('(Property/text())[3]', 'nvarchar(128)') AS DeviceID ,item.REF.VALUE('(Property/text())[4]', 'nvarchar(128)') AS VolumeName ,item.REF.VALUE('(Property/text())[5]', 'nvarchar(128)') AS SizeGB ,item.REF.VALUE('(Property/text())[6]', 'nvarchar(128)') AS FreeGB ,item.REF.VALUE('(Property/text())[7]', 'nvarchar(128)') AS PercentFree FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml) CROSS APPLY feedXml.nodes('/Objects/Object') AS item(REF) |
