INSERTing data into SQL Server via ODBC gets an error that a column name or table name is invalid, even though all names can be confirmed to be correct.

Products

• Advantage Data Transformer

Issue/Introduction

Description:

An ADT script is attempting to insert data into Microsoft SQL Server. ADT reports the error as:

Communications error: STORE to profile '<profile name>'; specific error #22: ODB error S0022: [Microsoft][SQL Native Client][SQL Server]Invalid column name '<columnname>'.
or
Communications error: STORE to profile '<profile name>'; specific error #2: ODB error S0002: [Microsoft][SQL Server Driver][SQL Server]Invalid table name '<tablename>'.

Solution:

The ODBC System DataSource used by an ADT ODBC Interface Profile to access SQL Server needs to have the ODBC DSN configuration option "Use ANSI quoted identifiers" checked.

Background:

When an ADT ODBC Interface is used, you need to name an ODBC System Data Source that will connect to the database and pass the SQL commands through to Microsoft SQL Server. An ODBC System Data Source is also used to connect to the ADT Idb which may be hosted by SQL Server. There may be some confusion about the DSN configuration parameters because an SQL Idb requires that the ODBC DSN option "Use ANSI nulls, padding, and warnings" be unchecked.

Some ADT users may uncheck both options

• "Use ANSI quoted identifiers" and
  
  
• "Use ANSI nulls, paddings, and warning"

for the Idb and for DSNs used by ADT Profile connections. This is not recommended, and we have found that un-checking "Use ANSI quoted identifiers" during DSN configuration for a DSN used in the Profile configuration will cause the above error.

Environment

Release:
Component: DWM