SQL Server OUTPUT clause

Question

I am a little stuck with why I can not seem to get the 'new identity' of the inserted row with the statement below. SCOPE_IDENTITY() just returns null.

declare @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 1
set @LastException = 'test'

set nocount off

DELETE dbo.WorkRequestQueue
OUTPUT
        DELETED.MessageEnvelope,
        DELETED.Attempts,
        @LastException,
        GetUtcdate(), -- WorkItemPoisened datetime
        DELETED.WorkItemReceived_UTC
    INTO dbo.FaildMessages
FROM dbo.WorkRequestQueue
WHERE
    WorkRequestQueue.ID = @WorkRequestQueueID

IF @@ROWCOUNT = 0 
  RAISERROR ('Record not found', 16, 1) 

SELECT Cast(SCOPE_IDENTITY() as int) 

Any assistance would be most appreciated.

For now I use a workaround this like so.

declare     @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 7
set @LastException = 'test'

set nocount on
set xact_abort on 

DECLARE @Failed TABLE 
(
    MessageEnvelope xml, 
    Attempts smallint,
    LastException nvarchar(max),
    WorkItemPoisened_UTC datetime,
    WorkItemReceived_UTC datetime
)

BEGIN TRAN

DELETE dbo.WorkRequestQueue
OUTPUT
    DELETED.MessageEnvelope,
    DELETED.Attempts,
    @LastException,
    GetUtcdate(), -- WorkItemPoisened datetime
    DELETED.WorkItemReceived_UTC

INTO 
    @Failed
FROM 
    dbo.WorkRequestQueue
WHERE
    WorkRequestQueue.ID = @WorkRequestQueueID

IF @@ROWCOUNT = 0 BEGIN  
    RAISERROR ('Record not found', 16, 1) 
    Rollback
END ELSE BEGIN
    insert into dbo.FaildMessages select * from @Failed
    COMMIT TRAN
    SELECT Cast(SCOPE_IDENTITY() as int) 
END

Answer 1

EDITED FEB'2013

@MartinSmith alerts us that this bug don't want be fixed by Microsoft.

"Posted by Microsoft on 2/27/2013 at 2:18 PM Hello Martin, We investigated the issue and found that changing the behavior is not an easy thing to do. It would basically require redefining some of the behavior when both INSERT & OUTPUT INTO target has identity columns. Given the nature of the problem & the uncommon scenario, we have decided not to fix the issue. -- Umachandar, SQL Programmability Team"

EDITED OCT'2012

This is caused by a bug:

Testing bug:

Quoting OUTPUT Clause doc:

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

After test it It seems that scope_identity() only works if outer operation is an insert in a table with identity columns:

Test 1: Delete

create table #t ( a char(1) );
create table #d ( a char(1), i int identity );

insert into #t
values ('a'),('b'),('c');

delete #t
output deleted.a into #d;

select SCOPE_IDENTITY(), * from #d;

     a i 
---- - - 
null a 1 
null b 2 
null c 3 

Test 2: Inserting in outer table with identity

create table #t ( a char(1), i int identity );
create table #d ( a char(1), i int identity );

insert into #t
values  ('x'),('x'),('x');

insert into #t
output inserted.a into #d
values  ('a'),('b');

select scope_identity(), * from #d;

  a i 
- - - 
2 a 1 
2 b 2 

Test 3: Inserting in outer table without identity

create table #t ( a char(1) );
create table #d ( a char(1), i int identity );

insert into #t
values  ('x'),('x'),('x');

insert into #t
output inserted.a into #d
values  ('a'),('b');

select scope_identity(), * from #d;


     a i 
---- - - 
null a 1 
null b 2 

Answer 2

You might try to use a table variable for your output clause, thus allowing you to explicitly insert into FaildMessages:

declare     @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 1
set @LastException = 'test'

set nocount off

    -- Declare a table variable to capture output
    DECLARE @output TABLE (
        MessageEnvelope VARCHAR(50),    -- Guessing at datatypes
        Attempts INT,                   -- Guessing at datatypes
        WorkItemReceived_UTC DATETIME   -- Guessing at datatypes
    )

    -- Run the deletion with output
    DELETE dbo.WorkRequestQueue
    OUTPUT
        DELETED.MessageEnvelope,
        DELETED.Attempts,
        DELETED.WorkItemReceived_UTC
    -- Use the table var
    INTO @output
    FROM dbo.WorkRequestQueue
    WHERE
        WorkRequestQueue.ID = @WorkRequestQueueID

    -- Explicitly insert
    INSERT
    INTO dbo.FaildMessages
    SELECT
        MessageEnvelope,
        Attempts,
        @LastException,
        GetUtcdate(), -- WorkItemPoisened datetime
        WorkItemReceived_UTC
    FROM @output


IF @@ROWCOUNT = 0 
  RAISERROR ('Record not found', 16, 1)


SELECT Cast(SCOPE_IDENTITY() as int)