how to manage concurrency between multiple, disconnected clients

I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".

All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row.

Thanks.

I have never used SQL Server 2005 timestamp field which is what you are referring to as the row version. This is an autoincrementing field when a change is detected and is useful in preventing dirty writes.

I have used this practice in managing phantom data back with SQL Server 2000 just by adding a column of type INT and incrementing it each time the record is changed. Basically this is how it works, when I retrieve the record, I get a version of 1, at the same time another person gets the same record with version 1 as well. This person then updates the record causing it to increment to 2. In the meantime, I decide to update the record as well, your application or database should compare the version number you have which is 1 with the current version number at the database, if it is the same then allow update as this mean no changes were detected. In this case the version is at 2 which means the copy of the data at version 1 when i retrieved it is stale and you would alert the user that either they cannot save until they do a refresh or inform them if the choose to proceed, data will be lost.

Using row versions is optimistic locking as many people can read the same record.

You can also use pessimistic looking by locking the record once you have it. You can do this by using Transactions in .NET (System.Data.SqlClient.Transaction) and specify the Isolation levels.

How to make reports available to clients

I have designed some reports using MS Reporting Services. I want to make
those available to some of my clients. What that procedure is called and how
would I do that.
Thank you in advance.You can download the microsoft code which lets you embed a report
viewer object in your .net web pages which is the easiest way.
Or you can write a custom interface and in your code behind render the
report with code.
"RA" <rchaudhary-nospam@.storis.com> wrote in message news:<OYJ$eLsWEHA.2576@.TK2MSFTNGP10.phx.gbl>...
> I have designed some reports using MS Reporting Services. I want to make
> those available to some of my clients. What that procedure is called and how
> would I do that.
>
> Thank you in advance.

how to lock a table?

hi, I have a problem on building an application that can be accessed by
multiple clients concurrently. I have a table called [sub_bg_id_allocation],
it has 2 int columns: bg_id and sub_bg_id. The business rule states that
sub_bg_id should be unique within a bg_id, e.g.
bg_id subbg_id
--
1 110
1 111
1 112
2 110
2 111
... ...
the subbg_id is started by 110 and incremented by 1 within a bg_id, actually
this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. The
newly allocated sub_bg_id will be used for inserting a 'real' data record in
another table called [pro_child_subbg_info]. (don't ask me why I need the
[sub_bg_id_allocation] table, it's already there and used by existing system
)
Now I write a stored procedure like this:
CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
@.bg_id int,
@.subbg_id int output
@....
AS
BEGIN
BEGIN TRAN
-- calculate subbg_id
declare @.last_subbg_id int, @.subbg_id int
select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgid
= @.bg_id
if @.last_subbg_id >= 110
set @.subbg_id = @.last_subbg_id + 1
else
set @.subbg_id = 110
-- subbg_id allocation
insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- subbg info.
insert pro_child_subbg_info (
bg_id, sub_bg_id, ...) values (
@.bg_id, @.subbg_id, @....)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- end successfully
COMMIT TRAN
RETURN 0 -- ***** 0 means ok *****
ErrorHandler:
ROLLBACK TRAN
RETURN -1 -- ***** -1 means fail *****
Is there any problem in a concurrent client scenario? I wonder about the
MAX() function works properly in concurrent case, should I lock the
[sub_bg_id_allocation] table within this stored procedure? How can I lock it
?
Thanks a lot!Hi
You may want to add a hint such as UPDLOCK to the select statement.
http://msdn.microsoft.com/library/d...br />
4ox9.asp
This will potentially reduce concurrency such as
http://support.microsoft.com/defaul...kb;en-us;179362
so you may want to look at using an identity instead.
John
"nonno" wrote:

> hi, I have a problem on building an application that can be accessed by
> multiple clients concurrently. I have a table called [sub_bg_id_allocation
],
> it has 2 int columns: bg_id and sub_bg_id. The business rule states that
> sub_bg_id should be unique within a bg_id, e.g.
> bg_id subbg_id
> --
> 1 110
> 1 111
> 1 112
> 2 110
> 2 111
> ... ...
> the subbg_id is started by 110 and incremented by 1 within a bg_id, actual
ly
> this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. T
he
> newly allocated sub_bg_id will be used for inserting a 'real' data record
in
> another table called [pro_child_subbg_info]. (don't ask me why I need the
> [sub_bg_id_allocation] table, it's already there and used by existing syst
em)
> Now I write a stored procedure like this:
> CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
> @.bg_id int,
> @.subbg_id int output
> @....
> AS
> BEGIN
> BEGIN TRAN
> -- calculate subbg_id
> declare @.last_subbg_id int, @.subbg_id int
> select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgi
d
> = @.bg_id
> if @.last_subbg_id >= 110
> set @.subbg_id = @.last_subbg_id + 1
> else
> set @.subbg_id = 110
> -- subbg_id allocation
> insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- subbg info.
> insert pro_child_subbg_info (
> bg_id, sub_bg_id, ...) values (
> @.bg_id, @.subbg_id, @....)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- end successfully
> COMMIT TRAN
> RETURN 0 -- ***** 0 means ok *****
> ErrorHandler:
> ROLLBACK TRAN
> RETURN -1 -- ***** -1 means fail *****
> Is there any problem in a concurrent client scenario? I wonder about the
> MAX() function works properly in concurrent case, should I lock the
> [sub_bg_id_allocation] table within this stored procedure? How can I lock
it?
> Thanks a lot!|||Thanks John. How can I use identity in this case?
"John Bell" wrote:
> Hi
> You may want to add a hint such as UPDLOCK to the select statement.
> http://msdn.microsoft.com/library/d... />
z_4ox9.asp
> This will potentially reduce concurrency such as
> http://support.microsoft.com/defaul...kb;en-us;179362
> so you may want to look at using an identity instead.
>
> John
>
> "nonno" wrote:
>|||Hi
That would be a change in functionaility in that the subbg_id will be
continually increasing. Does the subbg_id really need to do what you are
trying to build?
When needed you can still rank the entries by using something
select a.bg_id, subbg_id as original_subbg_id, (select count(*) from
sub_bg_id_allocation b where a.subbg_id < b.subbg_id ) + 110 as
ranked_subbg_id FROM sub_bg_id_allocation a
John
"nonno" wrote:
> Thanks John. How can I use identity in this case?
> "John Bell" wrote:
>