1

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;
Dmitrij Kultasev
  • 5,447
  • 5
  • 44
  • 88
  • https://stackoverflow.com/a/10858305/11683? – GSerg Jun 11 '20 at 07:34
  • No, stored procedures, nested EXEC statements have nothing to do with that question – Dmitrij Kultasev Jun 11 '20 at 07:37
  • Read the answer, not the question. `FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;', ...)` – GSerg Jun 11 '20 at 07:39
  • I've tried to put UPDATE statement using `OPENROWSET` before `WAITFOR DELAY` statement and the result was the same as if I was using normal UPDATE. I understand that there is lack of info, I'll try to provide more info about the exact task/problem I'm trying to solve. To be short, it is related to the `rowversion` values, generated during update with combination of the temporal tables' interval columns values. – Dmitrij Kultasev Jun 11 '20 at 08:38
  • @DmitrijKultasev, have you been able to resolve your issue? – Alex Jun 15 '20 at 00:58

1 Answers1

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.

Alex
  • 4,885
  • 3
  • 19
  • 39