Test-ODBCConnection

Powershell function to test if an ODBC connection is able to connect with the database or not.  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 O

 
 
 
 
 
2.3 Star
(3)
2,180 times
Add to favorites
Databases
2/12/2017
E-mail Twitter del.icio.us Digg Facebook
  • Data source name not found and no default driver specified
    2 Posts | Last post March 28, 2019
    • If I configure a real basic User or System DSN, that connects to a SQL box, successfully at DSN creation, then I try to run this by passing it that DSN name, in this case, 'prod', I get the following error message:
      
      Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
      
      Any thoughts? Ive tried creating the DSN in both User DSN tab, System DSN tab, in the 32 bit odbc and 64 bit obdc. All attempts are using Driver called 'SQL Server'
    • I found that this script will not execute correctly under any circumstance.  The Open() method does not return anything-- if a connection is successful it will not return true.
      
      Here is the code I wrote to test:
      
      ---
      
      $conn = new-object system.data.odbc.odbcconnection("DSN=test2")
      
      
      $ret = $conn.open()
      Write-Host $ret
      $cmd = New-Object System.Data.Odbc.OdbcCommand("SELECT PersonID FROM testTable",$conn)
      $reader = $cmd.ExecuteReader()
      
      while ($reader.Read())
      {
          Write-Host $reader[0]
      }
      
      $conn.close()
      
      ---
      
      $ret has no value if the connection is successful. The error that you get throws if the DSN does not exist.  But that error is not related to this script because this script does not work.