The AddBatch() method

Brian DonahueBrian Donahue Posts: 6,590 New member
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" <[email protected]> wrote in message
news:[email protected]
> 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
>

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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" <[email protected]> wrote in message
    news:[email protected]
    > 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" <[email protected]> wrote in message
    > > news:[email protected]
    > >
    > >>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
    > >>
    > >
    > >
    > >
    >
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi 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" <[email protected]> wrote in message
    news:wPO6cLUWEHA.10[email protected]
    > 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" <[email protected]> wrote in message
    > > news:[email protected]
    > >
    > >>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" <[email protected]> wrote in message
    > >>>news:[email protected]
    > >>>
    > >>>
    > >>>>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.