StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Although not directly answering your question here, rather than reinventing the wheel and making life difficult for yourself, unless you enjoy it of course ;-), may I suggest that you look at using SQL Server Service Broker.</p> <p>It provides an existing framework for using queues etc.</p> <p>To find out more visit.</p> <p><a href="http://msdn.microsoft.com/en-us/library/ms166043(SQL.90).aspx" rel="nofollow noreferrer">Service Broker Link</a></p> <p>Now back to the question, I am not able to replicate your problem, as you will see if you execute the code below, data is returned regardless of the order os the select/update statement.</p> <p>So your example above then.</p> <pre><code>create table #MSG_Q (id int identity(1,1) primary key,status int) insert into #MSG_Q select 0 DECLARE @queueid INT BEGIN TRANSACTION TRAN1 SELECT TOP 1 @queueid = id FROM #MSG_Q WITH (updlock, readpast) WHERE #MSG_Q.status=0 UPDATE #MSG_Q SET status=1 WHERE id=@queueid SELECT TOP 1 * FROM #MSG_Q WHERE #MSG_Q.id=@queueid COMMIT TRANSACTION TRAN1 select * from #MSG_Q drop table #MSG_Q </code></pre> <p>Returns the Results (1,1) and (1,1)</p> <p>Now swapping the statement order.</p> <pre><code>create table #MSG_Q (id int identity(1,1) primary key,status int) insert into #MSG_Q select 0 DECLARE @queueid INT BEGIN TRANSACTION TRAN1 SELECT TOP 1 @queueid = id FROM #MSG_Q WITH (updlock, readpast) WHERE #MSG_Q.status=0 SELECT TOP 1 * FROM #MSG_Q WHERE #MSG_Q.id=@queueid UPDATE #MSG_Q SET status=1 WHERE id=@queueid COMMIT TRANSACTION TRAN1 select * from #MSG_Q drop table #MSG_Q </code></pre> <p>Results in: (1,0), (1,1) as expected.</p> <p>Perhaps you could qualify your issue further?</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