1 Reply Latest reply on May 15, 2018 4:57 PM by 300

    How to create ODBC connection and run SQL from powershell?

    300

      I am using template from https://www.andersrodland.com/working-with-odbc-connections-in-powershell/

      $Error.Clear();

       

      function Get-ODBC-Data{

         param(

         [string]$query=$('select count(*) from [master].[sys].[table_name]'),

         [string]$username='db username',

         [string]$password='db password'

         )

         $conn = New-Object System.Data.Odbc.OdbcConnection

         $conn.ConnectionString = "Server=IP_ADDRESS;Initial Catalog=DB_NAME;Uid=$username;Pwd=$password;"

         $conn.open()

         $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)

         $ds = New-Object system.Data.DataSet

         (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null

         $conn.close()

         $ds.Tables[0]

      }

       

      $result = Get-ODBC-Data

       

      Write-Host "Statistic: " $result[0];

      Write-Host "Message: " $result[0] ;

      exit 0

       

      But I keep getting error: "Not Defined." or - Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

      Did try to replace hard coded IP address with SolarWinds inbuilt ${IP} variable as well but same error.

       

      I think I am making some syntax error, specially on $conn.ConnectionString line.

      Can I get some help in identifying what am I doing wrong?