Error When Using ODBC Connection String

The information in this article applies to product: e-Work 6.x

Issue

  • An attempt is made to issue a SQL query against an external database (e.g. using a function such as %ExecSQL). The query contains a reference to a datetime expressed using a standard ODBC timestamp escape sequence. E.g., the query has a WHERE clause such as 'WHERE DeliveryDate < \{d '2004-11-01'\}' The external database query fails with an error similar to the following: "Failed to process 'eExecuteSQL' e-Work Database Connector request. Unable to transform SQL statement with ODBC timestamp. Procedure ExecSQL call failed. Position: 308"

    This problem is exhibited using both Microsoft SQL Server and Oracle DBMSs.

Cause

  • The issue arises due to specifying a database connection string in standard ODBC form.
    E.g., DSN=eworkdsn;UID=ework;PWD=secret;

Resolution

  • Specify a connection string using the standard OLEDB form.
    E.g., Provider=OraOLEDB.Oracle.1;Data Source=ework.sid;User Id=ework;Password=secret;
    or
    Provider=SQLOLEDB;Data Source=EWORKSERVER;Initial Catalog=e-work;User ID=ework;Password=secret;Trusted\_Connection=No;
Tagged: