BODS fails to return SQL Server check constraint error

dataservices job design
1

Under a certain set of circumstances, BODS tries to insert data into SQL Server table, fails, does not return an error, and the job completes successfully. Here is the scenario:

  • Dataflow selects data from a database.
  • Same dataflow “upserts” data in a different database. We use options “Auto-correct load=Yes”; “Allow merge or upsert=Yes”
  • Merge SQL cannot push down because two different databases involved. Instead, data is selected into a buffer. Database language IF statement is generated on the fly: “if target row exists then update… else insert…”
  • The IF statement is successfully submitted to the database engine. The statement fails. In our example, it fails because a check constraint is violated (we are trying to insert U into a column that allows Y/N). Data Services does not recognize the error. DS thinks the insert was successful and increments the number of rows processed, and goes on to the next row.

I ran a SQL trace, copied the statement (if exists then update… else insert…) right out of the trace, pasted it into Management Studio and ran it. It now returns correct constraint error.

I reviewed target table editor options in the manual. I discussed with co-workers, nobody seems to have experienced this scenario. I am not using bulk insert.

Any insights would be appreciated. Dan

dhaag :us: (BOB member since 2013-01-22)

2

(Scratching head…)

I dunno. That’s pretty strange. :crazy_face:

There is nothing in the error log at all? You don’t have a try/catch that isn’t re-raising the exception?

Sounds like a bug to me.

eganjp :us: (BOB member since 2007-09-12)

3

Looks like BODS catches the error internally?

How does the generated SQL look like? The same as in the SQL trace?

Johannes Vink :netherlands: (BOB member since 2012-03-20)

4

Nothing in error log to indicate an error. I’m going to add a try/catch as you suggest to see if there are any other clues.

The generated select statement looks same as in trace.

dhaag :us: (BOB member since 2013-01-22)

5

No, if there isn’t anything in the error log then adding a try/catch won’t do you any good. I was asking if there was a try/catch already in place. Often times developers forget to re-raise an exception at a lower level of the job so that the exception “bubbles up” to the job level where it can then be used to kill the job.

eganjp :us: (BOB member since 2007-09-12)

6

I discovered that if I simply change the sequence of rows being processed I get different results. If the first row to be processed violates the check constraint, the error is returned correctly and job fails. If the first row is good, the job finishes successfully, even though some subsequent rows will fail to insert.

And if I change the commit count to 1, the job also correctly returns the error and fails.

dhaag :us: (BOB member since 2013-01-22)

7

I further refined my test job to try to boil it down. This time my job is not selecting from a database at all, but using BODS Row_Generation transform. I put check constraint on column YES_NO to allow only Y or N. I can make it return error code correctly by making row number 1 have YES_NO=U, and commit count = 1000. It pretends to finish successfully if the U is in a different row. Here is what the sql trace looks like:

SQL submitted from loader <Z_DH_TEST> in is:
IF EXISTS
(SELECT 1 FROM “DBO”.“Z_DH_TEST”
WHERE “CLAIMID” = 1 )
UPDATE “DBO”.“Z_DH_TEST” SET “YES_NO” = ‘Y’, “COLUMN2” = ‘N’ WHERE “CLAIMID” = 1
ELSE INSERT INTO “DBO”.“Z_DH_TEST” (“CLAIMID”, “YES_NO”, “COLUMN2”) VALUES (1, ‘Y’, ‘N’)
IF EXISTS
(SELECT 1 FROM “DBO”.“Z_DH_TEST”
WHERE “CLAIMID” = 2 )
UPDATE “DBO”.“Z_DH_TEST” SET “YES_NO” = ‘N’, “COLUMN2” = ‘Y’ WHERE “CLAIMID” = 2
ELSE INSERT INTO “DBO”.“Z_DH_TEST” (“CLAIMID”, “YES_NO”, “COLUMN2”) VALUES (2, ‘N’, ‘Y’)
IF EXISTS
(SELECT 1 FROM “DBO”.“Z_DH_TEST”
WHERE “CLAIMID” = 3 )
UPDATE “DBO”.“Z_DH_TEST” SET “YES_NO” = ‘U’, “COLUMN2” = ‘Y’ WHERE “CLAIMID” = 3
ELSE INSERT INTO “DBO”.“Z_DH_TEST” (“CLAIMID”, “YES_NO”, “COLUMN2”) VALUES (3, ‘U’, ‘Y’)

Data flow <z_test_constraint_error> is completed successfully.
Process to execute data flow <z_test_constraint_error> is completed.
Job <z_job_test_constraint_error> is completed successfully.

The essential element is that multiple update statements are stacked in the buffer. It appears the job correctly interprets the return from the first statement, then if the first statement is successful, it assumes the remaining statements are also successful.

If I set commit count to 2, and the third row has YES_NO=U, the job correctly returns error message and fails.

dhaag :us: (BOB member since 2013-01-22)

8

I wonder if BODS has problems with the check constraint on SQL Server specifically or in general( and thus also with Oracle)?

ErikR :new_zealand: (BOB member since 2007-01-10)

9

I think this is a bug. SQL Server error handling is not quite as sophisticated as what you can find in Oracle PL/SQL. A constraint error is likely created by SQL Server but the next command still gets executed and that clears out the error from the buffer.

It seems to be simply a matter of bad scripting on the part of Data Services. There isn’t any check for @@ERROR. Here is what the SQL Server manual has to say about @@error:

eganjp :us: (BOB member since 2007-09-12)

10

Thanks for that Jim - that makes sense.

But is the error on the part of SQL Server clearing the @@ERROR value and happily moving alogn despite a constraint violation - or is Data Services ‘lazy’ in its checking of @@ERROR and thus misses the raised error all together?

If it is the first, then Data Services would not have a chance to catch the error amid the burst of SQL statements executed on the database?

Has an ADAPT ticket be raised for this issue by the topic starter?

ErikR :new_zealand: (BOB member since 2007-01-10)

11

Thank you all for your comments. I now have a better understanding of the issue. ADAPT ticket has not been raised. I don’t have that capability, I can only pass along the issue to my client, which I have done. As a workaround, I plan to set commit count=1 for small amounts of data, and force full pushdown for larger datasets (loading to interim template table without constraints when necessary).

dhaag :us: (BOB member since 2013-01-22)

12

SQL Server is just doing what it is told.

Without breaking out my rusty TSQL coding skills to produce production ready code here is how I think it should look:

IF EXISTS 
(SELECT 1 FROM "DBO"."Z_DH_TEST" 
WHERE "CLAIMID" = 1 ) 
UPDATE "DBO"."Z_DH_TEST" SET "YES_NO" = 'Y', "COLUMN2" = 'N' WHERE "CLAIMID" = 1 
ELSE INSERT INTO "DBO"."Z_DH_TEST" ("CLAIMID", "YES_NO", "COLUMN2") VALUES (1, 'Y', 'N') 
IF (@@ERROR <> 0)
BEGIN
   ROLLBACK TRAN;
   RAISERROR;
END;
... Next statement

A try/catch might work better. Anything would be better than no error handling.

eganjp :us: (BOB member since 2007-09-12)

13

on the target table that you are inserting the rows into via BODS, under options> have you enabled error handling and to output rows to a flat file?

If this is the case, BODS will write the rows that cannot be inserted to the .txt etc and the job will carry on successfully. If this is not the case, ignore me :slight_smile:

John_F_Sheppey (BOB member since 2009-10-16)

14

Somehow I missed your reply. I have never used error handling file for target table before. Thanks for the suggestion.
We finally heard back from SAP:

dhaag :us: (BOB member since 2013-01-22)