BorderLayoutBoxedLayoutOpenLayoutMaximum textMedium textSmall text


Register
Saturday, July 04, 2009
View Article

Sqlps.exe is the New Black

Posted by Jason on Wednesday, May 14, 2008 to
988 Views | 4 Comments | Article Rating

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:
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


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


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

Copyright 2006 by Statistics IO, My SQL Server Blog