Inserting zero in Identity Column

Question

Post reply

Inserting zero in Identity Column

ramadesai108

SSChampion

Points: 10609
More actions
March 4, 2010 at 9:19 am

#386435

Hi,
I have an identity column where the identity seed is 1 and identity increment is 1. When i insert a record from a web application, it enters zero in the identity column. Any ideas?
Thanks.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Is IDENTITY_INSERT set to ON for that table while inserting?

Can you please provide the table script?

-- Gianluca Sartori

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

Yes, Identity insert is ON. I just created the table. It has not been truncated or reseeded.

Thanks.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Then change it to OFF. The identity column is there to be computed at the time you insert into the table. If you set IDENTITY_INSERT ON you allow inserts on the identity column: you push 0, you get 0.

-- Gianluca Sartori

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

I misread your earlier post. IsIdentity is on. But i don't know if Identity insert is on. How can i tell?

Thanks.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Ok, let's start from the table definition script: can you post it please?

You may want to read the article linked in my signature, you would find it useful.

-- Gianluca Sartori

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Thanks for your time

Table Definition

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[customers](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Description] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ContactPhone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Email] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Street] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Zip] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[StateID] [int] NULL,

[Created] [datetime] NOT NULL CONSTRAINT [DF_clients_dteCreate] DEFAULT (getdate()),

CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Stored Procedure to insert

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_insCustomers]

-- Add the parameters for the stored procedure here

(@Name nvarchar(50)

,@Description nvarchar(300)

,@ContactPhone nvarchar(12)

,@Email nvarchar(75)

,@Street nvarchar(75)

,@City nvarchar(50)

,@Zip nvarchar(12)

,@StateID int)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[customers]

([Name]

,[Description]

,[ContactPhone]

,[Email]

,[Street]

,[City]

,[Zip]

,[StateID])

VALUES

(@Name

,@Description

,@ContactPhone

,@Email

,@Street

,@City

,@Zip

,@StateID)

select @@Identity

END

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Are you seeing 0's in your table currently, or are you referring to @@identity returning a 0 during this proc?

According to script and table definition, you should not be inserting any 0's nor should you be able to have any more than one 0 in your table.

Try adding a semi-colon after the insert statement, and try using ident_current rather than @@identity.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

homebrew01 SSC Guru Points: 55539 More actions · Answer 8

Off topic, but it's not a best practice to start stored procedures with "sp_" . That's generally reserved for SQL system SPs.

ramadesai108 SSChampion Points: 10609 More actions · Answer 9

This stored procedure and the table definition has been working since the past 2 years. I just scripted the table definition from another database and then created the table on another database using the same script.

When I insert a record using a web application (which used the stored proc), it inserts zero into the identity column and @@Identity returns zero as well. When i run a query "select * from Customers", I see zero in the identity column.

Thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

ramadesai108 (3/4/2010)
This stored procedure and the table definition has been working since the past 2 years. I just scripted the table definition from another database and then created the table on another database using the same script.
When I insert a record using a web application (which used the stored proc), it inserts zero into the identity column and @@Identity returns zero as well. When i run a query "select * from Customers", I see zero in the identity column.
Thanks.

What is the difference between the table where it is working and this other database and app where you just scripted out the table from?

Please run dbcc checkident against the table.

How many 0's do you have in your table currently?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramadesai108 SSChampion Points: 10609 More actions · Answer 11

Just 1 record with zero. If i insert data manully using query analyzer, then the second record is inserted with the identity of 2.

Here is another user who is complaining the same thing:

http://www.generation-nt.com/us/instead-insert-inserted-identity-column-0-help-116980361.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Do you have a trigger on the table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramadesai108 SSChampion Points: 10609 More actions · Answer 13

There are no triggers on the table

the following is having the same issue:

http://www.generation-nt.com/us/instead-insert-inserted-identity-column-0-help-116980361.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Is there any way you could setup the table to perform some fresh tests?

If possible, drop and recreate the table - else truncate it (if this is a test environment only please).

As soon as the table is prepped, do the following:

dbcc checkident('customers',NORESEED)

Run your app (to perform a test insert)

dbcc checkident('customers',NORESEED)

Give the values for each of the checkident commands that you run.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events