2 Replies Latest reply on Mar 9, 2012 5:50 AM by Kgronau-Oracle

    Workaround for MS SQL Column Names >30 characters...using DG4ODBC

    922679
      Good Afternoon.

      Using DG4ODBC (Heterogeneous Agent Release 11.1.0.7.0 - 64bit Production) to link to a MS SQL Database (2008R2 64 Bit).

      Getting a error: "[Microsoft][ODBC SQL Server Driver][SQL Server]'ALLOW_SYSTEM_NOTIFICATION_FLA.' is an invalid name because it contains a NULL character or an invalid unicode character.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared."

      Ultimately traced it to the fact that the MS SQL Server table we are querying has a few column names in excess of 30 characters.

      Shortening the column names in MS SQL database does resolve the problem, HOWEVER for various reasons this is not an option.

      Is there a setting (HS_) or a way to compensate (DG4ODBC) for long MS SQL Column names?

      Thanks,

      Steve

      Edited by: user11130488 on Mar 8, 2012 12:11 PM
        • 1. Re: Workaround for MS SQL Column Names >30 characters...using DG4ODBC
          damorgan
          No version numbers and a lot of other information is missing ... but consider creating a view and accessing the table via a view.

          Edited by: damorgan on Mar 8, 2012 12:03 PM
          • 2. Re: Workaround for MS SQL Column Names >30 characters...using DG4ODBC
            Kgronau-Oracle
            There is no other way around then creating a view on the MS SQL Server side which has less or equal to 30 characters for table and column names as the 30 character limitation is a limitation in the Oracle database up to 11.2

            Here the most common errors:

            For table names that exceed the 30 character limit:
            Any identified longer then 30 character will result in
            SQL Error: ORA-00972: identifier is too long
            00972. 00000 - "identifier is too long"
            *Cause:    An identifier with more than 30 characters was specified.
            *Action:   Specify at most 30 characters.


            And for column names:
            SQL Error: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
            [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid column name 'ALLOW_SYSTEM_NOTIFICATION_FLAG'. {42S22,NativeErr = 207}[DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared. {HY000,NativeErr = 8180}
            ORA-02063: preceding 2 lines from SQLSERVER_DG4DD61_EMGTW_1123_DB