none
WCF-SQL adapter select data using stored proc with parameter RRS feed

  • Question

  • I'm moving an application from bts2006 to bts2013. from sql to wcf-sql.  I now have a stored proc that is called by 3 other stored procs (same data is selected but with another parameter) so I now have 3 receive locations of which the polling result is an xml with the same schema. so 3 receive locations, 1 schema

    When moving to wcf-sql I thought about using typed polling (all the data is in one table so not a complicated structure. But I don't want to have 3 different schema's , which would be the case if I understand correctly.(InboundId is part of the schemaname)

    In what way would you move the old sql-solution to wcf-sql?  Is trying to use typed polling wrong? Better to use another kind of polling?


    Kind regards Isabelledc

    Tuesday, May 24, 2016 9:04 AM

Answers

  • Hi

    I think this should help in your scenario-

    So basically create only a single schema(as your resultset from SQL is the same) with a single inboundId. But, append a dummy parameter in each of your 3 ReceiveLocations(that will give you a unique URI for each of your 3 Receive Locations). 

    You have to manually append a bogus parameter value to the uri as below - 

    mssql://server/instance/DierbergsFTD?inboundId=uspFTDOrdersToProcessGet&MyUriDifferentiator=AM

    mssql://server/instance/DierbergsFTD?inboundId=uspFTDOrdersToProcessGet&MyUriDifferentiator=PM

    The MyUriDifferentiator parameter values are never used by the adapter but this will get you past the creation of the receive locations pointing to the same stored procedure. You should probably use a bogus parameter that's more descriptive and conveys the purpose of the receieve location 

    https://www.gittprogram.com/question/1458027_same-storeproc-in-different-receive-location-in-biz10-with-different-uri-in-wcf-custom-doesn-and-39-t-work.html

    Thanks Arindam


    Wednesday, May 25, 2016 6:46 AM
    Moderator
  • When moving to wcf-sql I thought about using typed polling (all the data is in one table so not a complicated structure. But I don't want to have 3 different schema's , which would be the case if I understand correctly.(InboundId is part of the schemaname)


    Kind regards Isabelledc

    You don't need to generate three different schemas. Just generate single schema with unique InboundID. Yes you have to provide the InboundID as it mandatory parameter for Typed polling. 

    Basically what InboundID does is it acts like a identifier that you add to the connection URI to make it unique. You must provide this connection parameter if you want to generate metadata for the TypedPolling inbound operation. Also, in a BizTalk application, if you have multiple receive locations polling the same database, the inbound ID makes the connection URI unique.

    But the problem is that you will face issue while creating receive location with single InboundID. Creating receive locations with same Uri is not allowed. BizTalk doesn’t allow you to create multiple RLs in the same BizTalk application with the same URI. From the BizTalk point of view, it’s like having 2 RLs competing in the same BizTalk application for the same incoming messages.

    To cater that you can  pass an extra dummy parameter like temp for each receive location with different value so that URI will be different, it function same as InboundID.

    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=Novalue
    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=1
    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=2

    Have a look here for reference, this is for WCF-SQL adapter and the approach logic is same, passing parameter to have different URI.

    Don't you worry about the URI, it will be still a valid working URI even after adding the dummy parameter.


    Rachit Sikroria (Microsoft Azure MVP)

    Wednesday, May 25, 2016 8:07 AM
    Moderator

All replies

  • Hi Isabelledc,

    Typed polling is the correct way, Please have a look into below article for how to use wcf-sql adapter,

    https://lajak.wordpress.com/2011/07/17/biztalk-configure-wcf-sql-adapter-for-outbound-operation-using-typed-stored-procedure/


    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.

    Wednesday, May 25, 2016 4:31 AM
    Moderator
  • Hi

    I think this should help in your scenario-

    So basically create only a single schema(as your resultset from SQL is the same) with a single inboundId. But, append a dummy parameter in each of your 3 ReceiveLocations(that will give you a unique URI for each of your 3 Receive Locations). 

    You have to manually append a bogus parameter value to the uri as below - 

    mssql://server/instance/DierbergsFTD?inboundId=uspFTDOrdersToProcessGet&MyUriDifferentiator=AM

    mssql://server/instance/DierbergsFTD?inboundId=uspFTDOrdersToProcessGet&MyUriDifferentiator=PM

    The MyUriDifferentiator parameter values are never used by the adapter but this will get you past the creation of the receive locations pointing to the same stored procedure. You should probably use a bogus parameter that's more descriptive and conveys the purpose of the receieve location 

    https://www.gittprogram.com/question/1458027_same-storeproc-in-different-receive-location-in-biz10-with-different-uri-in-wcf-custom-doesn-and-39-t-work.html

    Thanks Arindam


    Wednesday, May 25, 2016 6:46 AM
    Moderator
  • When moving to wcf-sql I thought about using typed polling (all the data is in one table so not a complicated structure. But I don't want to have 3 different schema's , which would be the case if I understand correctly.(InboundId is part of the schemaname)


    Kind regards Isabelledc

    You don't need to generate three different schemas. Just generate single schema with unique InboundID. Yes you have to provide the InboundID as it mandatory parameter for Typed polling. 

    Basically what InboundID does is it acts like a identifier that you add to the connection URI to make it unique. You must provide this connection parameter if you want to generate metadata for the TypedPolling inbound operation. Also, in a BizTalk application, if you have multiple receive locations polling the same database, the inbound ID makes the connection URI unique.

    But the problem is that you will face issue while creating receive location with single InboundID. Creating receive locations with same Uri is not allowed. BizTalk doesn’t allow you to create multiple RLs in the same BizTalk application with the same URI. From the BizTalk point of view, it’s like having 2 RLs competing in the same BizTalk application for the same incoming messages.

    To cater that you can  pass an extra dummy parameter like temp for each receive location with different value so that URI will be different, it function same as InboundID.

    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=Novalue
    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=1
    mssql://DBName//BTSCustomDB?InboundId=TradingPartnerInfo&temp=2

    Have a look here for reference, this is for WCF-SQL adapter and the approach logic is same, passing parameter to have different URI.

    Don't you worry about the URI, it will be still a valid working URI even after adding the dummy parameter.


    Rachit Sikroria (Microsoft Azure MVP)

    Wednesday, May 25, 2016 8:07 AM
    Moderator
  • Thank yo for the 'dummy'-parameter tip! Didn't know that, it will certainly come in handy ....

    Although I must admit I used another solution that also works.

    I moved to XMLPolling and now get everything I need in 1 go=1 RL . The parameter I need (that was the reason for the different RL's) is now part of the XML that gets returned in kind of a header section, with records as before in a detail-section.  Created an envelope that splits the incoming message ...et voila....this works as well :-) 


    Kind regards Isabelledc

    Wednesday, May 25, 2016 11:54 AM