Changing/resetting current seed value for auto number entity columns

Changing/resetting current seed value for auto number entity columns

  

Hello,

I'm writing a migration script where I need to persist id values from old tables to be the same in the new tables. After migration, new table id values should continue to auto increment.

I planned to do this with advanced SQL using DBCC checkident command, by resetting auto index for each row I'd insert. It's a heavy SQL, but this is to avoid extra logic changes on top of already massive effort. To my disappointment, I got an error:

Database returned the following error:
Error in advanced query SQL1: User '<OS DB user>' does not have permission to run DBCC CHECKIDENT for object '<NEW TABLE>'.

Apparently DB user does not have enough permissions to do this in OS cloud installation and my question is: how to achieve such a thing?

I need to have this scripted and whole thing to be run in context of one timer, to avoid multiple publish/manual change cycles in each environment where this migration is being run.

Hi Nikko,

The DB user that the platform uses to execute queries at runtime is configured by default to only have permissions for standard access to data, not structure of your tables/database. That's why you cannot change DB tables at runtime, etc... my guess is it also does not allow for T-SQL DBCC.

I don't think there's an easy way around this on a cloud environment. On premises you could ask your DBA to change the privileges of the DB runtime user that OutSystems uses (with all the risks it implies)

Jorge Martins wrote:

I don't think there's an easy way around this on a cloud environment.

By saying that, are you suggesting there is a way? :D

I think the hard solution you are referring to requires me to insert/remove dummy rows until I reach desired "row id - 1", then insert the actual data. This way I have one shot to get it correct per environment before I need to recreate module/tables all over again.

Wash, rinse & repeat.

Sigh.