What is the official solution for resetting a sequence in DF? For example, @nextval(thissequence,1)@ will remember the last generated value between publishes, which can be very handy. We would also like the ability to reset this number back to the starting value. We tried deleting the dbo.gtseq_thissequence reference in the (MSSQL) GTRepo as a pre-publish action, but this produced an error until we ran it a second time (which did reset the value).
Instead of dropping the dbo.gtseq_thissequence table, try using this SQL as a pre-publish action against the Test Data Repository:
DBCC CHECKIDENT('gtseq_thissequence', RESEED, 0)
The last parameter is the new "reset" value for the sequence. In this case, the next value that the sequence uses will be 1 and so on.
You can find more information about DBCC CHECKIDENT command at this link: DBCC CHECKIDENT (Transact-SQL)
Also, I confirmed with Grid-Tools that they do not have any other official solution to reset sequences at this time. Hope this helps.
I couldn't find gtseq_variablename table in gtrep to DBCC CHECKIDENT query. But I could reset my nextval seq variable value from following workflow.
Login Gtrep using MS SQL server management studio -> Go to programmability folder inside gtrep ->Click on sequences folder -> It will show up list of sequences variable list -> Click on required sequence variable -> Click on the check box on Restart sequence.
Also the ALTER SEQUENCE statement can be used to reset the start value.ALTER SEQUENCE <schema_name.sequence_name> RESTART WITH <desired_value>
Does Sameer's answer resolve this question? If so, please mark this as answered. If not, can you provide further details?