MS SQL Server Locking problem!

Hi,
MS SQL server has many sort of locking levels and locking modes.
Among all of these can I know what best suit for me when simulating the identity column property to avoid dublicating rows with same number, such as :
-------------------
CREATE PROC NewNumber
@.OwnerNo int,
@.ReturnValue int OUTPUT
AS

DECLARE @.NewNum int
DECLARE ctr_cursor CURSOR FOR
SELECT MAX(MessageNo) AS MaxNumber FROM tblMessages WHERE OwnerNo = @.OwnerNo

OPEN ctr_cursor
FETCH NEXT FROM ctr_cursor INTO @.NewNum
If @.@.FETCH_STATUS = 0
BEGIN
SELECT @.NewNum = @.NewNum + 1
END
ELSE
BEGIN
SELECT @.NewNum = 1
END

SELECT @.ReturnValue = @.NewNum

CLOSE ctr_cursor
DEALLOCATE ctr_Cursor

GO
-------------------
I can't use the identity (seed & increment) property with my case because my column is not unique, the uniqueness is built on two columns.

I would really appreciate your help :confused: .
Thankstoo bad!
I can't find some one help me:(
still waiting:confused:|||Create a table named UniqueID with 2 columns
TableName Varchar(15)
LastID Int

Add a record with the table or value name and the number you want to start with.

Create a stored procedure to increase the number and call it when you need to add a record to your other table.

This will work faster than searching the table every time.

CREATE procedure GetNextTableUID (@.tableName varchar(20)) as

/* Gets the next UniqueID for the given table.
* If there is no entry for that table, adds one.
*/

begin transaction;

declare @.thisUID int;

set @.tableName = lower(@.tableName);

-- Get next UID

select @.thisUID = LastUID + 1
from UniqueID UPDLOCK HOLDLOCK
where lower(TableName) = lower(@.tableName);

-- Update UniqueID table

if ((@.@.rowcount = 0) or (@.thisUID is null)) begin
set @.thisUID = 1;
insert UniqueID values (@.tableName, @.thisUID);
end else
update UniqueID set LastUID = @.thisUID
where lower(TableName) = lower(@.tableName);

-- Check for errors

if @.@.error <> 0 begin
rollback transaction;
raiserror( 'SQL Error: GetNextTableUID', 16, -1 )
return 0;
end

commit transaction;
return @.thisUID;

GO|||I thanked god, that you were there!:rolleyes:

I recieved two more professional solutions by email, but yours was the best, you got me stright to the point.

Many many thanks
All the best:D

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.