Monthly Archives: October 2011

List OLE/ODBC Providers with .NET


Recently I wanted to be able to list the OLE/ODBC Providers installed on a system. Not horribly hard, but also didnt have a ton of luck finding info on how to do this.

OLE

For OLE there is a .NET library that allows you to do this fairly easily. I focused in on the wrong method at first but thanks to help from BigTeddy over at the Technet Powershell forums the problem was resolved.

System.Data.OleDBEnumerator class has a GetElements method that will return a list of available providers.


(New-Object system.data.oledb.oledbenumerator).GetElements()

ODBC

Oddly enough, you would think that the System.Data.ODBC namespace would have something along those lines, but no. If it does, I cant find it. However, you can pull it from the registry fairly easily from the following location.

HKLM:\Software\ODBC\ODBCINST.INI\ODBC Drivers


Get-ItemProperty 'HKLM:\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers'

#32 bit drivers on a 64 bit system here

Get-ItemProperty 'HKLM:\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers'

Advertisements

DB2 ODBC connection crashes with an Access Violation in DB2APP64.DLL


Im trying to work with DB2 (you’ll see a few more posts in regards to this) and I installed the ODBC driver on to my Win7 box and tried a simple connection/query with powershell, something like this.


$connstr = "DRIVER={IBM DB2 DRIVER for ODBC - C:/DB2ODBC};Database=myDB;Hostname=myServer;Port=1234;Protocol=TCPIP;Uid=user;Pwd=password;"
$conn = New-Object data.odbc.odbcconnection $connstr
$conn.open()

$cmd = new-object data.odbc.odbccommand
$cmd.connection = $conn
$cmd.commandtext = "select * from myTable"
$cmd.executereader()

Normally this would be fine, but in the case of DB2, it crashes powershell stating there was an Access Violation with DB2APP64.DLL.

After a bit of screwing around, what I found was that this happens when you try to access a 32bit database (yeah, I have a 32bit DB2 system) from a 64 bit system.

Installing the 32 bit odbc driver and using 32bit powershell resolves this. Not how i’d like it to go, but, its all I could come up with.

PowerShell and Task Scheduler – Working with Exit Code (Last Run Result)


Until we start using PS 3.0, we’re stuck with using Task Scheduler to run our tasks, which is fine, but its hard to tell when a script fails. We then need to create monitoring scripts to monitor the results of the script, or add a lot to the script to get it to handle its own errors.

If you have a monitoring system in place that can monitor schedule tasks, then it would be great to put that to use, and now you can.

In any place you’d normally trap errors and exit, you can do this.


[Environment]::Exit(11)

This will send the Exit Code 11 to Task Scheduler so that a monitoring system can pull the last run result from that task.

There is a ExitCode property in Environment which sets the code on a normal exit

http://msdn.microsoft.com/en-us/library/system.environment.exitcode.aspx

So you can set it and let the script continue if you like.


[Environment]::ExitCode = 11

The other part of this is reading exit codes from task scheduler (maybe you want to make your own monitoring system)

Its real easy if you have the task name, lets say the task is called TestScript


schtasks /query /tn TestScript /v /fo list | ?{$_ -match "Last Result:\s+(.+)"} | %{$matches[1]}

You can remove the TN param and get info for all but then you need to do a little more regex work to pull what you need, but it’s not all that hard.

If you are interested in getting all of them let me know!

Enjoy!

Rounding a date in PowerShell


Was involved in an interesting post about grouping data based on the date. Now obviously you can’t just use Group-Object on the date because by default it has it down to the second and that’s not going to group anything. So we can do a little rounding.


$dt = get-date

$dt.addminutes(-($dt.minute % 5))

This will round it down to the closest 5 min marker. You can swap out the minutes for hours or seconds or whatever unit you want as well as change the number.

If you want to use this in a select, you can do something like this.


$myobject | select name, something, @{l="date";e={$_.addminutes(-($_.minute % 5))}

Enjoy!