SQL errors not captured or Displayed in Director

[pxr40]

Hi,
I am wondering if there is any setting or some parameter that needs to be set to enable the capture of SQL errors/warnings and display in the Director for a given job. ( know for sure the data has some constraint violations which are not being captured by DS)

(Just in case if you are wondering if the filter is customized in the director .. All options are selected in Director Filter for viewing log .. so all log messages have to be displayed)


Source and Target Databases are MS SQLServer 2000 based
Datastage 7.5 is installed on a Window Server 2003
Using ODBC connections created thru regular standard windows ODBC administrator.


Thanks
Poorna

[Sainath.Srinivasan]

You can use logic in DS. But unless it is from external connection to DS or included by some logic in DS, it is difficult to control and maintain.

[ogmios]

From experience... the ODBC stage is not very good at spewing out the good error messages, I spent a couple of hours one time on an "row insert error" (as the ODBC stage indicated) while in fact my tablespace was full.

The native stages are usually better integrated.

Ogmios

[ray.wurlod]

Add an extra Transformer stage just ahead of the ODBC stage.
Create a reject output from this Transformer stage. Rows will go down this link if they are rejected by SQL Server.
In addition to the original data rows you can create extra diagnosic columns on the reject link. The can be populated with link variables from the link to the ODBC stage.

• outLink.SQLSTATE (VarChar 12) the ODBC SQL State generic error code
  
  outLink.DBMSCODE (VarChar 12) the SQL Server error code
  
  outLink.LASTERR (VarChar 2048) full error text from SQL Server

In an after-stage subroutine you can, if desired, read this information from the file to which it was sent and move it into the job log. Or maybe just detect that something went along the rejects link and trigger a warning message (or email, from a job sequence).