Is There A Way To List Current Activity, Such As Primary Key?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » In The Enterprise » Is There A Way To List Current Activity, Such...

Is There A Way To List Current Activity, Such As Primary Key?

Rate Topic

Display Mode

Topic Options

Author

Message

EdA ROC

Posted Wednesday, February 03, 2010 1:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394

There's a bug in our ERP application and until the vendor fixes the code I need to execute some SQL statements and run a utility to fix the data. I periodically throughout the day check and fix.

I have written a couple SQL statements which let me know the jobs (job_number) to fix. However, I don't want to 'fix a job' that a user is still active in - they may be editing various parts of a job and therefore be actively making changes and when they finish the data will need fixing. No sense fixing it now and it being corrupted the next moment.

I've been looking around Enterprise Manager and can't see anything. The closest I get is looking in the properties of the Lock / Processes. Not time efficient.

What I visualize (wish list) is to see what each user is currently accessing by listing the user's ID, Table, Primary Key of the record(s). I can tell by looking at this information what I need to know.

For example: If I issued a command in Query Analyzer "SELECT ... FROM orders where customerid = 'xyz';" the listing would show all the orders that were returned:
myloginid | ORDERS | 12345
myloginid | ORDERS | 21233
myloginid | ORDERS | 34322
someone else | INVENTORY | 1278312

(Hope this makes sense).

"The Road To Success Is Always Under Construction."

Post #858985

bitbucket-25253

Posted Wednesday, February 03, 2010 2:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 5,103, Visits: 20,215

Just a wild idea, which I hope will stimulate your creative juices.

Have you considered running sp_who, at least it will tell if some one is using the database, if no one, can you then do your fix up task with an exclusive lock on the table in question?

My wild idea, schedule a job to run sp_who say every 30 minutes and insert the results into a temp table, query the temp table, if the database name is not found, then do the fixing up.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #859044

EdA ROC

Posted Thursday, February 04, 2010 8:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394

Well, I now know about sp_who.

The database is our ERP database - "everyone's always in it". Hehe

I ran sp_who and there are 87 people in the database. All but one is "sleeping" / "AWAITING COMMAND".

If everyone's status is sleeping I could do my updates.

Nice suggestion, it's a start - "Brute force" is how one of my college professors would describe it.

"The Road To Success Is Always Under Construction."

Post #859715

bitbucket-25253

Posted Thursday, February 04, 2010 11:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 5,103, Visits: 20,215

Brute force nah with finesse / elegance.

This has got me intrigued ... been a long time since working with 2000, but here may be something to stir your creative juices even more.. if memory serves me correcting a Spid less than 50 is a system spid not a user spid. That said run the followng as an insert into a temp table.

SELECT spid,Db_Name(dbid) FROM Master..sysprocesses WHERE Spid > 49 AND DB_Name(dbid) 
LIKE '%Northwind%'

Then using the entries in the temp table run the following command
into another temp table.

DBCC INPUTBUFFER (spid value)

Then a select on the 2nd temp table with a LIKE clause for your table name.

Also look at fn_get_sql available from a 2000 update, and you should have same if not seach TECHNET for the hot fix.

fn_get_sql uses the sql_handle from sysprocess to return the SQL statement.. negating the need to use the DBCC command.

Being optimistic, if this all works out how about posting the code on the forum so others may learn ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #859827

Alvin Ramard

Posted Thursday, February 04, 2010 11:39 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383

Running "SP_WHO2 Active" may be more appropriate in this case since it only shows the active spids.

Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

Post #859832

« Prev Topic | Next Topic »

Permissions