sp_who
-
Hai,
How can i take the result of sp_who to a table
subban -
Here's one way:
if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#t_sp_who_output'))
drop table #t_sp_who_output
go
CREATE TABLE #t_sp_who_output (
[spid] [smallint] ,
[ecid] [smallint] ,
[status] [nchar] (30) ,
[loginame] [nvarchar] (128) ,
[hostname] [nchar] (128) ,
[blk] [char] (5) ,
[dbname] [nvarchar] (128) ,
[cmd] [nchar] (16)
)
GO
insert into #t_sp_who_output
exec sp_who
select * from #t_sp_who_output
Hope this helps!
Chris
-
I wonder y u need to insert those to a table.
My Blog:
http://dineshasanka.spaces.live.com/ -
Hi Dinesh
see, what my requirement is, at anytime i should be able to see who all are logged in my sql server through my web browser. in fact this sql server is a remote server.
what i will do is , i will take the result of sp_who to a table, and show it in the web browser through my asp code
is there any other way to do this,
subban -
If U R using ADO, U can run stored procedure via ASP.
My Blog:
http://dineshasanka.spaces.live.com/ -
This URL will help
HOWTO: Call SQL Server Stored Procedures from ASP
My Blog:
http://dineshasanka.spaces.live.com/ -
Dinesh is right. I assumed you had a bigger requirement than that - such as a need to audit historical connections. In that case, it would make sense to dump sp_who to a table and then summarize from there into the audit table. But now that I think more about it (notice that this is a more favorable time of day for such activity), even that wouldn't be much of a requirement, since you could just summarize from sysprocesses.
Cheers,
Chris
-
Forget temp tables and the like. Here's what I run every 5 minutes on the server.
INSERT INTO dbo.tblActivityLog ( SPID, LastUpdate, Status, Login, HostName , BlkBy, DBName, Command, CPUTime, DiskIO , LastBatch, ProgramName) SELECT sp.SPID, GetDate(), RTRIM(sp.Status), RTRIM(sp.Login) , RTRIM(sp.HostName), RTRIM(sp.BlkBy), RTRIM(sp.DBName) , RTRIM(sp.Command), sp.CPUTime, sp.DiskIO , RTRIM(sp.LastBatch), RTRIM(sp.ProgramName) FROM OPENQUERY ( <YOUR SERVER NAME> -- NOTE: SET FMTONLY OFF is required to bypass build time -- checking of statement syntax. , 'SET FMTONLY OFF EXEC master..sp_who2'  
as SP--------------------
Colt 45 - the original point and click interface
-
Phil,
There's something wrong with the formatting in your query - a smilie snuck in.
But having said that, the rest of what I'm going to post feels a little bit odd, but I'm just curious about something. I don't understand your "forget temp tables and the like" comment. Is there some significant way in which your script differs from what I posted? I feel like I'm missing something here ...
My scriptlet specified a create table statement, which wouldn't have to be a temp table, of course, but when I post samples I like them to be reusable and self-standing. Your code uses a remote rowset function as the record source, but you would still have to create that tblActivityLog first, right? So it's still create a table and then run a periodic insert. Whether Subban wants a temp table or a permanent table should be based on his business needs. Moreover, I think that when Dinesh got Subban to clarify what was really needed, it became clear that all Subban needs to do is just execute sp_who and forget about persisting the results in tables altogether.
All this left me wondering what you were trying to point out in your post. But like I said, I might be missing something here. If you have time, can you shed some light on what prompted your post? I figured this one was a dead horse after Dinesh nailed it.
Chris
-
Chris
The forum formatting for code isn't what it used to be, but they'll get there. That's one of the things that makes this a great site.
It also seems to have lost half on my post
in which I went on to elaborate that running this query has a couple of benefits.a) you can capture the information from sp_who for trend analysis and troubleshooting.
b) you can specify which columns you view. eg: just the spid, dbname, DiskIO and CPUTime.
c) Running the output to a table removes the overhead of executing sp_who on the ASP page. The sp_who2 procedure creates it's own temp table and does a lot of reformatting on the results. Reading from you own table is a very simple select statement.
Hope this clears up your curiosity
--------------------
Colt 45 - the original point and click interface -
Gotcha. Yeah, that makes more sense. And I guess I was missing something (half your post) after all! But I had to ask. I just looked at the post and thought (based on other posts) "this doesn't seem like Phill..."
And yes, it would be a nice mod to have a style in the dropdown box for code, wouldn't it? As it stands, I think you'd have to click the HTML button and then put the <pre> tags in yourself.
I am not script: <script>alert("hello!")</script> And these are not smilies: 
Well, after that very scientific test, that didn't work either. Hmm, I guess that's why we see lots of posts that have a space between the parens and the winking semi-colon, eh?
Cheers,
Chris
-
There is a style called Formatted that puts in <PRE></PRE> tags, but it seems to do on a line by line basis. In the old forums you used to be able wrap scripts in
tags. This worked a bit better than the current Formatted style.I also don't like the use of the <P></P> tags. I keep thinking I'm putting in two lines
--------------------
Colt 45 - the original point and click interface -
Phill,
Would you mind posting your scriptlet in its entirety again (without the smiley)? The code sounds like a good idea, but I'm having problems getting it to work.
Thanks,
Jon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply