Re: One message should result into inserts into several sql-tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank You for Your reply.

But I'll have to use several stored procedures it seems.
Since there is a header-line structure (where line is repeating)

It works if I have a schema with 2 nodes , 1 for inserting headerinfo and
and another one for lines. (that schema I have manually put together
cutting/pasting from generated schemas:-)
I can then use the unbounded feature on the insertline-node to insert several
lines at once without having to loop in some way.
Don't know how I could do that if both inserts are in the same SP?

Even when using two SP's there seems to be no problem for working
transactionally, if inserting of line fails , header isn't created ... so
seems fine
(even without using transaction in the SP)



Isabelle


"Saravana Kumar [MVP]" wrote:

Create a stored procedure in SQL which will take care of multiple inserts
(you can also make it transactional that way).

Call the stored procedure from BTS using SQL Adapter.

You need to design you schema in the way you capture all the required data.


--
Regards,
Saravana Kumar [MVP - BizTalk Server]
http://www.biztalk247.com/v1/
http://blogs.digitaldeposit.net/saravana/

"isabelledc" <isabelledc@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3B686754-D786-4691-8E2D-FE674565CF65@xxxxxxxxxxxxxxxx
Hello,

we have a project where we will receive one message from application A and
the
receiving application B expects the data it has to process in several
SQL(interface)tables.
(fe headertable, linetable,triggertable,itemtable)

What's the best way to go about this in biztalk 2006 R2?
I haven't used the sql-adapter untill now so I don't know what's possible
or
the best way to use.
Inserting into one table , that I could handle but how to work when
inserts
need to happen in several tables?

(oh by the way , i haven't used sql-adapter but I know I want to work
with
stored procedures as much as possible)

Thanks for your reply.

Isabelle


.

Relevant Pages

  • Re: Problem with Stored Procedure that conatins several select statements
    ... If you test the stored procedures from the SQL Analyser, ... The Problem is that everytime I create a generated schema there is only ... one element in the response node called "Success" and not the two columns ... the final SELECT then the response elements are created as I expected. ...
    (microsoft.public.biztalk.general)
  • Re: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
    ... > How important is to specify the schame (dbo. ... > application may be improved by specifying schema names." ...
    (comp.databases.ms-sqlserver)
  • Re: A philosophical question about inserts
    ... Stored Procedures are the ideal -for all the benefits mentioned above. ... schema from them and have them call stored procedures or views to get the ... DBI/DBD) would prepare/execute: ... The main goal here is to isolate the developer from knowing the database ...
    (comp.databases.ms-sqlserver)
  • Execute Immediate and bind variables
    ... I'm translated SQL Server stored procedures into Oracle. ... The schema name is passed into the stored procedure. ... We concatenate the ... Can I use bind variables in this ...
    (comp.databases.oracle.misc)