Thursday, February 28, 2013

Trigger as a root cause of ODBC error

Scenario:
One table fed from two different real-time sources. The requirement is to keep only the latest version of a given row in the target.

Solution:
Two real-time workflows with one target with an instead-of trigger to take care of insert-and-updates.

Error:
Code: WRT_8229
Message: FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]1

Additional symptoms:
All rows reported as "Rejected" with row details logged in session log. However - this was the big surprise - all rows have been properly delivered to the database. With the insert/update logic correctly executed!

Cause:
As part of the trigger definition there was a statement to display the affected row number:
print @@rowcount
The message was treated by ODBC driver as low-severity error. And since all the rows were processed one-by-one, for each and every one the appropiate message ("1") has been returned to ODBC, which added the FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver] information resulting in the above error.

After the "print @@rowcount" has been removed, all statistics were reported correctly.

No comments:

Post a Comment