Tip: How to reset the identity column in a sql server database

Tip: How to reset the identity column in a sql server database

  
Hi, I had to reset the identity column to 0 again for one of my applications. This was necessary because one of the other attributes of the entity somewhat depended on the identifier (probably that should not have the case at all). This can be done with the following command in the query screen:

dbcc checkident ('<table_name>', reseed, 0)

If you only want to know what the current identifier value is of the latest created row (still existing or not), you can use:

dbcc checkident ('<table_name>')

Cheers, Annemarie
Nice to know.

Although probably one of the golden rules related to a primary key has been violated.

Keep in mind that doing a reseed may result in broken references. Although SQL Server (since 2000) supports cascade updates, it's a propery on the foreign key reference. In other words, be aware of a possible reference integrity damage.

Marco

Hi

This is kind of old post but. . .


When I try this:

DELETE FROM {Commission}; DBCC CHECKIDENT ({Commission} , reseed, 0);

I am getting

(DELETE FROM {Commission}; DBCC CHECKIDENT ({Commission} , reseed, 0);  ): Incorrect syntax near '.'.


Any ideas?


Hi Hakan

Where are you executing those commands? 

Are you in an advanced query in your eSpace or in SQL Server Management Studio?

You have to execute DBCC CHECKIDENT command directly in SQL Server Management Studio.

The table name must be between ' ' and must be the physical table name.

First execute the query bellow to find physical_table_name:

select physical_table_name
from ossys_entity
where name='Commission'

Then execute:

DELETE FROM <physical_table_name>; DBCC CHECKIDENT ('<physical_table_name>' , reseed, 0); 

I hope it helps.

Cláudia



Also tried that but still not working:

SyntaxEditor Code Snippet

DELETE FROM {Commission}; DBCC CHECKIDENT ('{Commission}' , reseed, 0);

Hakan

I think you cannot do a DBCC CHECKIDENT in an advanced query in Service Studio.

You should do that in a SQLServer client application like SQL Server Management Studio. If you don't have it instaled, you can download SQL Server 2012 Management Studio Express from here:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

Cláudia

why not simply delete the table and recreate it?


I am doing data migration-and-import and I have to verify the data integrity in every step.

So I need to reinitialize the tables and perform the reload with a single click.

Anything that can be done from a web browser is acceptable. 

If I have to switch screen go there delete the table, go to another place to deploy the application to create the table, then go to another solution to load the data... 

I don't want a solution. I want the solution from a single place. 

With Outsystems, anything less is unacceptable.



Cláudia Oliveira wrote:

Hakan

I think you cannot do a DBCC CHECKIDENT in an advanced query in Service Studio.

You should do that in a SQLServer client application like SQL Server Management Studio. If you don't have it instaled, you can download SQL Server 2012 Management Studio Express from here:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

Cláudia

Hi Claudia,

How do you access the outsystems tables via SQL Server Management Studio? I am trying to reseed the autonumber identity field in an outsystems entity on their cloud. I tried using the advanced query but I do not have permissions to run the dbcc command.

Thanks in advanced,

Raymond


Hi Raymond

I was referring to an on-premises instalation. So, I have access to the database server.

I never used a cloud instalation. Probably you could not connect to a cloud database using SQLServer management studio.

Are you using development environment? If so, deleting the table and creating a new one might be a solution.

Cheers

Thanks Claudia, I had a feeling you had an on-premise installation. Appreciate the email :)

Cláudia Oliveira wrote:

Hi Raymond

I was referring to an on-premises instalation. So, I have access to the database server.

I never used a cloud instalation. Probably you could not connect to a cloud database using SQLServer management studio.

Are you using development environment? If so, deleting the table and creating a new one might be a solution.

Cheers

The situation is that in a lot of projects you don't have direct access to the database and you also need a user with privileges to do it.

"Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."


I think it's important to mention that in the solution. But thank you for sharing :)


Kind regards