locked
Look Up activity -Stored Procedure with Out param RRS feed

  • Question

  • Hi All,

    I am trying to execute stored procedure with out parameter in look up activity of ADF. I am getting following exception in Pipeline:

    "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. Invalid Stored Procedure script: '[dbo].[get_XXXXX]'.,Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "Lookup1" }

    1.I have created connection linked service to my Azure PAAS database 

    2. I have created Azuredataset which is not pointing to any table, since data would be fetched/looked up using  stored procedure.

    3. I have added lookup activity in the pipeline.

    Could you please help me out?

    Thanks

    Girish


    girishkumar.a

    Tuesday, September 10, 2019 7:16 AM

All replies


  • "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. Invalid Stored Procedure script: '[dbo].[get_XXXXX]'.,Source=Microsoft.DataTransfer.ClientLibrary,'", "failureType": "UserError", "target": "Lookup1" }

    Does the SP run fine when you execute it in SSMS? Does it return any data or at least empty result set?

    The SP should have a select query at the end as last statement. 

    See: 

    https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse#azure-sql-data-warehouse-as-the-source


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)


    Tuesday, September 10, 2019 7:37 AM
  • Yes, It runs fine in SSMS. oh, my stored procedure had return statement at the end

    girishkumar.a

    Wednesday, September 11, 2019 3:06 AM
  • Hello Girish , 

    At this time I can confirm that I was able to repro the issue . The proc with OUTPUT does throw the same error as you mentioned , but if  your proc does not have a OUTPUT defined it works fine . 

    At this time I am reaching out to the internal team to get help on this , I will let you know once i hear back from them .


    Thanks Himanshu

    Thursday, September 12, 2019 4:50 PM
  • I just heard back from the internal team . The OUTPUT parameter is not supported and you need to alter the stored procedure script as result set output, rather than the output parameter


    Thanks Himanshu

    Tuesday, September 17, 2019 5:23 PM