Call stored procedure in separate session in SQL Server

Question

I tried calling a SQL Server stored procedure in another session.

This is my code:

CREATE PROCEDURE [dbo].[CI_AdHoc_PrepareJob]
(
    @JobName NVARCHAR(50),
    @StepName NVARCHAR(50),
    @Value NVARCHAR(MAX)
)
AS
BEGIN
    BEGIN TRAN;

    DECLARE @MyStepName SYSNAME = @StepName + '_' + CAST(NEWID() AS NVARCHAR(36));
    DECLARE @MyCmd NVARCHAR(MAX) = 'EXEC CI_AdHoc_SP @id=1, @value=''' + @Value + '''';

    -- Call helper to create the job step immediately
  EXEC dbo.sp_add_jobstep
    @jobname = @JobName,
    @stepname = @myStepName,
    @subsystem = N'TSQL',
    @command = @MyCmd,
    @on_success_action = 1, -- Quit with success
    @on_fail_action = 2, -- Quit with failure
    @DatabaseName = 'MyDB';


    -- Now start the job
    EXEC msdb.dbo.sp_start_job 
        @job_name = @JobName,
        @step_name = @MyStepName;

    -- Get Job ID (optional)
    DECLARE @JobId UNIQUEIDENTIFIER;
    SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;

    ROLLBACK TRAN;
END

I want that even I do rollback at the end, the stored procedure will run (without the rollback interfere it. It is used for some updates in db i.e.).

How can I do that?

Does running job with parameter - a good solution.

(It may be fine whether there are no parameters for the stored procedure, but I need passing parameter, so I need to call sp_add_jobstep inside the transaction. I didn't find any way doing that otherwise).

The bigger problem here is the huge injection issue. This problem smells like an XY Problem; what are you really trying to achieve here. — Thom A, Commented Apr 20 at 13:24
i.e. a stored procedure that add logs to logs-table in db (just logs). Whether there is any begin tran + rollback, I still want the logs to be inserted into db (it should not be depended on the caller transaction). So, need to create a stand-alone session that can get parameters. I found that possible via jobs (but without any parameters). For using parameters - I just created a job step with the command (with parameters), but it seems also impossible, since it is in the transaction. — Eitan, Commented Apr 20 at 13:51
You can create a job that deletes itself after running, and just do that part in transaction, the running can be done outside — siggemannen, Commented Apr 20 at 15:31
As I mentioned in my code - I ineed created a job and even a step (you cannot pass parameters to a job in its session, so I must create a step as I did). It didn't work, since it is surrounded in "begin tran" + "end tran", so even the step creation in the main transaction (the running of the job is not). I don't know how to solve it. Are there any changes in the code, you can refer to? — Eitan, Commented Apr 20 at 15:51
in pseudo code: begin tran create_job_and_params_that_deletes_itself commit tran; exec start_job <your_new_job> — siggemannen, Commented Apr 20 at 16:07

Eitan · Accepted Answer · 2025-04-23 10:52:46Z

There is a solution for that.

Indeed jobs are run on separated session.

The problem, that you cannot pass parameters to the stored procedure (while they are kept in other session). i.e. whether you add in a temporary table and do rollback at the end.

Save the parameters in a temporary file.
Add a stored procedure, that can add data into the temporary file and call to the job.
Add a stored procedure that the job can call (i.e. the latest file found).
- In that stored procedure - run sql with the parameters read from the file.

For that solution - any call to the special stored procedure (that calls the job) won't be interfered by any transactions.

Collectives™ on Stack Overflow

Call stored procedure in separate session in SQL Server

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related