CODEWITHSUNDEEP
sql-server
Joe
Joe

Reputation: 468

When a SQL job is started within a transaction, will the steps in the job be a part of the transaction?

I run a sql statement in a stored procedure like so:

...
CREATE PROCEDURE my_proc
AS
BEGIN

BEGIN TRAN

...do some work

EXEC msdb.dbo.sp_start_job N'MyJob';

...do some work, maybe rollback if some criteria is not met

COMMIT TRAN
...
END

Are the steps in 'MyJob' a part of this transaction? Or do they occur independently of it?

For a specific example: 'MyJob' deletes records from table 'MyData'. Then some logic in the stored proc ends up rolling back the transaction instead of committing it. Will the records in 'MyData' that were deleted by the job be deleted?

Upvotes: 1

Views: 910

Answers (1)

Thom A
Thom A

Reputation: 95827

sp_start_job starts the job, and then anything that job does is asynchronous. This means that they are in an entirely separate session. If you were to rollback the transaction, then the job still would have been started, and any actions the job performed will be unaffected.

Upvotes: 1

Related Questions