none
WCF SQL Adapter and stored procedure to generate a Receive Port for Type Polling RRS feed

  • Question

  • Hello

    I have an advanced stored procedure that we have written inside a SQL Server db. We would like to use a Typed Polling receive port to send the results of the SP to BizTalk to start an orchestration. 

    However, when we generate the schema, the WCF SQL adapter generates a send/receive port. I was wondering how the SQL adapter knows to generate a Receive only port instead of the Send/Receive.

    I have attached the procedure below for reference and would appreciate any insight into how the SP needs to look like for the proper schema generation.

    Greg

    SET ANSI_NULLS

    ON

    GO

    SET

    QUOTED_IDENTIFIER

    ON

    GO

    CREATE

    PROCEDURE sp_GetDocCodes

     

    -- Add the parameters for the stored procedure here

     

    @p1

    varchar(250),

    @p2

    VARCHAR(250),

    @p3

    varchar(250),

    @p4

    varchar(250)

    AS

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from

     

    -- interfering with SELECT statements.

     

    SET NOCOUNT ON;

     

    -- Insert statements for procedure here

     

    --SELECT @p1, @p2

    declare

    @commcodes varchar(250

    ),

    @statuscodes

    varchar(250),

    @commcode

    varchar(250),

    @statuscode

    varchar(250),

    @transactionid

    varchar(250),

    @superdocid

    varchar(250),

    @criid

    varchar(250)

     

    Set

    @transactionid = NEWID

    ()

    --get one super_doc_id from the pending records

    select

    top 1 @superdocid = DAS_String_dcf_super_doc_id from dbo.TABLE_dc_cri_student_documents_received where DAS_String_dcf_integration_result is

    null

    --update the available records with a transaction number

    Update

    dbo.TABLE_dc_cri_student_documents_received Set DAS_String_dcf_transaction_id = @transactionid Where DAS_String_dcf_super_doc_id = @superdocid and DAS_String_dcf_integration_result is

    null

    --loop through the records

    While

    (Select Count(*) From dbo.TABLE_dc_cri_student_documents_received Where DAS_String_dcf_super_doc_id = @superdocid and DAS_String_dcf_transaction_id = @transactionid and DAS_String_dcf_integration_result is null) > 0

    Begin

     

    --get one record

     

    Select Top 1 @commcode = DAS_String_dcf_comm_code, @statuscode = DAS_String_dcf_status_code, @criid = DAS_Druid_dcf_cri_doc_id From dbo.TABLE_dc_cri_student_documents_received Where DAS_String_dcf_super_doc_id = @superdocid and DAS_String_dcf_transaction_id = @transactionid and DAS_String_dcf_integration_result is null

     

    --update the result status to tran to drop it out of loop

     

    Update dbo.TABLE_dc_cri_student_documents_received Set DAS_String_dcf_integration_result = 'TRAN' Where DAS_Druid_dcf_cri_doc_id = @criid

     

    select @criid

     

    --gather the commcodes and status codes

     

    if @commcodes <> ''

     

    set @commcodes = @commcodes + ',' + @commcode

     

    else

     

    set @commcodes = @commcode

     

    if @statuscodes <> ''

     

    set @statuscodes = @statuscodes + ',' + @statuscode

     

    else

     

    set @statuscodes = @statuscode

    End

    --return parameter values

    select

    @superdocid 'superdocid', @transactionid 'transactionid', @commcodes 'commcodes', @statuscodes

    'statuscodes'

    SET

    @p1 = @superdocid

    SET

    @p2 = @transactionid

    SET

    @p3 = @commcodes

    SET

    @p4 = @statuscodes

    END

    GO

    Thursday, September 22, 2011 5:58 PM

Answers

  • Hi everyone,

    I made a mistake when I was generating my schema. I was using an outbound operation of the SQL Adapter instead of inbound. Once I generated the schema properly, the pararmeters functioned like I wanted.

     

    Thanks

    Greg

    • Marked as answer by Greg Svitak Monday, October 10, 2011 7:15 PM
    Monday, October 10, 2011 7:15 PM

All replies

  • Hi Greg,

    It is possible see my post on Oracle polling, you can perform similar steps for SQL Server.

    HTH

    Regards,

    Steef-Jan Wiggers
    MVP & MCTS BizTalk Server 2010
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Thursday, September 22, 2011 6:56 PM
    Moderator
  • Hi Steef,

    Thanks for the quick response.

    We have setup Typed Polling before using a table and a basic stored procedure that only did a select in the SP. My problem is that I am not sure how to write the SP properly so the schema generated in a receive only port. Currently, the SP schema generation creates a send receive so the Typed polling will not work.

    Any guidenance would be appreciated..

    Greg

    Thursday, September 22, 2011 7:33 PM
  • I would say that it is becasue your SP has parameters, if you cannot change this SP create another on that hard codes the parameters.
    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.
    Friday, September 23, 2011 1:26 PM
  • Hi everyone,

    I made a mistake when I was generating my schema. I was using an outbound operation of the SQL Adapter instead of inbound. Once I generated the schema properly, the pararmeters functioned like I wanted.

     

    Thanks

    Greg

    • Marked as answer by Greg Svitak Monday, October 10, 2011 7:15 PM
    Monday, October 10, 2011 7:15 PM