How many user current "connect" to a row in table.

Question

How many user current "connect" to a row in table.

sol.nt

SSCrazy

Points: 2062
More actions
November 8, 2006 at 8:57 pm

#117602

Hi all...
Iam a newbie and i have one question want to ask experts ... .
I am current working on one App which have some user connect to database at the same time.
And i want to know: how many user connect or use (read , update) to one row in a table?
Could it be possible to know that??? If you have one solution to solve this problem , please let me know ...
Could it be done by Software , T-SQL or anything ...., iam happy to know.
Thanks all.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

It is an interesting question. Rows are not treated as objects in SQL Server. I really doubt we can find the number of connections to a row. Please correct me if I am wrong.

sol.nt SSCrazy Points: 2062 More actions · Answer 2

I have same think too , but i remember i have saw some software which show what user is connected and read , update record , so i think there are some ways to know ... iam a newbie and if this is a silly post , don't laugh

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Hi silly,

If you don't want us to laugh, then quit putting the smileys in your posts.. heh

I think that you could use a tool called SQL profiler, which will record all of the activity on your server for a finite period of time.

Then you could use the results of the Profiler Trace to determine when a user is reading or updating the row that you want. I would suggest that you read up on the Profilier tool. You should run it for a few minutes with the default trace configuration and look at what it captures.

If you are looking to find selects and updates to a table (let's call it tbl_sol) based on a Key field (sol_id), the profiler will capture statements like

UPDATE tbl_sol set value = 'important data' where sol_id = <the ID for the row in tbl_sol that you care about>

or SELECT * from tbl_sol where sol_id = .....

jg

sol.nt SSCrazy Points: 2062 More actions · Answer 4

Thanks for your post ...

I think there are some reason which i could use sql profiler ...

- My server must run Profiler over and over again to gain newest data and it impact performance.

- My app must read trace file to see which column or row has changed and so , the same result as above.

My idea is something as "event" will raise when data changed and my app will catch that event and perform next operation....

Please , tell me what you think about this.

Thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Hi sol,

You've changed your requirements a little now. At first you wanted to know when people accessed a row in a table. Now you indicate that you'd like to programmatically take some action when a particular row is changed.

You could do it in your application, or via stored procedures, or via triggers. If you take the time to explain what you really want to do, then the solution could be narrowed down a bit.

jg

sol.nt SSCrazy Points: 2062 More actions · Answer 6

My purpose at begin is Sql server or My App will notify my client when data was changed for any client who current connect to that row.

For example:

-user A connect and load row R1 into dataset(vb.net).

-user B also connect to that table and load row R2

- R1 and R2 has some column which has the same data

==> when user change data on some column and finished it,a litle later, because user B did not know data was changed and continue changed that data...so if i sum on total column(for example)then may be the value was double and that is what i don't expect.

Sorry about my poor English, but if you understand what i mean , please give me your advise

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Sol, you may use "timestamp" column.

Timestamp in MS SQL does not have anything common with dates. It's integer value being incremented automatically every time the row is updated.

When you load data you read timestamp value and keep it in memory. When you save the row you check if timestamp in the table is still the same as in memory. If yes - green light. No - you know better what to do in this case: inform user, reload data or something else. It's up to you.

Read BOL about timestamp datatype.

_____________
Code for TallyGenerator

sol.nt SSCrazy Points: 2062 More actions · Answer 8

Thanks for your answer .

I know how to use timestamp and some way to catch this concurrency, but maybe my description about what i meet not clearly , sorry about that.

what i mean is two user use two different records but has some columns with the same data, so i want to check change in those column

If not , second user maybe change data (it is allowed to do that because it change on a different row)when it has updated by first user and my data go wrong.

Any idea ,please let me know , thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 9

Hi sol,

There a couple quadzillion (10^40) ways to interpret what you are asking for. If you can be specific as to what you are trying to accomplish, someone hwere might be able to help you out.

Perhaps you could post the table layout and some sample data and give an example of two changes that occur that you want to intercept, and a precise explanation of what you want to happen as a result. If it is too complicated to explain in detail here, then it is too complicated to solve here.

jg

sol.nt SSCrazy Points: 2062 More actions · Answer 10

Thanks for your advise.

This is may Table

Key F_Key

N1 N2 N3 N4 N5 ID H_ID

User10 0 0 0 01 1

User20 0 0 0 02 1

sum(N1) , sum (N2)...,sum(N5) base on H_ID must be N5 to ensure that none of columns has sum > 50 , if has i will rollback that transaction.

Please let me know if i wrong or missed something

Thanks.