View Single Post
  #7 (permalink)  
Old January 14th, 2009, 05:27 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default A few minor points about that code...

Code:
<%
Option Explicit
Dim rsqdb, SQL
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
' set rsqdb = server.CreateObject("ADODB.Recordset") ' *** SEE BELOW ***
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myserver;Initial Catalog=qdb;UID=Reports;PWD=;"
SQL = "use msdb; " _
    & " select distinct j.Name as JobName, j.description as JobDescription, " _
    &           " h.run_date as  LastStatusDate, " _
    & " ( case h.run_status  " _
    & " when 0 then 'Failed'  " _
    & " when 1 then 'Successful'  " _ 
    & " when 3 then 'Cancelled'  " _
    & " when 4 then 'In Progress'  " _
    & " else 'INVALID RUN STATUS' " _
    & " end ) as JobStatus " _
    & " from sysJobHistory h, sysJobs j " _
    & " where j.job_id = h.job_id and h.run_date =  " _
    & " (select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id) " _
    & " order by JobStatus ")

Response.Write "DEBUG SQL: " & SQL & "<HR>" & vbNewLine

set rsqdb = conn.Execute (SQL)

if rsqdb.EOF then 
    response.Write "No records returned from query"
Else
    response.write "At least on record returned; first name = " & rsqdb("name")
End If
set rsqdb = nothing
set conn = nothing
%>
(1) The line
set rsqdb = server.CreateObject("ADODB.Recordset")
is a waste of code. Because you will later do
set rsqdb = conn.Execute( SQL )
which will WIPE OUT the prior reference to the created recordset. So the one created via createObject will just have to be garbage collected without ever having been used.

(2) By assigning the SQL string to a variable, first, you are able to do a DEBUG output of the SQL. Thus you can easily go test that same SQL in some DB tool (e.g, Query Analyzer).

(3) I added a semicolon after the "use msdb". Yes, I know. It shouldn't be necessary. SHOULDN'T and ISN'T are nearly synonyms. Nearly.

(4) I added an ELSE to the CASE WHEN. Just a bit of paranoia on my part. But "a little paranoia is healthy for programmers and their programs."

(5) I added a positive check for the recordset EOF along with a message indicating it happened. More paranoia.

None of my changes are necessary or even important. Just things that I've found myself doing in my old age as part of my ongoing feeling that computers are out to get me.


Last edited by Old Pedant; January 14th, 2009 at 05:34 PM..
Reply With Quote