Options
The AddBatch() method
Brian Donahue
Posts: 6,590 Bronze 1
Hi Chris,
That's exactly right. AddBatch will append a batch of SQL to the end of
the ExecutionBlock. Batch markers aren't added automatically; you'd need to
use the AddBatchMarker method to append a GO onto the end. The transactional
plumbing is done elsewhere, so you'd need to add those bits in yourself, if
you find you need them.
Regards,
Brian Donahue
Red Gate Technical Support
"Chris Tybur" <ctybur@infinitevisions.com> wrote in message
news:q3LV1JlUEHA.1512@server53...
> I presume ExecutionBlock.AddBatch() adds a T-SQL batch to the generated
> SQL stored in the ExecutionBlock object. A couple of questions: does it
> add the script to the beginning or end of the script? Does it insert a
> batch end marker (GO)? Does it put the same 'plumbing' code after the
> batch to allow for a rollback if the batch fails?
>
> Thanks.
>
> Chris
>
That's exactly right. AddBatch will append a batch of SQL to the end of
the ExecutionBlock. Batch markers aren't added automatically; you'd need to
use the AddBatchMarker method to append a GO onto the end. The transactional
plumbing is done elsewhere, so you'd need to add those bits in yourself, if
you find you need them.
Regards,
Brian Donahue
Red Gate Technical Support
"Chris Tybur" <ctybur@infinitevisions.com> wrote in message
news:q3LV1JlUEHA.1512@server53...
> I presume ExecutionBlock.AddBatch() adds a T-SQL batch to the generated
> SQL stored in the ExecutionBlock object. A couple of questions: does it
> add the script to the beginning or end of the script? Does it insert a
> batch end marker (GO)? Does it put the same 'plumbing' code after the
> batch to allow for a rollback if the batch fails?
>
> Thanks.
>
> Chris
>
This discussion has been closed.
Comments
They sould be rolled back as long as they're between a BEGIN TRAN and an
END TRAN...
Regards,
Brian Donahue
Red Gate Technical Support
"Chris Tybur" <ctybur@infinitevisions.com> wrote in message
news:e3aCvXLWEHA.1084@server53...
> Since the transactional code isn't automatically added, does that mean
> any batches I add won't be included in the transaction for the main
> script? My goal is to include some custom SQL code as part of the main
> script, and if it happens to fail then I'd like every change made prior
> to be rolled back.
>
> Chris
>
> Brian Donahue (Red Gate) wrote:
> > Hi Chris,
> >
> > That's exactly right. AddBatch will append a batch of SQL to the end
of
> > the ExecutionBlock. Batch markers aren't added automatically; you'd need
to
> > use the AddBatchMarker method to append a GO onto the end. The
transactional
> > plumbing is done elsewhere, so you'd need to add those bits in yourself,
if
> > you find you need them.
> >
> > Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support
> >
> > "Chris Tybur" <ctybur@infinitevisions.com> wrote in message
> > news:q3LV1JlUEHA.1512@server53...
> >
> >>I presume ExecutionBlock.AddBatch() adds a T-SQL batch to the generated
> >>SQL stored in the ExecutionBlock object. A couple of questions: does it
> >>add the script to the beginning or end of the script? Does it insert a
> >>batch end marker (GO)? Does it put the same 'plumbing' code after the
> >>batch to allow for a rollback if the batch fails?
> >>
> >>Thanks.
> >>
> >>Chris
> >>
> >
> >
> >
>
It's not possible to modify an ExecutionBlock that's produced by
SQLCompare.Engine in this version of Toolkit. What you can do, though, is
create your own instance of an ExecutionBlock and add batches to that. Then
you can run (or script) those execution blocks in order to append SQL either
to the end or the beginning of the migration script that SQL Compare had
produced for you.
But as for adding batches to SQL Compare's ExecutionBlock, that's not
possible at this time.
Regards,
Brian Donahue
Red Gate Technical Support
"Chris Tybur" <ctybur@infinitevisions.com> wrote in message
news:wPO6cLUWEHA.1084@server53...
> I'm afraid I wasn't clear enough: I want my custom SQL code to run
> *inside* the transaction for the main script. I can run my stuff in a
> separate transaction, but then if the main script runs OK and my code
> fails, I can only rollback the changes made by my code, not the changes
> made by the main script. I could just restore the database in those
> cases, but that's something I'd like to avoid if possible.
>
> Chris
>
> Brian Donahue (Red Gate) wrote:
> > Hi Chris,
> >
> > They sould be rolled back as long as they're between a BEGIN TRAN
and an
> > END TRAN...
> >
> >
> > Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support
> >
> > "Chris Tybur" <ctybur@infinitevisions.com> wrote in message
> > news:e3aCvXLWEHA.1084@server53...
> >
> >>Since the transactional code isn't automatically added, does that mean
> >>any batches I add won't be included in the transaction for the main
> >>script? My goal is to include some custom SQL code as part of the main
> >>script, and if it happens to fail then I'd like every change made prior
> >>to be rolled back.
> >>
> >>Chris
> >>
> >>Brian Donahue (Red Gate) wrote:
> >>
> >>>Hi Chris,
> >>>
> >>> That's exactly right. AddBatch will append a batch of SQL to the
end
> >
> > of
> >
> >>>the ExecutionBlock. Batch markers aren't added automatically; you'd
need
> >
> > to
> >
> >>>use the AddBatchMarker method to append a GO onto the end. The
> >
> > transactional
> >
> >>>plumbing is done elsewhere, so you'd need to add those bits in
yourself,
> >
> > if
> >
> >>>you find you need them.
> >>>
> >>>Regards,
> >>>
> >>>Brian Donahue
> >>>Red Gate Technical Support
> >>>
> >>>"Chris Tybur" <ctybur@infinitevisions.com> wrote in message
> >>>news:q3LV1JlUEHA.1512@server53...
> >>>
> >>>
> >>>>I presume ExecutionBlock.AddBatch() adds a T-SQL batch to the
generated
> >>>>SQL stored in the ExecutionBlock object. A couple of questions: does
it
> >>>>add the script to the beginning or end of the script? Does it insert a
> >>>>batch end marker (GO)? Does it put the same 'plumbing' code after the
> >>>>batch to allow for a rollback if the batch fails?
> >>>>
> >>>>Thanks.
> >>>>
> >>>>Chris
> >>>>
> >>>
> >>>
> >>>
> >
> >
>