Why the following cmds in sequence do not work

Question

Post reply

Why the following cmds in sequence do not work

hoa.nguyen 64423

Mr or Mrs. 500

Points: 547
More actions
February 6, 2026 at 3:01 am

#4730841

I need to delete rows from both the temporal main and history tables.
The temporal main table can get deleted without issues.
For the temporal history tables, I code to turn system versioning off, issue the Delete, then turn system versioning back on.
If I execute manually each of the below cmds one at a time, they all work without an error.
But if I execute the whole 3-line unit of code, it errors.
I have hundreds of temporal history tables to delete so I cannot be executing the cmds one at a time...
Appreciate your attention and help.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

hoa.nguyen 64423 Mr or Mrs. 500 Points: 547 More actions · Answer 1

Serializing made no difference... Manual run of each cmd takes sub-seconds...

Untitled2

Phil Parkin SSC Guru Points: 247224 More actions · Answer 2

Try putting 'GO' after each statement.

ALTER ...
GO
DELETE...
GO

frederico_fonseca SSCoach Points: 16307 More actions · Answer 3

see this link https://learn.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables?view=sql-server-ver17

scroll down to "Use custom cleanup script approach"

hoa.nguyen 64423 Mr or Mrs. 500 Points: 547 More actions · Answer 4

This works. I will have to take the dynamic sql approach as coded on the article. Thank you very much!

hoa.nguyen 64423 Mr or Mrs. 500 Points: 547 More actions · Answer 5

I am coding an sproc so I cannot code many GOs. Thanks for helping!

carwalk Grasshopper Points: 23 More actions · Answer 6

The reason those three commands don’t work when you run them all together in one shot, even though each runs fine when executed one-by-one, is that SQL Server treats batches as units and some statements (like altering system versioning on a temporal table) have to be separated into their own batch; if you send them all in “one sequence” without batch separators the server still compiles them together, which can cause errors or unexpected behavior. In tools like SSMS you need to break the script into separate batches with GO between the ALTER, the DELETE, and the next ALTER so that each runs independently in the right order, just like running them manually one at a time

hoa.nguyen 64423 Mr or Mrs. 500 Points: 547 More actions · Answer 7

@frederico_fonseca,

Update the MSFT learn article with Dynamic sql allows me to save the sproc successfully at create procedure/alter procedure (aka at definition update) time. However at run time ( aka when I execute the sproc, it failed with the exact same error...) --- it still sees the table as temporal so the system versioning off.

Executed manually in 2 separate sprocs (1 that deletes the main temporal tables and turns off versioning , the follow on sproc can delete the temporal history OK as it does not see them as temporal anymore). It is a kluge.

Any comments or suggestions from anyone will be much appreciated.