Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Executing PowerShell in SQL Server Redux

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)

Comments

Posted by robster on 22 March 2013

Great suff.  Can you use SQL jobs, step type: powershell, and simply execute  ps script writing to table?

Leave a Comment

Please register or log in to leave a comment.