CODEWITHSUNDEEP
powershellodbcpowershell-2.0oledbconnectionhana
ace
ace

Reputation: 23

Connecting SAP HANA DB from powershell

I am using the following Power Shell code to connect to a HANA instance from a host where HANA Client is installed -

    function Get-OLEDBData ($connectstring, $sql) {
    $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
    $OLEDBConn.open()
    $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
    $readcmd.CommandTimeout = '300'
    $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
    $dt = New-Object system.Data.datatable
    [void]$da.fill($dt)
    $OLEDBConn.close()
    return $dt
    }

    $hdbSqlCmd = "myquery"
    $hdbConnectionString = "Driver={HDBODBC};ServerNode=myserver:30015;UID=myuser;PWD=mypasswd;"
    Get-OLEDBData $hdbConnectionString $hdbSqlCmd**

But I get following error -

   New-Object : Exception calling ".ctor" with "1" argument(s): "An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'."
   At C:\myspace\hana_connect_1.ps1:5 char:27+    $OLEDBConn = New-Object <<<<  System.Data.OleDb.OleDbConnection($connectstring)
+ CategoryInfo          : InvalidOperation: (:) [New-Object],        MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

When I change the connection string to Provider instead of Driver -

   $hdbConnectionString = "Provider={HDBODBC};ServerNode=myserver:30015;UID=myuser;PWD=mypasswd;"

I get below error -

   Exception calling "Open" with "0" argument(s): "The '{HDBODBC}' provider is not registered on the local machine."
   At C:\myspace\hana_connect_1.ps1:6 char:19
   +    $OLEDBConn.open <<<< ()
  + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

The error remains the same even if I change to 32 bit driver - HDBODBC32, saying HDBODBC32 is not registered.

I am now lost, any help somebody!!

Upvotes: 2

Views: 2700

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

The SAP HANA ODBC driver is not an OLEDB driver. Therefore you have to use the ODBC API instead:

 function Get-ODBCBData ($connectstring, $sql) {
    $Conn = New-Object System.Data.odbc.ODBCconnection($connectstring)
    $Conn.open()
    $readcmd = New-Object system.Data.odbc.ODBCCommand($sql,$Conn)
    $readcmd.CommandTimeout = '300'
    ...
    $hdbConnectionString = "Driver={HDBODBC};ServerNode=myserver:30015;UID=myuser;PWD=mypasswd;"
    Get-ODBCBData $hdbConnectionString $hdbSqlCmd**

should work.

Upvotes: 3

Related Questions