SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting zero in Identity Column


Inserting zero in Identity Column

Author
Message
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 690
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.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22693 Visits: 13361
Is IDENTITY_INSERT set to ON for that table while inserting?
Can you please provide the table script?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 690
Yes, Identity insert is ON. I just created the table. It has not been truncated or reseeded.

Thanks.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22693 Visits: 13361
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 690
I misread your earlier post. IsIdentity is on. But i don't know if Identity insert is on. How can i tell?

Thanks.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22693 Visits: 13361
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 690
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
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63719 Visits: 18570
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

homebrew01
homebrew01
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11638 Visits: 9222
Off topic, but it's not a best practice to start stored procedures with "sp_" . That's generally reserved for SQL system SPs.



ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2289 Visits: 690
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search