21
Views
1
Comments
How to reseed Row IDs in Outsystems
Question

I have a table that will be truncated nightly  and I would like to set the ID to begin at 1000 when the truncate occurs.   I have tried getting the physical table name from the Entity Entity but when I try to run the SQL I get an error indicating that the OSUSR prefix is reserved.


DBCC CHECKIDENT (''@TableName'', RESEED, 1000)


I'm a bit stuck, I'm using the ID to generate a unique AlphaNumeric key  (base-36 encoding that along with the current juliandate)  but I can't figure out how to get this ID set back to 1000 when the table gets truncated each night.

mvp_badge
MVP
Rank: #39

Hi Josh,

There are ways to go around the OSUSR prefix validation, but it's very rarely a good idea. Besides being inconsistent across OutSystems versions, they're frequently patched out since this is a security feature. You're essentially fighting the Advanced Query parser. 

For the sake of a full answer, this currently works in personal environments, but I do not recommend it for production code:

So long as the OSUSR prefix is broken up, it won't trigger the validation. 


Instead of resorting to these workarounds, wouldn't it be possible to find another way to generate your unique key? Using a volatile database ID in order to create your unique values doesn't seem very resilient. If you can share a bit more about your use case, I'm sure we could come up with other alternatives.