locked
Stored Procedure to to get table data From SQL RRS feed

  • Question

  • Hi 

    can anyone tell me how do we get the data from SQL using stored procedure, I tried by  using simple select statement in SP  while generating schema i am not getting the schema fields. here is my SP, 


    CREATE PROCEDURE [dbo].[GetData]


    AS
    BEGIN
    SELECT [ItemID],[FileID],[AppNumber],[ClientName],[AddressLine1],[AddressLine2],[AddressLine3],[City],[StateCode],[PostalCode],[Flag],[OrderNo] from [DatabaseName].[dbo].[FData] where Flag = 'true' 
    END

    I need to generate schema from this stored procedure, Please let me know what changes are required for this.

    Thanks


    panadi



    • Edited by panadi Friday, April 22, 2016 12:40 PM
    Friday, April 22, 2016 12:36 PM

Answers

  • Are you able to browse your SP from the Consume Adapter Service wizard?

    If yes, what happens when you click on SP name and click on Add?


    Thanks Arindam


    Friday, April 22, 2016 12:42 PM
    Moderator

All replies

  • Are you able to browse your SP from the Consume Adapter Service wizard?

    If yes, what happens when you click on SP name and click on Add?


    Thanks Arindam


    Friday, April 22, 2016 12:42 PM
    Moderator
  • Hi arindam

    thanks for the reply,

    Previously i was tried by using Procedures instead of typed pricedures, Now have tried using typed procedure, And able see the fields as two records , one is resultset and second record conatins array of result set.

    Now i am getting the following error in event viewer,

    The adapter "WCF-Custom" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.AdapterException: Input string was not in a correct format.. 

    So could you pls help on this

    Thanks


    panadi

    Friday, April 22, 2016 1:09 PM
  • Hi Panadi

    How are you setting the fields in the WCF-SQL request schema? I would suggest look at the map output(if you are using one), some field validation is failing.


    Thanks Arindam


    Friday, April 22, 2016 1:21 PM
    Moderator
  • Hi Pandi,

    Thank you for posting on MSDN forum.

    Apart from Arindam's suggestion, please have a look into below articles,

    http://tahirhemani.blogspot.in/2010/06/how-to-generate-schema-using-wcf-sql.html

    http://www.codeproject.com/Articles/37808/How-to-invoke-Stored-Procedures-with-the-FOR-XML-c

    http://geekswithblogs.net/bosuch/archive/2010/10/19/biztalk---simple-wcf-tutorial-for-using-a-stored-procedure.aspx


    Thanks,

    If my reply is helpful please mark as Answer or vote as Helpful.

    My blog | Twitter | LinkedIn

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, April 22, 2016 2:45 PM
    Moderator
  • Hi 

    when i generate schema using strongly typed procedure ,i got two toot nodes as Storedprocedure result set0 and array of stored procedure resultset0 , may i know which fields i should use to map with the target schema.

    thanks


    panadi

    Monday, April 25, 2016 1:16 PM
  • Array of ResultSet would just be a sequence/array of ResultSet elements - think of Resultset as a single row from a SQL query output. I would suggest to use Array for the multiple row req/response scenario.

    Thanks Arindam

    Monday, April 25, 2016 1:36 PM
    Moderator
  • Hello Panadi,

    Please use Select Contract type as Service insteade of client. After that you have to click on typed procedure and it will generate schema correctly.

    Thanks

    JB


    JB

    Monday, April 25, 2016 9:19 PM
  • Depends if you want to get data from SQL from Receive Location(also known as polling) - use Contract type as Service in the Consume Adapter Service in this case. This causes the Receive Location to call SQL Server in a PULL model - it actively checks for new data in SQL Server - it's meant for scenarios when BizTalk periodically needs to check if some new record was added/modified in SQL side.

    If however, you want to call a SQL StoredProc from BizTalk Send Port based on some other message that BizTalk received, use Contract type as Client in the Consume Adapter Service in this case. This is what was discussed earlier on the thread.


    Thanks Arindam



    Tuesday, April 26, 2016 2:21 AM
    Moderator