Weird non-deterministic outcome from Stored Procedure.

  • I work as a developer in a manufacturing company.  We use Jitterbit to manipulate complex XML from one system and output it to SQL Server, where further Jitterbit scheduled tasks manipulate the raw staging data before writing it to SAGE.

    We've had to introduce a horrible hack (surely not!) to ensure that drawing numbers from XML are inserted into the correct columns in one of the SAGE tables.  It would be almost impossible to script example tables and stored procedures to show how this works (and it's proprietary as well) but here's a general overview of what happens.

    Jitterbit generates a collection of rows, one per part, from the raw XML.  Each row may have a comma-separated list of drawings.  These are inserted into a staging table.

    Jitterbit has another scheduled task to open a view (let's call it MyDrawingUpdate) on the staging table and, one by one, process each row by sending parameters obtained from the row to a stored procedure which then manipulates the data and writes it to SAGE.

    Part of this process is to split the comma-separated string of drawings into a temporary table and then concatenate the rows into separate string variables according to business logic.  Once this process is complete a MERGE operation on the SAGE table is performed.  So, for example, at the end of this there might be a string variable called @text01 with a value 'DWG001 REV02, DWG003 REV01' which is destined for a particular column in SAGE.

    This is the weird part.  If I allow Jitterbit to manage this process, I can see, by constantly refreshing a query

    SELECT * FROM MyDrawingUpdate

    that the number of rows that remain to be processed diminishes.  After a minute there are no further rows to process.  So I go to SAGE and I can see that the value of @text01 did NOT get correctly written to SAGE.  In fact it wrote only 'DWG001 REV02', omitting the second drawing.

    But if I execute this stored procedure from SSMS using the same parameters for a single row, the value in @text01 is correctly processed, and the column in SAGE has both drawings.

    Other columns, where there may be only one drawing, are correctly populated.  But where there are two or more drawings, comma-separated, only the first value is written to SAGE.

    So how can the same raw data, operating on the same stored procedure, generate a different result if it's run as part of a collection of updates, than if it's run atomically.

    Thanks

    Edward

    • This topic was modified 1 year, 11 months ago by  edwardwill.
  • If you follow the same code path, unless the data changes, you'll get the same result.

    However, and this might be me mis-reading your post, but it seems like there are multiple things happening with the Jitterbit part, that are different from what might happen with the proc. In there would be the chance that data is being changed

  • Thanks Steve.  I agree - I think the Jitterbit part is likely the "culprit", but I'm struggling to understand how the two code paths can result in the different outcomes.  I think I'm going to add an OUTPUT clause and capture the results from the MERGE and see what's happening.  I would add that we've been testing this for two months and I have a deep understanding of the interrelationship of all the systems (there are others beside Jitterbit, SQL Server and SAGE) so at this late stage, just before going live, the issue is a bit of a head-scratcher.

    • This reply was modified 1 year, 11 months ago by  edwardwill.
  • I've figured out the problem.  We had increased the width of the underlying table but we didn't refresh the view, so the comma-delimited data was truncated.  Memo to self - views don't automatically refresh themselves (also memo to Microsoft - why not?)

    • This reply was modified 1 year, 11 months ago by  edwardwill.
  • Why not indeed. This has been a bug with people and SELECT * for a long time.

    Essentially, the compilation of the view is the problem. It's like two different programs linked. If we don't recompile the dependent one, it doesn't change.

  • Well, at least I learnt a valuable lesson!  Thanks for your help in this.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply