http://www.sqlservercentral.com/blogs/chadmiller/2010/11/30/executing-powershell-in-sql-server-redux/

Printed 2014/09/17 03:41PM

Executing PowerShell in SQL Server Redux

By Chad Miller, 2010/11/30

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)

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.