Exclusive Row Locking in SQL SERVER 2000

Hi
I have a table with 1 million records. I Have 5 applications (identical) that read from that table and perform the actions. Now, I want an exclusive locks on the data selected. The sql statement is below.

select top 1000 * from Numbers
With (***I need this part *** Exclusive lock on the selected data, not allowing other apps to even read)
where IsSent = 1

How to achieve this. Please explain.

Regards,
Noorul

You can do something like below:

begin tran

select top 1000 * from Numbers
With (xlock, rowlock)
where IsSent = 1

And you will have to keep the transaction open for the locks to be effective. But this is not really a good way to use the database engine. Holding a transaction open with locks consumes lot of resources & with above locking you will essentially be blocking any process that tries to do anything with these rows. You will be better off having another column in the table that tracks the state of each row. You can update the selected row(s) from each application and work with that data offline. There are also other ways of doing the same.

|||Thanks Uma,

Ok, if that would consume more resources then how about splitting the table into 5 tables (with no repetition of course) and using those tables? If so, then could you provide me the code to how to split the tables plz. I am relatively new to SQL

Regards,
Noorul

Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret