InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:34:31 GMT20http://www.sqlservercentral.com/Forums/Topic858985-54-1.aspxRunning "[b]SP_WHO2 Active[/b]" may be more appropriate in this case since it only shows the active spids.Thu, 04 Feb 2010 11:39:53 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic858985-54-1.aspxBrute 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.[code="sql"]SELECT spid,Db_Name(dbid) FROM Master..sysprocesses WHERE Spid > 49 AND DB_Name(dbid) LIKE [i][u]'%Northwind%'[/u][/i][/code]Then using the entries in the temp table run the following commandinto another temp table. [code="sql"]DBCC INPUTBUFFER ([i][u]spid value[/u][/i])[/code]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 ... Thu, 04 Feb 2010 11:24:03 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic858985-54-1.aspxWell, 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. [b]All but one is "sleeping" / "AWAITING COMMAND".[/b]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.Thu, 04 Feb 2010 08:50:50 GMTEdA ROChttp://www.sqlservercentral.com/Forums/Topic858985-54-1.aspxJust 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.Wed, 03 Feb 2010 14:18:32 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic858985-54-1.aspxThere'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 | 12345myloginid | ORDERS | 21233myloginid | ORDERS | 34322someone else | INVENTORY | 1278312(Hope this makes sense).Wed, 03 Feb 2010 13:12:13 GMTEdA ROC