Renato is correct.

I have tested the situation shown in the original proc and Renato is correct.  A deadlock can occur in this situation when using HOLDLOCK. Using UPDLOCK (instead of...

Renato said:

I think the deadlock in the original sproc occurs if two instances acquire shared locks on the select before either can attempt to acquire the exclusive...

Renato,

I lean towards your idea of putting the UPDLOCK hint on the Select sub query just to play it safe.

This seems like it would be difficult to test and although...

Kenneth,

Thanks for your explanation.

The point with this 'backward' notation, is that you can update (the other columns) and assign values from columns not updated (ie the PK) in one...

Don't keep it secret - what was the solution.

Select StoreNo

From MyTable

Where DayPolled between '22-Mar-2005' and '28-Mar-2005'

group by StoreNo

Having Count(*) < 7

Edit: Sorry I just noticed this answers only part of your question.

I'm sure with over 300 posts on this site you have heard of the CAST or CONVERT functions.

Please see my edited post above.

I guess I can answer my own question from BOL:

UPDATE (T-SQL)

Changes existing data in a table.

Syntax

UPDATE

        {

         table_name WITH ( <table_hint_limited> [...n])

         | view_name

         | rowset_function_limited...

You're updating the table instead of the cursor position!

WHILE (@@FETCH_STATUS) = 0

BEGIN

 UPDATE WorkSite SET CRED_ID = @Value

 Where Current Of XYZ  <---- Add this

 FETCH NEXT FROM XYZ INTO @CompanyName

 SET...

Noel,

I'm not at a machine where I test at the moment but is that syntax correct?  I haven't seen it before.  If so, live and learn.

UPDATE unprocessed_message 

SET state =...

Noel,

BOL:

Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then...

Using 'with HoldLock' raises the isolation level to Serializable and will place a range lock on the index for the column 'state'.  If 'state' is not indexed then an excessive amout...

check out artice from this site:

http://www.sqlservercentral.com/columnists/mcurnutt/datadictionaryfromwithinsqlserver2000.asp

From BOL this may help:

FLOOR (T-SQL)

Returns the largest integer less than or equal to the given numeric expression.

Syntax

FLOOR(numeric_expression)

SELECT FLOOR(123.45) returns...