February 3, 2010 at 1:12 pm
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).
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 3, 2010 at 2:18 pm
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.
February 4, 2010 at 8:50 am
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.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 4, 2010 at 11:24 am
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 ...
February 4, 2010 at 11:39 am
Running "SP_WHO2 Active" may be more appropriate in this case since it only shows the active spids.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply