| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-02-26 : 10:15:00
|
| hello,is the current activity in the enterprise manager held in a table, ie, can I display the current activity via a sql statement or stored procedure ?thank you for any info.Jamie |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-02-26 : 10:24:47
|
| Have a look at sp_who and sp_who2 |
 |
|
|
claire
Starting Member
19 Posts |
Posted - 2004-02-26 : 10:28:44
|
| also master.dbo.sysprocesses |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-02-26 : 10:33:13
|
| cheers ehorn and claire, I'll look into it. |
|
|
|
sphadke
Yak Posting Veteran
55 Posts |
Posted - 2004-02-26 : 12:05:08
|
| If you want to store the contents of sp_who or sp_who2 in a table make a copy of the proc call it sp_who_2_<somethinghere> and in the last SELECT statement use the INTO <table name> and you are good to go.eg:select spid, ecid, status, loginame=rtrim(loginame), hostname, blk=convert(char(5),blocked), dbname = case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end ,cmdinto ##tempfrom master.dbo.sysprocesseswhere spid >= @spidlow and spid <= @spidhighSachin |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-02-27 : 05:22:29
|
| thanks for the tip Sachin.can I ask another question , a follow up really,is it possible to show the sql statements that each spid is running ?is this info also stored in a system table ? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-02-27 : 06:57:02
|
| Nigel, nice example, but where the heck are you getting command from ?is it from running dbcc input buffer against the spid, will that give me the sql statement ? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-27 : 07:24:14
|
| Yep - it runs that against every spid.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
current activity