SQL Server 2008 Problem with SCOPE_IDENTITY()

Question

My code does not update the thread field. It is null. Anyone have any ideas?

INSERT INTO [Messages]([Sender], [Receiver], [Job_Number], [Subject], [MessageText], [DateSent])
VALUES(@Sender, @Receiver, @Job_Number, @Subject, @MessageText, @DateSent)

SET @ThreadID = SCOPE_IDENTITY()

UPDATE [Messages] 
SET Thread = @ThreadID
WHERE MessageID = @ThreadID

EDIT: It seems the UPDATE routine isn't being executed at all. I even added the following code to the end of the sproc, but nothing gets updated.

UPDATE Comments 
SET SomeField = @ThreadID
where SCID = 33

EDIT:

/****** Object:  Table [dbo].[Messages]    Script Date: 04/09/2010 12:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Messages](
    [MessageID] [int] IDENTITY(1,1) NOT NULL,
    [Sender] [varchar](30) NOT NULL,
    [Receiver] [varchar](30) NOT NULL,
    [Job_Number] [varchar](20) NULL,
    [Subject] [varchar](200) NULL,
    [MessageText] [varchar](max) NULL,
    [DateSent] [datetime] NULL,
    [Thread] [int] NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [DF_Messages_DateSent]  DEFAULT (getdate()) FOR [DateSent]
GO

EDIT: When I execute the stored procedure from Management Studio, the update works just fine. The problem is in my app when I call it using SQLHelper:

SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString,
            "spMessagesInsert",
            0,
            message.Sender, 
            message.Receiver,
            message.Job_Number,
            message.Subject,
            message.MessageText,
            message.DateSent
            );

EDIT: Ultimately, I changed the program code to call the stored procedure using Linq-to-sql instead of using SqlHelper. This seemed to fix the issue.

Answer 1

You could be unlucky and hitting the (in)famous identity/parallelism bug reported on MS Connect, which is now a KB article too on both SQL Server 2005 and 2008.

That is, the value returned for SCOPE_IDENTITY is wrong. The code look OK and plenty of folk have had a look at it, so what if the value is wrong? This would give the same symptoms as you report.

Try OPTION (MAXDOP 1) on the insert as suggested in the articles

I've seen this happen myself so it's not abstract or rare.

Answer 2

"When you eliminate the impossible, whatever is left, however improbable, must be the truth"

There are only a handful of possibilities if we assume that there are no errors:

1. The value is not being inserted or deleted. We can verify that by querying for it right after Set @ThreadId = SCOPE_IDENTITY()
2. The Insert transaction is being rolled (e.g. from calling code transaction, from a calling sp transaction). We can verify that by looking at @@TRANCOUNT before and after the Update statement.
3. Something is setting the Thread column to null right after your Update statement. We can check @@ROWCOUNT right after the Update statement. If it is zero, then the only possibility is that the record no longer exists. If it is 1, then clearly the update worked. Right after the Update statement, you should be able to call Select * From Messages Where MessageId = @ThreadId And Thread Is Not Null and get a record. That means if later in your code is Null again, something else had changed it.

Try the following:

Set NoCount Off

INSERT INTO [Messages]([Sender], [Receiver], [Job_Number], [Subject], [MessageText], [DateSent])

VALUES(@Sender, @Receiver, @Job_Number, @Subject, @MessageText, @DateSent)

SET @ThreadID = SCOPE_IDENTITY()

-- ensure that the value is not null
Select @ThreadId

-- we should get our record from this query
Select * From Messages Where MessageId = @ThreadId

UPDATE [Messages] 
SET Thread = @ThreadID
WHERE MessageID = @ThreadID

-- we should get 1
Select @@ROWCOUNT

-- we should get a value
Select * From Messages Where MessageId = @ThreadId And Thread Is Not Null

-- are we in a transaction?
Select @@TRANCOUNT

EDIT One other immensely helpful tool in rooting out these sorts of problems is the SQL Server Profiler. For example, you can tell it to show Rollback Tran completed or Commit Tran completed events along with the other SQL statements and see if something is rolling back the transaction.

Answer 3

Check the Messages table for triggers.

---

SET @ThreadID = SCOPE_IDENTITY() 

PRINT convert(varchar(30), @ThreadID)

UPDATE [Messages]  
SET Thread = @ThreadID 
WHERE MessageID = @ThreadID 

Does it print what you expect?

Answer 4

I've seen this exact same problem before. The solution was to turn on NOCOUNT at the top of your stored procedure code (assuming it doesn't cause any unwanted side effects for you):

SET NOCOUNT ON

Alternatively, you can set nocount on from SQLHelper for the connection your using.

I don't have all the details about why this worked, other than when nocount is off (ie. rows are counted), it seems to "confuse" the following update statement (if I get more details I'll update my answer later). The same thing that you describe happened...the following UPDATE statement never executed, and even more so, the stored procedure unexpectedly exited (based on what was recorded in the SQL Profiler).