Powershell and ODBC

| 2 Comments
One nice thing about PowerShell is that it can talk to databases without a predefined ODBC connection. That makes them a lot more portable! I approve. However, I had trouble finding out how to set up and read data. So here is what I have.

##### Key variables
$SQLServerName="sqlserver"
$SQLDatabase="YourDatabaseInTheServer"

##### Start the database connection and set up environment
$DbString="Driver={SQL Server};Server=$SQLServerName;Database=$SQLDatabase;"
$DBConnection=New-Object System.Data.Odbc.OdbcConnection
$DBCommand=New-Object System.Data.Odbc.OdbcCommand
$DBConnection.ConnectionString=$DbString
$DBConnection.Open()
$DBCommand.Connection=$DBConnection

$InsertStatement="INSERT into Mbox_DB (MBServer, MBStore) values ('$MBServer', '$MBStore')"
$DBCommand.CommandText=$InsertStatement
$DBResult=$DBCommand.ExecuteNonQuery()

$SelectStatement="SELECT MBDBID From Mbox_DB WHERE (MBServer=$MBServer) AND (MBStore=$MBStore)"
$DBComand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteScalar()

$Date=get-date
$PurgeDate=$Date.AddMonths(-3)
$SelectStatement="SELECT Users From LastLogon WHERE (DateTime < '$PurgeDate')"
$DBCommand.CommandText=$SelectStatement
$DBResult=$DBCommand.ExecuteReader()
$UserTable=New-Object system.data.datatable
$UserTable.load($DBResult)

Yes, this is part of a larger script I'm writing. When that finishes, I'll probably post it too.

2 Comments

SysAdmin1138:

I addiver at this post from here: http://serverfault.com/posts/154094/revisions

where you mentioned a larger script that (if I read correctly) will upload events from wevtutil to an ODBC connection. Where can I get a copy of that script?

Thanks,

-Mark

Leave a comment

Other Blogs

My Other Stuff

Monthly Archives