none
Catch Return Value of Stored Procedure using SQL Adapter In BizTalk Server 2006 RRS feed

  • Question

  • Hi All,
    I have ageneral query regarding SQL adapter..
    i have Request - Response SQl port where i send data to insert into a table ..so in response i get  default Xml as mentioned.. 
    <?xml version="1.0" encoding="utf-16"?><ResponseSPxmlns="http://schemas.microsoft.com/BizTalk/2003"></ResponseSP>
    If stroed procedure is returning some int value on success then how can i catch in orchestration..
    Pls Advise..
    Thanks
    Sandeep
    Monday, July 27, 2009 10:01 AM

All replies

  • what do you mean by catch? in orchestration you need to create request response port stored procedure response can be received atreceive shape and you can process the response message as you want, can you explain properly what you want to handle
    KiranMP
    Monday, July 27, 2009 12:23 PM
  • Hi,
    I mean when we have SP which return some int value on suppose success of update..then i want to catch this int value in orchestration ..when i do add generated items ..then after giving SP name i hit generate ..so by this SQL SP schema is added to BizTalk..now if we see in schema the Response node have on Sucess element..
    And when i run the solution i only get the balnk Xml as mention above post..

    Why can't i receive that int value in response...

    Thanks
    Sandeep

    Monday, July 27, 2009 1:27 PM
  • ok when creating schema you need to pass correct parameters to stored procedure inorder to get response elements in schema, however even if you have success as element you should get response correctly so check if your stored procedure is returning the value correctly.
    KiranMP
    Monday, July 27, 2009 2:12 PM
  • Hi Kiran,
    Thanks for your reply...
    So that mean when i creating schema by using add generated items ..i need to pass some value ..it could be anything..
    and will i have to tick the check box...
    Secondly...
    I SP i have insert statement and after that wrote..
    if exists(select * from table_1 where id = @id)
    begin
    return 1
    end
    else
    begin
    return 2
    end
    ..
    So when i execute this SP in SQL i get 1 if (IF EXISTS) statement is true..

    So why can't i get vaue when i dump response message of SQL to a folder...

    Thanks
    Sandeep
    Monday, July 27, 2009 4:52 PM
  • Hi,

    You can try the WCF-SQL adapter.

    The response schema genrated by  WCF-SQL adapter contains the integer response returned by the SP.

    It also contains a lot of feature improvemennts as compared to the native SQL adapter.


    You can download it freely from

     http://www.microsoft.com/downloads/details.aspx?FamilyID=CEB0BB34-F9D2-4D14-8BBE-0311D227B091&displaylang=en

    Tuesday, July 28, 2009 7:10 AM
  • Hi Rohit,
    Can't i able to use SQL Adapter..as for WCF SQL adapter i need .net3.5 SP1 and currently in production i am using .net 2.0

    Is SQL adapter is not capable of giving response message in int ..

    Thanks
    Sandeep
    Tuesday, July 28, 2009 7:16 AM
  • sandeep the SQL adapter while creating schema first executes your stored procedure and then roleback the same so when you doesnt pass correct values as paramters stored procedure may fail and adapter doesnt know what response elements should it create so it just creates success element.

    secondly in your stored procedure you are not returning proper xml so it may not retuen proper response to biztalk try below

    Decalre responseval as int

    if exists(select * from table_1 where id = @id)
    begin
    set responseval =1
    end
    else
    begin 
    set responseval = 2
    end

    select @responseval  as response for xml raw

    ..
    KiranMP
    Tuesday, July 28, 2009 9:12 AM
  • Hi,
    Thanks for your reply..
    The whole concept for me to catch response of SQL as i want to process further after i get confirmation from SQL ..
    so instead of modifying Stored procedure to get some int value and then using Xpath in orchestartion catch the value and then process further..

    Instead of all these is there any way so that the Xml i am getting as response of SQL which is just blank Xml(there is Success element in Resposne Schema)

    Thanks
    Sandeep

    Thursday, July 30, 2009 6:10 AM
  • Sandeep,

    If I understand you correctly, you want to send a request to SQL to execute a stored procedure and get the return value (such as an int value) inside the orchestration.  You can use this article as a starting point:

    http://www.codeproject.com/KB/biztalk/usingsqladapter.aspx

    Then, at the end of your procedure place a statement like

    select @a = 'your return value'

    and generate all schemas and that should give you the int in the output which you can extract with the map or a direct XPath.
    Will this help? 
    Thursday, September 17, 2009 6:20 PM
  • Sandeep,

    If I understand you correctly, you want to send a request to SQL to execute a stored procedure and get the return value (such as an int value) inside the orchestration.  You can use this article as a starting point:

    http://www.codeproject.com/KB/biztalk/usingsqladapter.aspx

    Then, at the end of your procedure place a statement like

    select @a = 'your return value'

    and generate all schemas and that should give you the int in the output which you can extract with the map or a direct XPath.
    Will this help? 

    Hi sandeep what pipeline did u use on the response part in your port which is supposed to receive this integer you get from Success variable??
    Wednesday, January 12, 2011 3:40 PM
  • I missed out the response part... That's where i went wrong...... :)
    • Proposed as answer by Darcia Friday, June 3, 2011 2:50 PM
    Monday, January 31, 2011 10:29 AM