Using WMI objects from TSQL

Question

Using WMI objects from TSQL

mdenner

SSC Eights!

Points: 833
More actions
January 10, 2005 at 8:22 am

#60378

I write a lot of capacity planning scripts that either gather information about SQL objects (i.e., how big is a table, how big is each row on average, etc.) or at the application level (how many widget objects have been created last month, etc.). I also have some capacity planning scripts for entities like disk drives and shares that run with an Excel spreadsheet using WMI (for examples and documentation, see: http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx).
I'd love to be able to put all these processes in one place, ideally writing the data to tables to track information over time, and the most natural way for me to do this is through scheduled tasks that execute T/SQL procedures.
Has anyone tried writing WMI scripts within TSQL using the sp_oaCreate/sp_oaMethod calls? I was stumped immediately, since most of the WMI examples I have seen in VBScript use GetObject rather than CreateObject to get the primary winmgmts object that drives everything else. T/SQL doesn't seem to have a comparable call. Any suggestions?
Thanks,
Mark Denner

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Why not just run the wmi script through xp_cmdshell, have it output to disk, and then stuff that output into a table..? (probably needs some clever parsing, but you don't need to mess with the sp_OAxxx stuff)

/Kenneth

mdenner SSC Eights! Points: 833 More actions · Answer 2

I've considered it, but at that point I'd rather just write it as pure VBScript that uses WMI to get the info and ADO to update the relevant fields in a table. I guess I'd rather just keep it all within one technology. Thanks for the insight, however.

Mark

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Well, ADO is just fine if you want to do it from a 'client-side'. If you wanted to do from within Transact SQL, then I'd probably use xp_cmdshell...

Personally, I'd stay away from sp_OAxxx procs - they're just too risky/messy

/Kenneth

Antares686 SSC Guru Points: 125444 More actions · Answer 4

But why not just write in VBScript as that is a language option for a job step? Much simpler than trying to use sp_OACreate and stuff.

Gustavo Merle SSC Rookie Points: 49 More actions · Answer 5

Mark: about your original question, even if I completely agree with the other posts about not being a fan of sp_OA... procedures for a couple of reasons, if you want to continue playing with this option you could try the following:

From ADSI SDK 2.5 you can take and register ADsFactr.dll (22-Dec-1998 - 32.768 bytes - v1.0.0.1) on the server.

Having this DLL allows you to use GetObject even from sp_OA... (by using the GetObject method from the ADSFactory object that you create with CreateObject). The following example works with rootDSE:

declare @factory int, @rootdse int, @property varchar(100)

EXEC sp_OACreate 'ADSFactory', @factory OUT

EXEC sp_OAMethod @factory, 'GetObject("<A href="ldap://rootDSE&quot'/">LDAP://rootDSE")', @rootdse OUT

EXEC sp_OAMethod @rootdse, 'get("serverName")', @property OUT

EXEC sp_OADestroy @rootdse

EXEC sp_OADestroy @factory

The problem that I ran into that I could not overcome when trying to implement some WMI stuff was the inability of the sp_OA... procedures to go through a collection with something like "for each".