9 April 2010, 10:45 pm
It’s been a long time (over 2 months) since I last posted. I’ll try to get back into a rhythm of posting at least weekly. Anyway, this is something that occurred to me at work when writing a script.
I usually avoid nullable columns, but sometimes date fields make sense to be null (rather than use sentinel values like 1/1/1900). In this case, I had a nullable date column and I needed to check in PowerShell whether the field was in fact null or not. In SQL, I would have just used an IS NULL, or used the IsNull() function to replace the null value with something a little easier to deal with. My first (feeble) attempt was to do this:
if (!$_.completedDate){
# it’s null
}
Unfortunately for me, that doesn’t work. Next, I used this (which worked, but wasn’t very satisfactory either):
if ($_.completedDate.ToString() -eq ''){
# it’s null
}
Realizing that I was being stupid, I googled “PowerShell SQL NULL and after looking at several pages which didn’t really address the issue, I found this. A little work to change it into a function, and voilà.
function is-null($value){
return [System.DBNull]::Value.Equals($value)
}
A few quick tests and this is what I wanted. Now, my code looks like this:
if (is-null $_.completedDate){
# it’s null
}
I find it hard to believe I haven’t written this function before (or seen it).
By the way…be watching the SQL PowerShell Extensions project. Chad released version 2.1, which includes SQL mode for the ISE (really nice). I know he and several others are collaborating on an update which should be out sometime soon.
-Mike
5 January 2010, 6:39 pm
The first module-based release of the SQL PowerShell Extensions (SQLPSX) was released recently on CodePlex. It features very handy wrappers for most of the SMO objects used to manipulate SQL Server metadata, SSIS packages, Replication, and (new in the 2.0 release) an ADO.NET module which I wrote based on the code in this post. There’s also a data-collection process and Reporting Services reports to help you get your SQL Server installations under control.
Chad Miller, the driving force behind SQLPSX, has put a lot of effort into this release, and you’ll find really good examples of advanced functions (with comment-based help, even).
If you deal with SQL Server in any way, you’ll almost certainly be able to use this set of modules to streamline your scripting experience (and probably learn something about SMO in the process).
You can find the release here.
16 December 2009, 11:51 pm
Recently, we had an occasion to write a process to read event logs on several sql servers to try to determine login times for different sql and Windows logins. Since we have begun using PowerShell v2.0, and since get-eventlog now has a -computername parameter, it seemed like an obvious solution.
The event message we were interested in looked something like “Login succeeeded for uesr ‘UserName’ ….”. The code we were trying to use was:
get-eventlog -computername $servername -logname Application -message "Login succeeded for user*" -after ((get-date).AddDays(-1))
I expected that, given a date parameter and a leading string to match wouldn’t be too bad, but this ended up taking several minutes per server. As there are over a hundred servers to scan, that didn’t work well for us.
We ended up falling back to get-wmiobject.
$BeginDate=[System.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-1))
get-wmiobject -class win32_ntlogevent -computerName $servername -filter "(EventCode=18453) and (LogFile='Application') and (TimeGenerated >'$BeginDate')"
Cons:
- We have to encode the date parameter (instead of using a nice datetime parameter like get-eventlog has)
- We have to write a WQL where-clause to match the parameters
Pros:
- We get to use the event code (rather than a string match)
- The code is orders of magnitude faster (39 servers in 13 minutes as a test case)
I think that you might have a positive experience using get-eventlog if you need to scan a range of time (for instance if you’re reporting on what happened on the server), but if you need to look for a specific event (or set of events) you’re probably going to want to use get-wmiobject.
-Mike
15 September 2009, 9:32 pm
We all know that using string concatenation (or substitution) in SQL is a “bad thing”. If you google “SQL Injection”, you’ll find hundreds of pages that tell you not to do things this way and that you need to use parameterized queries. However, I still see a lot of code in PowerShell that does this:
$cmd.ExecuteNonQuery("delete from Table1 where Column1='$value'")
Since code like this is obviously prone to SQL injection attacks, it must be that doing it the right way is difficult, right? Actually, no. Here’s a simple function that allows you to run parameterized queries easily using dictionaries.
function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
if ($help){
$msg = @"
Execute a sql statement. Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
Write-Host $msg
return
}
$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
$cmd.CommandTimeout=$timeout
foreach($p in $parameters.Keys){
[Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
}
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds) | Out-Null
return $ds
}
Example usage:
$conn=new-object data.sqlclient.sqlconnection "Server=servername;Integrated Security=True"
$conn.open()
exec-query 'select * from sys.databases' -conn $conn
exec-query 'select FirstName,LastName from AdventureWorks2008.Person.Person where FirstName=@fname' -parameter @{fname='Mike'} -conn $conn
As you can see from the second example, using a parameterized function wasn’t much harder than it would have been to do string concatenation or substitution. And you don’t have to worry about little Bobby Tables.
The code above still needs some work to take care of output parameters, but I’ll save that for another post.
Let me know if you have any questions, comments, or complaints.
Mike
