Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
o_sam
Starting Member
2 Posts |
 Posted - 2004-01-26 : 21:36:37
|
| Hi Everybody, Is there any way to lock a stored procedure. The main purpose is that i need to generate a unique value to form in VB at FORM LOAD. The Database is SQL SERVER 2000. This application is a multiuser. For this , I have a stored procedure which gets the maximum value and then add 1 to it and the same value is retrived into VB Form at ONLoad Event. I have noticed that i am not getting unique values into the form. i get this very rare once for every 5000 records (approx) The stored procedure is ALTER procedure taskno_sp @max_tkpaid3 int=0 as select @max_tkpaid3=max(tkpano)+1 from tkpaid insert into tkpaid (tkpaid) values(@max_tkpaid3) return @max_tkpaid3 Please help me in this issue and is there any way we could lock a stored procedure, so only once user can access it at a time. Thanks Sam |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-01-26 : 23:54:49
|
| Depending on what exactly tkpano is (INT, VARCHAR etc) you might want to look into an INDENTITY or UNIQUEIDENTIFER field. The whole "select max + 1" will get you in trouble. Trust me. I've done it before and I've learned my lesson.Do some reading in SQL Books Online or on http://www.sqlteam.com about INDENTITY (Good), UNIQUEIDENTIFER (Better), and natural keys (Best).Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-01-26 : 23:56:38
|
| Sam,Look up ISOLATION Levels in BOL.. SET TRANSACTION ISOLATION LEVELDavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
Locking a stored procedure