We help IT Professionals succeed at work.

VSE VB.NET SQL SERVER CE how to reset identity column on tables.

948 Views
Last Modified: 2012-06-27
For most all database design I have been using the DBE in Visual Basic Express 2010 with SQL Server Compact Edition 3.5.  Now I am ready to test deployment and I want to reset my database to its unused state.

This means I need to reset all the identity columns back to 0.

Is there a way to do this within VBE 2010?  If so, how?  

If not, what would you suggest as an alternative approach?

Thanks in advance.
Comment
Watch Question

Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
If there is no data in table then truncate it. It will automatically reset the counter.

If there is data in table then i is not possible to reset counter.

Thanks,

Alpesh
Kalyanum Deepak KumarSenior Technical Lead

Commented:
Hi Check this, might need to deal with this carefully and check there is no data loss for production environment.
Kalyanum Deepak KumarSenior Technical Lead

Commented:
oh sorry : here is the url to check http://msdn.microsoft.com/en-us/library/ms176057.aspx
Kalyanum Deepak KumarSenior Technical Lead

Commented:
basically, it describes DBCC commands.

Author

Commented:
From another website:

The Truncate table command does not work in SQL SERVER CE. A great work around for this is to use “DELETE from [Table]” and then to reset the Identity column if you are using one you can use “ALTER TABLE [TableName] ALTER COLUMN ID IDENTITY (1,1)”

My problem is I do not see a SQL Worksheet in the VBE IDE in which to enter this command.
Is it not available in the IDE or am I just missing it?
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Get the SQL server management studio. That's what it's for.  

Author

Commented:
aarontomosky:

I downloaded it.  Two questions:

Is 2008 the latest available Express (free) version?

How do I install the bulk of it to my E: drive, where I have more room?
(All the paths during the setup are greyed out so they cannot be changed.)

Thanks!
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
The newest version is 2008 R2. You can choose the install folder but it's not a large install.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Have not tried it with the command object yet, but I wonder why this did not work in the SQL Management Studio (there was an error parsing the query)::

ALTER TABLE CommandCodes ALTER COLUMN CommandNo <int> (51, 1);

Is the  syntax not quite right?  Do I need some single quotes somewhere maybe?  (and I wonder what (51,1) has to do with?)

Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
If you right click modify the table there is a line in the properties for increment value.

Author

Commented:
aarontomosky:


Not seeing it. May not be there for SQL Server Compact Edition, or not having used this tool before, I just don't know precisely where to look and click.   Right clicking on the table name my only choices are  Edit Table and Properties.    Editing the table schema I can change the identity seed, but since the table was previously populated, I assume that no longer has any effect.  Difficult to say for sure, since from within this tool I do not see how to display the table data and add a row to it like I can with the DBE and the VBE IDE.

Please advise.
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Correct, you must clear the table to reset the identity seed or else it would assign the same Pk values all over again.

Author

Commented:
The table is empty, but as yet I have been unable to truncate it or do anything to set it back to an "unused" state.    So right now, it likely still "remembers" the last identity it used and will most likely continue to increment from that point.   I suspect if I can get the syntax right, CodeCruiser's suggestion will work, but I would also like to understand at least this much about how to use this tool, since I took the trouble to find it, download it, and install it and it may prove useful in the future.



Author

Commented:
Success!

ALTER TABLE CommandCodes ALTER COLUMN CommandNo IDENTITY (1,1);


BTW, aarontomosky, how do I clear the result panes?
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Just close it

Author

Commented:
aarontomosky:

I see menu options to hide it or show it, none to clear it and it does not have a close X

Author

Commented:
Ah, but the message window text is EDITABLE, which I did not expect.  I can select it all and delete it.

Author

Commented:
CodeCruiser was on the right track, but the actual SQLthat worked for me is what I posted under the word "Success!"
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.