Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

Error In Merge Replication while server syncs

At first we received error like

"The merge process could not retrieve identity range resource for table..."

then after solving this error we again started syncing of servers manually then we got some other error like

"the process could not enumerate changes to the subscriber".

We tried our best to solve this problem but some error appears each time like

"The process could not deliver the snapshot to the Subscriber."

"The schema script 'sync -t"Design" -o"dbo" -d"\\ECOSYSTEM\D$\FTP\MSSQL\EcoSystem\ftp\ECOSYSTEM_MLEcosystem_MLEcosystem\20060619204802\Design_1337.bcp" -hORDER( [intDesignId] ASC)' could not be propagated to the subscriber"

again identity erroe emerges and agin we do the same process.

How to solve these problem?

These look like transitory network issues. Can you set QueryTimeout to something larger and see if this solves the problem.

-- Hilary Cotter

Error in Identity Column

For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739

Any ideas why this would happen?

thanks

hi

735 & 738 must've been created but deleted somehow.

|||

No, it was not deleted

|||

Rick0194:

No, it was not deleted

Yes they were, that's the only way it could have been skipped. Now, it may be that they get inserted as part of a transaction and something goes wrong and rolls it back -- the identity colum's value will appear to skip the next time a good insert occurs (I just confirmed that on sql 2000)

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
Thanx
San
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>
|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:

> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>

Error in Alter Table Statement

Hi,
I tried to alter one column as Identity colum. I executed the following
statement.
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
NOT NULL
But it gives the following error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
The table has some rows. I do not want to re-set the identity value. I can
able to do this in Enterprise Manager. But I want to do in T-Sql statement.
Pls help me
ThanxSan
You cannot do that
CREATE TABLE #Test
(
col INT NOT NULL
)
GO
INSERT INTO #Test VALUES (20)
GO
ALTER TABLE #Test ADD col1 INT IDENTITY(1,1)
GO
SET IDENTITY_INSERT #Test ON
GO
INSERT INTO #Test(col1)SELECT MAX(col) FROM #Test
GO
SET IDENTITY_INSERT #Test OFF
GO
INSERT INTO #Test (col) VALUES (100)
GO
SELECT * FROM #Test
"San" <San@.discussions.microsoft.com> wrote in message
news:290563A4-569C-4136-80D7-6EEC6DDE7463@.microsoft.com...
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1,
1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql
statement.
> Pls help me
> Thanx
>|||Hi,
If you want to do this in t-sql you must follow the steps:
1) Create temporary table like CREATE TABLE dbo.Tmp_Table_1
(
Column_1 smallint NOT NULL IDENTITY (1, 1),
--,
--
) ON [PRIMARY]
2) SET IDENTITY_INSERT dbo.tmp_Table_1 ON
3) EXEC('INSERT INTO dbo.tmp_Table_1 (Col_1,--)
SELECT Col_1, -- FROM dbo.Table_1 TABLOCKX')
4) SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
5) drop table dbo.Table_1
6) sp_rename 'dbo.Tmp_Table_1','dbo.Table_1'
If you have indexes, constraints etc.. add a step to create them as well on
new table
I did not know any t-sql statement that alters a column to identity. If you
capture a trace via profiler while altering the column in Enterprise Manager
you will find out that it performs the same logic.
Hope this helps.
"San" wrote:
> Hi,
> I tried to alter one column as Identity colum. I executed the following
> statement.
> ALTER TABLE [dbo].[Table_1] ALTER COLUMN [Col_1] [smallint] IDENTITY (1, 1)
> NOT NULL
> But it gives the following error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
> The table has some rows. I do not want to re-set the identity value. I can
> able to do this in Enterprise Manager. But I want to do in T-Sql statement.
> Pls help me
> Thanx
>