Identity Reset Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity Reset Problem

Author

Topic

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 10:32:07

Hello,
I have migrated Access Databases to SQL Server 2005. In the access database tables the AutoNumber field is converted to Identity field. The problem am having now is we are using access database data in SQL Server and each time user adds new record it fails because in SQL Server Identity column is not incremented automatically. I have looked one table, there is upto 100 records, when i try to add a new record, the Identity column is not incremented to 101. Therefore, how can i autmate or enable the identity seed for all the tables so that in future this kind of problem do not come. Please let me know if the question is not clear. Thanks in advance for your help.I am looking for a script that helps to accomplish this task for 161 tables. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 10:47:33

See "DBCC CHECKIDENT" in Books Online.

N 56°04'39.26"
E 12°55'05.63"

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 12:24:12

Hi Peso:
I look at it and when i used
DBCC CHECKIDENT('[dbo].[TableName]', RESEED, 101)
When i enter the data in the table it did not pickup the Identity automatically. I do not know what is the problem.

Thanks,

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 13:15:17

are you SURE that the field is an IDENTITY in SQL SERVER? I have never heard of one that failed to increment. Failed insert because of duplicate, yes.

Follow the "How to ask" link in my signature, and restate your question.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 13:52:43

Here is my revised question with the code:
I have migrated Access Databases to SQL Server 2005. In the access database tables the AutoNumber field is converted to Identity field. The problem am having now is we are using access database data in SQL Server and each time user adds new record it fails because in SQL Server Identity column is not incremented automatically. I have looked one table, there is upto 100 records, when i try to add a new record, the Identity column is not incremented to 101. Therefore, how can i autmate or enable the identity seed for all the tables so that in future this kind of problem do not come. Please let me know if the question is not clear. Thanks in advance for your help.I am looking for a script that helps to accomplish this task for 161 tables.

It is fine that if i create a table with Identity column, it works but i only have a problem after the migration of Access to SQL Server and when adding the record the field in Identity column does not get incremented automatically. Here is the code for the table to make sure that there is Identity column in the table.

CREATE TABLE [dbo].[Assets](
[AssetID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[Asset_StatusID] [int] NULL,
[ManufacturerID] [int] NULL,
[ModelID] [int] NULL,
[Asset_TypeID] [int] NULL,
[Asset_Tag] [nvarchar](25) NULL,
[Serial_Number] [nvarchar](50) NULL,
[Networked] [bit] NULL DEFAULT ((0)),
[UserID] [int] NULL

CONSTRAINT [Assets_PK] PRIMARY KEY NONCLUSTERED
)
Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 14:03:30

Is that "create table" code (above) generated from a table that is currently having the problem?

>>when i try to add a new record, the Identity column is not incremented to 101
what does happen? an error? a null value, a value other than 101?

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 14:11:47

I am going to guess that the data coming in from access already has the autonumber field filled in, and he is trying to insert THAT into the identity field, and will be getting an error.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 14:19:00

Yes TG, I scripted out and pasted it here. It does not do anything infact. I added manually from the GUI not with the script.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 14:21:38

Show us your INSERT statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 14:23:28

DonAtWork,
No, that is not the case. I already imported the data from Access having AutoNumber filed to SQL Server with Identity field in SQL Server. The problem now is whenever user add new data on the exsiting data migrated from Access, the Identity Column in SQL Server does not get incremented and am looking for the way how can i be able to accomplish this. For example, i inserted data from Access and the AssetID is 100 but when users add data now, the assetID should be 101 with the auto increment but it is not happening here. Hope i made it clear. Please let me know if you still have problem understanding my question.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 14:24:01

quote:
Originally posted by tkizer

Show us your INSERT statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

*GASP* On the first date????

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 14:33:07

Following is my Insert Statement. Again, when i created this table in SQL Server(not the migration from Access, it works) but it does not work on the table that i have migrated from Access.

INSERT INTO [TestDB].[dbo].[Assets]
([SiteID]
,[Asset_StatusID]
,[ManufacturerID]
,[ModelID]
,[Asset_TypeID]
,[Asset_Tag]
,[Serial_Number]
,[Networked]
,[UserID])
VALUES
(1, NULL, NULL, NULL, NULL,NULL, NULL,NULL,NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 14:44:04

It works fine for me in SSMS. Try it out to see:

USE master
GO

CREATE TABLE [dbo].[Assets](
[AssetID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[Asset_StatusID] [int] NULL,
[ManufacturerID] [int] NULL,
[ModelID] [int] NULL,
[Asset_TypeID] [int] NULL,
[Asset_Tag] [nvarchar](25) NULL,
[Serial_Number] [nvarchar](50) NULL,
[Networked] [bit] NULL DEFAULT ((0)),
[UserID] [int] NULL
)

INSERT INTO [dbo].[Assets]
([SiteID]
,[Asset_StatusID]
,[ManufacturerID]
,[ModelID]
,[Asset_TypeID]
,[Asset_Tag]
,[Serial_Number]
,[Networked]
,[UserID])
VALUES
(1, NULL, NULL, NULL, NULL,NULL, NULL,NULL,NULL)

SELECT * FROM Assets

DROP TABLE Assets

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 14:45:12

quote:
Originally posted by abidba

Yes TG, I scripted out and pasted it here. It does not do anything infact. I added manually from the GUI not with the script.

So no row at all gets added and no error? So the fact that the identity didn't increment maybe mis-leading. Sounds like sql server never even received the INSERT statement. Could it be your GUI is not configured properly to the new table? If you use Sql Profiler when you do your insert does it capture anything? If you do the insert directly in a sql query window does it work?

Be One with the Optimizer
TG

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 14:51:44

No error and no row gets added. Well, on the otherway around, if i script out this table and and created in SQL Server and add records, it does its work, row gets added without error. It happens only to the table created from the migration of access to sql server. it is frustrating though. I have not used profiler yet. I have not tried from query analyzer. Thanks guys.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 15:01:39

I suspect it doesn't matter if the table was created in sql or not. I bet the problem is when you add the row from your GUI vs. adding it via an SSMS insert statement (like Tara's code).

Be One with the Optimizer
TG

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 16:06:20

Tara's code work for me from Query Window but does not work from SSMS. This is interesting !!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 16:19:44

Did you run my code exactly or did you make adjustments?

And what do you mean by Query Window as compared to SSMS? To me, those are the same.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

abidba
Starting Member

26 Posts

Posted - 2009-09-28 : 16:32:49

Tara:
I did run exactly same code as yours. Ok, SSMS is you can rightclick on the table and then click open and you can add the record manually but from query analyzer you can add record using TSQL with Insert statemtn.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 16:46:32

Do not right click on the table to make data modifications. Use the new query window only. We refer to the new query window as SSMS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

abidba
Starting Member

26 Posts

Posted - 2009-09-29 : 10:21:35

Thanks Tara.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources