If you kill a job's process, is everything the job has
done rolled back ie is the whole job run in a
transaction? What about if the job used cursors?
TIA,
CB
No implicit transactions are created for jobs or job steps. It is entirely
your responsibility to handle transactions within a step and transactions
cannot span multiple steps.
If you need this functionality, consider using a multi-step DTS package.
Hope this helps.
Dan Guzman
SQL Server MVP
"CB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
> If you kill a job's process, is everything the job has
> done rolled back ie is the whole job run in a
> transaction? What about if the job used cursors?
> TIA,
> CB
|||Dan,
it appears that multi-step DTS package would also not do the trick. In
fact, even within a step in a multiple or single step DTS package, the
commit of transactions still obeys the general rule -- unless you define
explicitely, individual implicite transactions still would commit. Wouldn't
you agree?
Quentin
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> No implicit transactions are created for jobs or job steps. It is
entirely
> your responsibility to handle transactions within a step and transactions
> cannot span multiple steps.
> If you need this functionality, consider using a multi-step DTS package.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "CB" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a4101c4c1ae$d60fda90$a401280a@.phx.gbl...
>
|||It is true that you need to explicitly issue a BEGIN TRAN and COMMIT (or
ROLLBACK) within the DTS package. However, unlike a SQL Agent job, the
steps within a DTS package can share the same database connection and
transaction. You can specify a DTS workflow so that the steps run in the
desired order. For example, you can create the following Execute SQL Tasks
using the same SQL Connection:
Execute SQL Task1:
BEGIN TRAN
Execute SQL Task2:
DELETE FROM MyTable
WHERE MyKey = 1
Execute SQL Task3:
INSERT INTO MyTable (MyKey)
VALUES(1)
Execute SQL Task4:
COMMIT
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:uFFeJvbwEHA.3448@.TK2MSFTNGP10.phx.gbl...
> Dan,
> it appears that multi-step DTS package would also not do the trick. In
> fact, even within a step in a multiple or single step DTS package, the
> commit of transactions still obeys the general rule -- unless you define
> explicitely, individual implicite transactions still would commit.
> Wouldn't
> you agree?
> Quentin
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmOSNCbwEHA.3624@.TK2MSFTNGP09.phx.gbl...
> entirely
>
sql
