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 Record Limit

Quick question, is there a record limit in MSSQL assuming that we don't use a identity key. I am going to be using a table to sava mail server logs. It will create about 5000 records a minute. Also are there any perfomrance issues once you reach a certain numberof records, assuiming i am indexing one of the columns.limited by available storage

[Books Online] Maximum Capacity Specifications

look for rows per table

or LINK (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp)

now 5000 rows a minute for how long??|||drop any index before inserting data.
when transaction complished,recreate index.|||Also plan to archive the data in order to manage the database efficiently without any issues. say archive the data after a week or month or so.

This way you can ease the administration of the database on the terms of performance etc.

Refer to http://www.sql-server-performance.com for tips and tricks on performance issues.|||Thanks for all your help, I am going to do some testing and see what happens... the best way to learn is to do.

MS SQL 2005 identity lost new database

Hello

I am creating a TABLE

CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]

when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1

now if I want to make a copy of that database, importing datas and tables

I get :
Identity Specification = No
(Is Identity) = No

How can I avoid this problem ? my application is not working anymore

ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]

is not solving that problem

thank youit sounds like you copied your data using SSIS or DTS or whatever you want to call it. This will copy your data but not your schema. You likely lost all of your indexes and constraints as well. What you want to do if you want to copy all of your data and your schema, is to take a backup of the database you want to copy and restore that database to a new location. If it is a new server, you will have to remap you logins as well.|||With SQL Server 2000 Enterprise Manager you could quickly and easily copy both data and schema between servers. With 2005, you can't. This is Microsoft's idea of progress...

A work-around is to generate a script for you schema and execute it on the target server, and then you SSIS to transfer just the data.|||thank you

what is the code line for ALTER COLUMN to create IDENTITY (1,1)
I must do it now with code ?|||there is not one.

I am a little concerned from what you said in first and last post that you are going to mess up all of your primary key and foreign key relationships.

however if you must you need to create a new table and in your create table statement you must define an identity column. copy the data from your current table to the new one. drop the old table, and rename the new one.|||in that way maybe ?:

ALTER TABLE [dbo].[TbTest] ADD
[id1_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED;
ALTER TABLE [dbo].[TbTest] DROP COLUMN [id_TbTest];
EXEC sp_rename '[TbTest].[id1_TbTest]', 'id_TbTest', 'COLUMN';

thank you