StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>"When you eliminate the impossible, whatever is left, however improbable, must be the truth"</p> </blockquote> <p>There are only a handful of possibilities if we assume that there are no errors:</p> <ol> <li>The value is not being inserted or deleted. We can verify that by querying for it right after <code>Set @ThreadId = SCOPE_IDENTITY()</code></li> <li>The Insert transaction is being rolled (e.g. from calling code transaction, from a calling sp transaction). We can verify that by looking at <code>@@TRANCOUNT</code> before and after the Update statement.</li> <li>Something is setting the Thread column to null right after your Update statement. We can check <code>@@ROWCOUNT</code> 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 <code>Select * From Messages Where MessageId = @ThreadId And Thread Is Not Null</code> and get a record. That means if later in your code is Null again, something else had changed it.</li> </ol> <p>Try the following:</p> <pre><code>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 </code></pre> <p><strong>EDIT</strong> 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 <code>Rollback Tran completed</code> or <code>Commit Tran completed</code> events along with the other SQL statements and see if something is rolling back the transaction.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload