How to work with SQL Server 2005 timestamp columns in an ADT target table

Products

• Advantage Data Transformer

Issue/Introduction

Description:

A SQL Server timestamp column is automatically populated with the current date and time when a row is created, so the ADT INSERT statement must not contain a value for the column.

The following will ensure that ADT does not attempt to insert "null" into this column.

Solution:

When there is a timestamp column in the target SQL Server 2005 table, you will not draw a transformation line to it in Mapper since you won't be moving any data into it. SQL will automatically populate such a column with the current date and time when the row is created.

If the column in SQL Server does not allow nulls and no data is being moved to that column, ADT will generate a target structure that does not include this field.  If the SQL Server column allows nulls and no data is being moved to it, ADT will generate a target structure listing this field with the NOUPDATE parameter.

In the former situation, all columns of the target table are not listed in the ADT structure, the INSERT used to STORE the data will be in the format that names the columns, e.g. INSERT INTO MYTABLE COL1, COL2, ... VALUES(..., ..., ...).

In the latter situation where all columns are listed in the target structure, the command will be in the format INSERT INTO MYTABLE VALUES(..., ..., ...) and the value will be "null" for any NOUPDATE fields. Trying to insert null into a timestamp column will get an ODBC error #23000 with a message that you need to use the INSERT format that lists columns.

If you cannot control the SQL column's Allow Nulls setting, there is a workaround: edit the script to remove this field from the target structure. Note that if this is done, you would need to repeat this edit if the script is regenerated in the future.

Environment

Release:
Component: DWM