reset db table auto number for id

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:

DBCC CHECKIDENT ('tablename', RESEED, 1)

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.

Tiago Gafeira wrote:

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};

Hi Jason,

What is the DBMS you are using, SQL Server?