Inserting zero in Identity Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Inserting zero in Identity Column

22 posts, Page 1 of 3

1 2 3 »»»

Inserting zero in Identity Column

Rate Topic

Display Mode

Topic Options

Author

Message

ramadesai108

Posted Thursday, March 04, 2010 9:19 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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.

Post #876907

spaghettidba

Posted Thursday, March 04, 2010 9:23 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 4,804, Visits: 8,073

Is IDENTITY_INSERT set to ON for that table while inserting?
Can you please provide the table script?

Get your two-cent-answer quickly
The Spaghetti DBA

Post #876910

ramadesai108

Posted Thursday, March 04, 2010 9:31 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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

Thanks.

Post #876921

spaghettidba

Posted Thursday, March 04, 2010 9:38 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 4,804, Visits: 8,073

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #876930

ramadesai108

Posted Thursday, March 04, 2010 9:43 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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

Thanks.

Post #876936

spaghettidba

Posted Thursday, March 04, 2010 9:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 4,804, Visits: 8,073

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #876946

ramadesai108

Posted Thursday, March 04, 2010 10:46 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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

Post #877001

SQLRNNR

Posted Thursday, March 04, 2010 10:59 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #877015

homebrew01

Posted Thursday, March 04, 2010 11:01 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,554, Visits: 7,211

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

Post #877016

ramadesai108

Posted Thursday, March 04, 2010 11:30 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

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.

Post #877041

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions