Back from a very long meeting. Why do bosses like doing that? My
concentration span is just too short to cope!

Right, you want to update several records with an automatically generated
slip number that's in the same table? Whats the sequence of events?

Whats the relationship between BankSlipNo and SlipNo? Is the SlipNo supposed
to be some sort of batch number? If it is, why don't you put it as a primary
key in a different "batch header" table, then use it to update the batch
entries? Please explain

-----Original Message-----
From: rajesh.kumar
[mailto:mssqldba-ezmlmshield-x64020715.[Email address protected]
Sent: Friday, August 31, 2007 12:26
To: LazyDBA Discussion
Subject: RE: restrict the second user while updating the first user

ROWID IS THE PRIMARY KEY
I am updating same slip no for multiple records

regards


-----Original Message-----
From: Noble Mudenha
[mailto:mssqldba-ezmlmshield-x4856576.[Email address protected]
Sent: Friday, August 31, 2007 2:57 PM
To: LazyDBA Discussion
Subject: RE: restrict the second user while updating the first user

It looks to me like you want to match the bankslipno to a slipno, why not
use a primary key that is auto incremented, then pick scope_identity and
maybe a trigger? Once the number of application users increases, you'll have
more problems with current approach. Or am I lost somewhere?

-----Original Message-----
From: rajesh.kumar
[mailto:mssqldba-ezmlmshield-x171175.[Email address protected]
Sent: Friday, August 31, 2007 11:01
To: LazyDBA Discussion
Subject: restrict the second user while updating the first user

Hi

I wrote a stored procedure to generate slip no automatically. Then problem
is when multiple users update the same time sp will go for a toss. How I can
I restrict the second user while updating the first user.

Pls find the attached sp below

CREATE proc CMS_procActionCDSlipVerify
@BankSlipNo Varchar(15)='',
@GeneratedBy Varchar(20)='',
@RowIDs Varchar(500)=''
As
Begin

Set NOCOunt ON

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Begin Tran

Exec('Update CMS_CDSlipEntry
Set BankSlipNo = ''' + @BankSlipNo +
''', SlipNo = (Select Max(IsNull(SlipNo,0)) + 1 From CMS_CDSlipEntry )
, GeneratedBy = ''' + @GeneratedBy +
''', GeneratedDateTime = GetDate() ' +
' Where RowID In (' + @RowIDs + ')')

if @@Error <> 0
Rollback Tran
Else
Commit Tran

--SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Set NOCOunt OFF

End






---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page