Run parallel transaction while executing code in another session

Question

Is there any possibility to run update at some specific point of time, but in different/parallel session? In the provided example I want some specific update to be run at the time when I run WAITFOR. Currently I have this WAITFOR block to have some time to switch to another SSMS (or other tool) window/tab and run update while it's waiting for 10 secs. Logically the only thing is needed to be done, is that transaction started at this point of time.

EXEC dbo.p_sync_from_accounts_ext_test @enable_snapshot_isolation = 1
                                     , @run_update_flag = NULL
                                     , @run_wait_for_10 = NULL
                                     , @acc = @acc;

WAITFOR DELAY '00:00:10'; -- Execute update in parallel transaction
-- table update should be performed in that parallel transaction

EXEC dbo.p_finish_sync_attributes;

Answer 1

Yes, you can do it.

Method 1: loop-back linked server (linked server that points to your current server) that does not have DTC enabled. Call your SP from your linked server.

Methd 2: create an SQL Server Job and start the job programmatically.

Note that in the first case your update statement must be included in an SP. In the second case it is advisable but not necessary.