Using @@IDENTITY on 7.0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using @@IDENTITY on 7.0

Author

Topic

monik
Starting Member

4 Posts

Posted - 2005-07-06 : 12:16:18

Here is my code:

DECLARE @ParentID INT
DECLARE @title NVARCHAR(64)

IF NOT EXISTS ( SELECT ReportItemID FROM ReportItem WHERE (Caption = N'Transaction Reports'))
BEGIN
INSERT INTO dbo.ReportItem
(
Caption,
Description,
FileName,
ParentID,
FactorySet
)
VALUES
(
N'Transaction Reports', -- Caption
N'', -- Description
N'', -- FileName
0, -- ParentID
0 -- FactorySet
)

SET @ParentID = @@IDENTITY

SET @title = N'All Access Attempts'
INSERT INTO dbo.ReportItem
(
Caption,
Description,
FileName,
ParentID,
FactorySet
)
VALUES
(
@title, -- Caption
N'This report shows all access attempts', -- Description
N'TrnHis27.rpt', -- FileName
@ParentID, -- ParentID
0 -- FactorySet
)

END
GO

--------------------------------------------------------------------
Here is the error:
Server: Msg 515, Level 16, State 2, Line 32
Cannot insert the value NULL into column 'ParentID', table 'GeoffreySQL.dbo.ReportItem'; column does not allow nulls. INSERT fails.
The statement has been terminated.

for some reason the variable is not getting the value, why is this. it only happens with 7.0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 13:00:31

One thing:
You should only attempt the second insert if the first insert was performed AND you successfully retrieved the identity value. (if Transaction Reports already exists in the caption column you won't have an identity value for the second statement) Error handling and transaction contol is missing from your statements.

Also, I think it's better to use scope_identity() rather than @@identity

Finally, are you certain the reportItem table has an identity column?

Be One with the Optimizer
TG

monik
Starting Member

4 Posts

Posted - 2005-07-06 : 15:25:13

yes it has. what is the sintax to use the scope_identity(). am new to sql. also our QA noticed that this code works on a full SQL 7 but it fails on MSDE 7, any idea why?

thanks,
Monica.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-06 : 15:36:32

scope_identity()syntax:
select scope_identity()

please read about in Books Online.

I don't know about MSDE 7 but I would suggest a samll test:


create table #test (rowid int identity(1,1), i int)
go
insert #test (i)
values (1)
select @@identity [@@identity], scope_identity() [scope_identity()]
go
drop table #test

Be One with the Optimizer
TG

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-07-07 : 00:13:14

SET @ParentID = SCOPE_IDENTITY()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources