Activity lookup doesn't catch Stored Procedure error RRS feed

  • Question

  • When using Lookup activity to execute an Azure SQL Stored Procedure, the activity is always succeeded, even when occurs an error. This problem doesn't happen when I execute the same Stored Procedure from a Stored Procedure Activity
    Thursday, July 11, 2019 9:51 PM

All replies

  • Could you share the lookup activity id? 
    Friday, July 12, 2019 11:17 AM
  • Hello Bruno Meyer and thank you for your inquiry.  The 'why' of the matter is a nuance of how an activity determines a failure.

    While the Stored Procedure activity cares about the success/failure of the actual stored activity on your server, the Lookup Activity merely cares about whether it receives data in response.

    For example, make a stored procedure which simply divides its parameters ( Select Param1 / Param2 End).  When Param1 = 2 and Param2 = 2, the result is 1, and is passed back.  The Stored Procedure activity is happy because the procedure was run successfully.  The Lookup Activity is happy because it was returned a value, 1.

    Now try Param1 = 2 and Param2 = 0.  This will cause a division-by-zero error.  The stored procedure activity reports an error because the exception bubbled up from the server.  The Lookup activity fails because there was no return value.

    Now try using a stored procedure which does not return any values.  The Stored Proc activity will succeed but the Lookup Activity will fail with

        "errorCode": "2100",
        "message": "Failure happened on 'Source' side. ErrorCode=UserErrorInvalidDbStoredProcedure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified Stored Procedure is not valid. It could be caused by that the stored procedure doesn't return any data. 
    .  Now let us go back and modify that example division stored procedute.
    ALTER procedure [dbo].[divide] @Param1 int, @Param2 int
    SELECT 2
    SELECT @Param1 / @Param2

    Now, see there are two select clauses.  The hardcoded 'Select 2' executes before the other one.  Now when we try running again, with Param1 = 5, anda Param2 = 0, the LookupActivity will receive the value "2", even though the stored procedure will fail with a divide by zero afterwards.  The Stored Procedure Activity will report failure because of the exception, but the Lookup Activity will succeed because it received some data.

    Does this make sense?  If this is not what is happening to you, please do give us some more details to work with.

    Friday, July 12, 2019 9:08 PM
  • @Bruno Meyer, did this answer your question?
    Monday, July 15, 2019 8:19 PM
  • Since you haven't responded, I will assume the issue resolved.
    Tuesday, July 16, 2019 9:46 PM