Bulk Insert WMIC output to table w/ xp_cmdshell

/*
Description:
This is an example of retrieving WMIC results using the xp_cmdshell proc, 
and inserting the results into a table.

Specifically, this script retrieves disk info from the LogicalDisk class, 
but WMIC provides thousands of other properties.  WMIC does require it be 
ran from a Windows 2003 server, but can query other Windows 2000 servers 
when executed from a W2003 server.

NOTE: If WMIC hasn't been run on the server before, it'll do a compile on 
its first run.  I recommend doing a manual execution of WMIC first before 
using this script, so you don't insert an extraneous output into the temp 
table

This example lists the available alias' on the system  
(e.g. WMIC alias list brief)

WMIC Reference:
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/wmic_overview.mspx?mfr=true


Author: Thuan Trang (7/2/2006)
*/
set nocount on

-- ** Create temporary tables
if object_id('tempdb.dbo.#tbl_cmdoutput') is not null
drop table #tbl_cmdoutput

create table #tbl_cmdoutput(
cmdout varchar(8000)
)

/*
This table must match column output of the WMIC command below.

Note UNICODE output of WMIC results
*/if object_id('tempdb.dbo.#tbl_diskprops') is not null
drop table #tbl_diskprops

create table #tbl_diskprops(
node nvarchar(128),
deviceid nvarchar(128),
freespace nvarchar(128),
size nvarchar(128)
)

-- ** Variable declarations

declare @wmic_tmpfile varchar(1000)
declare @cmd varchar(8000)
declare @sql varchar(8000)

/*
This step isn't a strict req't for the goal of this script, but the purpose 
is to locate a temporary file for the WMIC output, and my preference was not 
to  hard code and disk letters into the filename
*/
-- ** Expand the %temp% environment variable
insert #tbl_cmdoutput exec master.dbo.xp_cmdshell 'echo "%temp%\temp___.csv"'

set @wmic_tmpfile = (
select top 1 cmdout from #tbl_cmdoutput 
where cmdout is not null
)

set @cmd = 'wmic logicaldisk get deviceid,freespace,size /format:csv > ' 
+ @wmic_tmpfile

/*
A little dynamic SQL is required here because of a variable was used for the 
BULK INSERT statement
*/set @sql = 'bulk insert #tbl_diskprops from ' + @wmic_tmpfile + 
'with (
datafiletype = ''widechar'',
firstrow = 2,
fieldterminator = '','',
rowterminator = ''\n'',
keepnulls
)'

-- ** Execute the WMIC command and temporarily store output to the CSV file
exec master.dbo.xp_cmdshell @cmd, no_output

-- ** Execute BULK INSERT, retrieving the contents of the file into the temp 
-- ** table
exec(@sql)

-- ** Cleanup the temporary file
set @cmd = 'del /Q ' + @wmic_tmpfile
exec master.dbo.xp_cmdshell @cmd, no_output

select * from #tbl_diskprops
set nocount off