Powershell - ODBC

About

ODBC - Open Database Connectivity in powershell

ODBC is part of Windows Data Access Components (Wdac or Windows DAC)

Management

List Driver

• Filtered for SQL Server

Get-OdbcDriver | Select Name, Platform | Where-Object {$_.Name -like '*SQL Server*'}

Name                                       Platform
----                                       --------
SQL Server                                 32-bit  
SQL Server Native Client 11.0              32-bit  
ODBC Driver 13 for SQL Server              32-bit  
SQL Server                                 64-bit  
SQL Server Native Client 11.0              64-bit  
ODBC Driver 13 for SQL Server              64-bit  
SQL Server Native Client RDA 11.0          64-bit  
Oracle 7.1 SQL Server Legacy Wire Protocol 64-bit  
Oracle 7.1 SQL Server Wire Protocol        64-bit  
Oracle 5.3 32-BIT SQL Server Wire Protocol 64-bit 

List DSN

Get-OdbcDsn | Select Name

Name                     
----                     
AnalyticsWeb             
opss_datasource          
biplatform_datasource    
SqlAzure                 
ObieeClientConnection    
SqlDwhAzure              
Sample Microsoft Hive DSN
SqlOracleHiveAzure       
SqlHiveAzure             
DM_OHIBO                 
SDA_Datamart             

Create DSN

Add-OdbcDsn -Name $DSN_NAME -DriverName $DRIVER_NAME -DsnType "System"

Get DSN Properties

$myDsn = Get-OdbcDsn -Name OdbcName
$myDsn.PropertyValue

Server=sql00999t.database.windows.net
Encrypt=No
TrustServerCertificate=No
ClientCertificate=
KeystoreAuthentication=
KeystorePrincipalId=
KeystoreSecret=
KeystoreLocation=
Trusted_Connection=No
Database=adventureworkstest

Set DSN Properties

Set-OdbcDsn -Name "MyPayroll" -DsnType "System" -Platform "64-bit" -SetPropertyValue "Database=Payroll"