In general terms you can only capture the information after the event, you can of course capture open transactions ( although they might not be doing much ) and you can sometimes capture the details of running processes - the main issue is that values returned are nearly always cumulative so don't really show what the current query is doing, and it's difficult to know if this is a totally new query - and - if you're using client side cursors it's almost impossible. Apparently Oracle has been able to do this since version blah blah blah ... however sql server can only tell you afterwards.

There's a number of white papers written by microsoft which may help. you're unlikley to get anywhere with sysprocesses or sp_who2 ( these are really sql 2000 ) you'll need to understand the dmvs to extract this information. I have a number of queries i use linked to reports etc. I will put all these on my web site in due course but I can't promise if it'll be before christmas. ( http://www.grumpyolddba.co.uk )