Has anyone used advanced sql to reset the auto increment id number for a db table? What I want to do is be able to delete all entries from a table then run this command so that the next save will be id # 1.
Hi Jason,
In the cloud, the default user used in the runtime DB connection does not have permissions for that operation.
Cheers,
Tiago.
Tiago Gafeira wrote:
Tiago - we have an on prem installation and we can bump the permissions of the default user as this will ONLY be used on our test server.
In that case you should have no problems to perform the operation. Just use regular OutSystems notation.
I think the problem is that to reset the identity column (which is what an autonumber Attribute is), you need to specify the table name between quotes, like this:
DBCC CHECKIDENT ('tablename', RESEED, 1)
I haven't tried it myself, but I would probably get the physical table name from the Entity System Entity, and create a SQL query with an Expand Inline Input Parameter that contains the physical table name including the single quotes.
Kilian Hekhuis wrote:
Should do it.
Jason Herrington wrote:
Back to the original problem. A TRUNCATE should do exactly what you need. Both deleting the data and resetting the autonumber.
How would you do truncate through OutSystems - assuming the permissions are bumped up for the default user?
If the user has the right permissions, just use a regular TRUNCATE statement.
TRUNCATE TABLE {EntityName};
What is the DBMS you are using, SQL Server?
Hi,
Related question: https://www.outsystems.com/forums/discussion/74635/what-will-happen-after-the-id-attribute-get-full/ I have a question that is related with this post. What will happen after the auto biginteger number get full of usage (After a ton of insert and delete operations get its maximum value) will be reset or crash the application?
Hi Savvas,
You are opening a thread of 2 years ago. The better approach is to create a new topic, and if needed share a link to this old discussion.
Kind regards,
Daniel