When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Thursday, July 15, 1999

Just What is SQL Doing?
By Julian

Have you ever wondered what in the hell SQL server is doing at any given moment? Wouldn't it be nice to be able to view the inner workings of SQL at any point in time from the web? Well, it's quite possible.

- continued -

The trick, is to query the sysprocesses table. This tip, from Julian Sitkewich queries the sysprocesses table, showing us minute information on connections, statuses and processes running or sleeping on the server!

All we have to do is create a simple ASP page and paste the following code into it:

<% Dim conn Set conn = Server.CreateObject("ADODB.Connection") conn.Open "DSN=pubs" 'Use you own connection string here! 'Create a recordset and query sysprocesses Dim rs Set rs = Server.CreateObject("ADODB.Recordset") rs.Open "SELECT * FROM master..sysprocesses WHERE hostname <> ''", conn 'We query where hostname is NOT empty, so that we get the listing of 'user processes, not system processes. %> <HTML> <BODY> <TABLE border=1> <TR> <% For i = 0 to rs.Fields.Count - 1 %> <TH> <FONT SIZE=2> <%=rs.Fields(i).Name </FONT> </TH> <% Next %> </TR> <% Do While Not rs.EOF %> <TR> <% For i = 0 to rs.Fields.Count - 1 %> <TD> <FONT SIZE=2> <=rs(i)%></FONT> </TD> <% Next %> </TR> <% rs.MoveNext
Loop %> </TABLE> </BODY> </HTML> <%
'Always important, clean up time!!
Set rs = Nothing
Set conn = Nothing

So, what does this code do? Well, it's pretty straight forward, I think. It first creates a connection object and connects to the database of your choice. Next, a recordset object is created and a query on sysprocesses is performed. This recordset object is then displayed in a table. Not much to it, really. Simple, yet powerful.

Be sure to try it out, and see SQL's current status! :)

Julian is a software developer who currently spending his time developing a web-based project management software using Java. Most of his experience is in the e-commerce/Internet development arena and prefers to develop with Java, ASP, JSP, XML, Oracle, SQL Server, and UNIX (in no particular order). Previous to his current endeavors, Julian worked for an online learning company where he designed and implemented a large, distributed online learning application.

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article