>How do I lock 1 row in a table from an ODBC Connection.
>eg. On Oracle I issue:
>select * from T where (primary_key) = (some_value) FOR UPDATE;
>
>
>COnversly how do I test if this 1 row is locked?
>eg.On Oracle I:
>select * from T where (primary_key) = (some_value) FOR UPDATE NOWAIT;
>
>Is there a similar mechanism on sql server???

For SQL Server 7.0:

BEGIN TRANSACTION
SELECT * FROM table (ROWLOCK HOLDLOCK) WHERE pkey = value
...
COMMIT TRANSACTION

The locking hint HOLDLOCK will cause SQL Server 7.0 to hold the lock for the duration of the transaction.

For 6.5 you can do basically the same thing but you'll have to lock the entire page, which could cause concurrency problems. Change the locking hints to (PAGLOCK HOLDLOCK).

IMPORTANT: For both of these options, SQL Server may acquire multiple locks while searching for the correct row. ALL the locks will be held until the end of the transaction.

Maybe there's another way to do whatever you're trying to do?

-Mike