Sqlps.exe is the New Black

I have not been posting much :( I have been slammed at work. All of my non-family free time has been spent working on my TechED and SSWUG v-conference presentations and demo's. You can keep up with me on Friend Feed. I share things I find interesting in my google reader subscriptions, tweets, digg's and bookmarks there. We need more SQL people around those parts. There is life after NNTP.  Anyway, on to powershell.

In SQL 2005, you had a few options if you wanted OS or hardware data. You could xp_cmdshell into powershell, you get write a CLR proc or function  or, from powershell, you could create db connection objects and push to SQL. I have examples of doing these in the WMI category.

Now with two lines of code, you can do something like grab the current % CPU usage and insert it like this:

$v = (get-wmiobject -computer localhost Win32_PerfFormattedData_PerfOS_Processor -filter "name like '%total%'").PercentProcessorTime
invoke-sqlcmd -query "insert into demodb..tblCPULogger(value) values($v)"

On top of that, there are powershell job steps so you can run that from a job. Now, you say we can that you can already do that in SQL 2008 with the data collector. Well, -computer can also be a remote server like IIS, SQL Server 2000\2005 etc. The data collector only runs on SQL Server 2008.

Have you ever wanted to suck in a huge eventlog into SQL to analyze? I have. Dump to CSV, then openrowset, then munging dates into the proper format. Again, two lines of code.

$Events = Get-EventLog Application
foreach ($Events in $Events){invoke-sqlcmd -query "insert into demodb..AppLogger([Time],[Source],[type],[EventID],[message]) values ('$($events.Time)', '$($events.Source)','$($events.Type)', '$($events.EventID)', replace('$($events.message)', char(39), ''))" -ServerInstance LocalHost}

Grant it took me a couple days to get those two lines right but there really are not many examples on the net using invoke-sqlcmd. Thus the post. I think I will be getting up close and personal with powershell and I will post more samples. Please comment if you have a useful script already.

email it! |   |   |   |  | 

Permalink     4 Comments  

Rate this Post: 12345
COMMENTS:

I haven't done any PowerShell work yet, but the other guys at Quest are kicking my door down to get me to do it. They're building an open source scripting community at http://powergui.org/ and they're working on getting SQL scripts up there. They might have other stuff you can use.

posted @ Wednesday, May 14, 2008 7:56 AM by Brent Ozar

---

Sweet. Powergui looks nice. Being Quest, you know they will add SQLPS when it RTM's or sooner. You can still use it but there is no color coding or intellisense for SQLPS specific commands yet.

posted @ Wednesday, May 14, 2008 8:48 AM by JasonMassie

---

Comments from the following blog entry: Sqlps.exe is the New Black, located at: http://www.dotnetkicks.com/database/Sqlps_exe_is_the_New_Black

posted @ Saturday, May 17, 2008 3:29 PM by DotNetKicks.com

---

I have not found any help or guidance for this new powershell feature... :(

posted @ Thursday, March 19, 2009 10:18 AM by Remote DBA

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]