To read the article online, visit http://www.4GuysFromRolla.com/webtech/071599-2.shtml

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.

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!!
rs.Close
conn.Close
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! :)



Article Information
Article Title: Just What is SQL Doing?
Article Author: Julian
Published Date: Thursday, July 15, 1999
Article URL: http://www.4GuysFromRolla.com/webtech/071599-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers