ODBC Connections in Powershell

Working with ODBC Connections in Powershell

This post explains how to work with ODBC connections in Powershell. Powershell 4 introduced new cmdlets that make it very easy to create and manage ODBC connections. We use the .NET assembly system.data.odbc.odbcconnection to use ODBC connections present on the system to query the database.

ODBC connections in Powershell 4.0 and higher

Powershell 4 introduced these 4 cmdlets to create, manage and remove ODBC connections. If you for some reason still are on Powershell 3.0, I recommend you to upgrade to Powershell 5.0 which is the newest version at the time of this blog post. Managing ODBC connections in Powershell 3 or older is not fun and requires you to either modify registry or use an .exe file to manage them for you.

  • Add-OdbcDsn
  • Get-OdbcDsn
  • Remove-OdbcDsn
  • Set-OdbcDsn

Add a new ODBC connection with Powershell

Use the the cmdlet Add-OdbcDsn to create a new ODBC connection. The example code creates a new ODBC connection named MyPayroll.

Get an ODBC connection with Powershell

Use the the cmdlet Get-OdbcDsn to get a Powershell object of an ODBC connection. The following code example returns an object containing the 32-bit ODBC configuration named MyPayroll.

Change properties on an ODBC conenction with Powershell

Use the cmdlet Set-OdbcDsn to change the properties of an existing ODBC connection. The following code example change the ODBC connection named MyPayroll to use the database Payroll.

 

Testing an ODBC connection with Powershell

This is the source code for the function Test-ODBCConnection which test if the ODBC connection can connect to the database. The function returns $true if a successful connection is made, and $false if it cannot connect. It will print an error message to the console if there is an error somewhere, like the ODBC connection do not exist.

You can download this script from Microsoft Technet Gallery: https://gallery.technet.microsoft.com/Test-ODBCConnection-522fefaf

Query ODBC connection to get data with Powershell

 

Query ODBC connection to set data with Powershell

 

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I currently hold active 15 Microsoft certifications. Certified on Windows Server, Windows Client, SQL, Exchange and System Center Configuration Manager. Anders Rødland also holds an ITIL Foundation certification.

This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.

  • Giuseppe Toscano

    Is there a way to use Powerscript against my PC to check out ALL ODBC set up? I have over 100 and testing each one individually is painstakingly long.