Have Geek Brain

SQL Server 2008 issue with SCOPE_IDENTITY ()

My code is not updating the stream field. It is zero. Does anyone have any idea?

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 that the UPDATE procedure is not 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 fine. The problem is in my application 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: I ended up modifying the programming code to call the stored procedure using Linq-to-sql instead of using SqlHelper. This seems to fix the problem.

+2


source to share


4 answers


"When you eliminate the impossible, whatever remains, however improbable, must be true."

There are only a few possibilities, assuming no errors:

  • The value is not inserted or deleted. We can verify that by requesting it immediately afterSet @ThreadId = SCOPE_IDENTITY()

  • An Insert transaction was inserted (for example, from a code transaction call, from a sp call transaction). We can verify this by looking @@TRANCOUNT

    before and after the Update statement.
  • Something sets the Subject column to null right after your Update statement. We can check @@ROWCOUNT

    right after Update is approved. If it is zero, then the only possibility is that the entry no longer exists. If it's 1, then obviously the job worked. Immediately after the Update is approved, you can call Select * From Messages Where MessageId = @ThreadId And Thread Is Not Null

    and get the record. This means that if there is Null again later in your code, another will change 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 Another extremely useful tool for fixing these issues is SQL Server Profiler. For example, you can tell it to show events Rollback Tran completed

or Commit Tran completed

along with other SQL operations and see if something speeds up the transaction.

+2


source


You might be out of luck and hit (at) the well-known / parallelism error reported in MS Connect , which is now a KB article too on SQL Server 2005 and 2008.

That is, the value returned for SCOPE_IDENTITY is incorrect. The code looks fine and a lot of people have looked at it, so what if the value is wrong? This will give the same symptoms as you.



Try OPTION (MAXDOP 1)

on tab as suggested in articles

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

+4


source


Check the message 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?

0


source


I've seen this exact same problem before. The solution was to include 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 from SQLHelper for the connection you are using.

I don't have all the details as to why this worked, other than when nocount is disabled (ie rows are counted) it seems to "confuse" the next update statement (if I get more details, I'll update my answer later ). The same thing you described happened ... the next UPDATE statement was never executed, especially since the stored procedure exited unexpectedly (depending on what was recorded in SQL Profiler).

-1


source






More articles:


All Articles