Sign in
Microsoft.com
 
Resources for IT Professionals
 
 
 
Resources for IT Professionals > Forums Home > BizTalk Server Forums > BizTalk Server Adapters and Adapter Pack > How to realize a join with LOB adapters
Ask a questionAsk a question
Search Forums:
 

AnswerHow to realize a join with LOB adapters

  • Thursday, October 22, 2009 12:07 PMMrks83 Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Hi everybody,

    I want to realize a join or merge with the help of the WCF-SQL (or any other LOB) adapter based on some filter values. Is it possible to get such a behavior with the default feature set?

    The simplest select statement in my mind would be:
    select * from tableA, tableB where tableA.tableBId = tableB.id and tableA.Name = "foobar"

    Do you have suggestions?

    Thx, rgds
    Mrks
     

Answers

  • Thursday, October 22, 2009 3:49 PMBen Cline1MVP, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote

    So if you want to do a select with a join with WCF-SQL, I would use a stored procedure or view that does the join and refer to this in the add adapter service for generating the schemas.

    I am doing this on one of my projects.

    Thanks, 

    If this answers your question, please use the "Answer" button to say so | Ben Cline
     

All Replies

  • Thursday, October 22, 2009 3:49 PMBen Cline1MVP, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote

    So if you want to do a select with a join with WCF-SQL, I would use a stored procedure or view that does the join and refer to this in the add adapter service for generating the schemas.

    I am doing this on one of my projects.

    Thanks, 

    If this answers your question, please use the "Answer" button to say so | Ben Cline
     
  • Wednesday, October 28, 2009 3:03 PMMrks83 Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    I think this is the cleanest solution. However, having only read access to the database, the solution does not help since you can not create a view on your own. I think the old SQL adapter with SQLXML seems to be more powerful at this point.
     
  • Thursday, October 29, 2009 8:08 AMAndrew_ZhuMSFT, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Hi, Mrks83

    ->"the solution does not help since you can not create a view on your own. I think the old SQL adapter with SQLXML seems to be more powerful at this point."
    You can use stored procedure to create a view.

    Regards
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
     
  • Friday, October 30, 2009 7:59 AMMrks83 Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Hi Andrew,

    just to clarify. Do you mean, that it is possible to create a view with a stored procedure having read access to database only?
    But anyway, in my opinion it should be a feature of the WCF SQL adapter. The join with the help of a view (that is some kind of changing of the "interface") is just a work around to get a feature that exists in the old SQL adapter. Correct me, if I'm mistaken.

    Regards