Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Inserting zero in Identity Column Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 9:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Thursday, March 4, 2010 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
Is IDENTITY_INSERT set to ON for that table while inserting?
Can you please provide the table script?


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #876910
Posted Thursday, March 4, 2010 9:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Yes, Identity insert is ON. I just created the table. It has not been truncated or reseeded.

Thanks.
Post #876921
Posted Thursday, March 4, 2010 9:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #876930
Posted Thursday, March 4, 2010 9:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Thursday, March 4, 2010 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #876946
Posted Thursday, March 4, 2010 10:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Posted Thursday, March 4, 2010 10:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,750, Visits: 15,447
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


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
Posted Thursday, March 4, 2010 11:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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
Posted Thursday, March 4, 2010 11:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse