I have an orchestration, which receives a message that contains rows to
insert into SQL Server. I add to my pipeline an XML disassembler that
separates the rows into separated messages. Next I’m using Sql adapter to
insert the rows into my database, after that I wand to transfer SQL adapter
response into a done folder.
In addition the insertion to the DB and sending the response must commit
together or rollback together. So I thought to put both of them in an atomic
transaction thinking that if the insert command will succeed and the sending
will fail both will rollback, until I crossed through this article
http://blogs.msdn.com/georgenenc/ar.../15/184191.aspx - it says
that the SQL adapter won’t do rollback and therefore I’ll need to delete
 the
row that was insert- but regardless the extra work to do so – what if the
deletion command fails – does then it’s promised to rollback everything?
 How
do I delete the message if I have a running sequence in the DB, which I have
no control of?  More then that – I’m planning on writing another
orchestration that pops up when row is inserted to this table, and I have no
desire that this process will start until all committed in the previous
process.
So my question is – is there a way to start a transaction within the
database so I’ll do commit or rollback manually?
B.t.w another question will be – is the XML disassembler will work
synchronic? I mean that is it promised that the first document schema in the
envelope will be insert first the second – second and further?

Thanks for any help

Daniel
Email:B.Daniel@superderivatives.com

[ Post a follow-up to this message ]

AFAIK the only way to get automatic rollback would be to use a .NET
component that derives from ServicedComponent to actually implement the
databse logic rather than using the SQL Send Adapter.   If the orchestration
transaction fails, the Database objects in the ServicedComponent should be
automatically enlisted in that transaction and they should rollback.

Jon Flanders
http://staff.develop.com/jfland/

"daniel007" <daniel007@discussions.microsoft.com> wrote in message
news:D43BC00A-B851-473E-A708-4CC5C8F4E698@microsoft.com...
> I have an orchestration, which receives a message that contains rows to
> insert into SQL Server. I add to my pipeline an XML disassembler that
> separates the rows into separated messages. Next I'm using Sql adapter to
> insert the rows into my database, after that I wand to transfer SQL
adapter
> response into a done folder.
> In addition the insertion to the DB and sending the response must commit
> together or rollback together. So I thought to put both of them in an
atomic
> transaction thinking that if the insert command will succeed and the
sending
> will fail both will rollback, until I crossed through this article
> http://blogs.msdn.com/georgenenc/ar.../15/184191.aspx - it says
> that the SQL adapter won't do rollback and therefore I'll need to delete
the
> row that was insert- but regardless the extra work to do so - what if the
> deletion command fails - does then it's promised to rollback everything?
How
> do I delete the message if I have a running sequence in the DB, which I
have
> no control of?  More then that - I'm planning on writing another
> orchestration that pops up when row is inserted to this table, and I have
no
> desire that this process will start until all committed in the previous
> process.
> So my question is - is there a way to start a transaction within the
> database so I'll do commit or rollback manually?
> B.t.w another question will be - is the XML disassembler will work
> synchronic? I mean that is it promised that the first document schema in
the
> envelope will be insert first the second - second and further?
>
> Thanks for any help
>
> Daniel
> Email:B.Daniel@superderivatives.com
>

[ Post a follow-up to this message ]