Classic ASP schedule execution on server

Discussion in 'ASP General' started by JackV, Feb 25, 2004.

  1. JackV

    JackV Guest

    Hi All,

    I have a pure asp (Vbscript) page that manipulates data between MSAccess and
    SQLServer2000. I'm trying to use the Scheduler app on the IIS server to run
    this page nightly but there must be a better way of doing this.

    How can I make my code in to an object and install it on the server and have
    it schedule to run nightly?


    Below is the code:

    dim connectionstring, rs, db, Querystring

    'This vbscript communicates with the helpstar database located locally at
    'connectionstring = "provider=microsoft.jet.oledb.4.0;data
    source=c:\Program
    Files\FOGBUGZ\website\FogBugzDailyUploadfromHelpStar\HSW.MDB;persist
    security info=false"
    connectionstring = "provider=microsoft.jet.oledb.4.0;data source=
    \\Devadp\cdrive\Inetpub\wwwroot\applications\helpstar\HSW.MDB"

    'for the purpose of extracting opened request assigned to the Applications
    queue.
    'Once data is found, this script will insert them into Fogbugz under the
    "From Helpstar" project (#8).
    'If duplicate helpstar requests (identifed by their case#) are found, they
    will not be re-inserted into Fogbugz.
    'Otherwise new request will be inserted.
    'In the Computer field in Fogbugz, helpstar request# are stored for
    reference in order to link back to the original case

    '===========================================================================
    =================

    'Prerequsites:
    ' 1) The Helpstar database (HSW.MDB) must be local to this machine?
    ' 2) This script runs on a web page. Must have this script run nightly
    perhaps on the server by executing this url?


    '***************************************************************************
    *****************

    '1. 'Open dataset to FogBogz to prep insert of formatted Data
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "FogBugZDataUpload","InsertUpdate","1234"
    set db = Server.CreateObject("ADODB.Connection")
    db.Open connectionstring
    Set rs = Server.CreateObject("ADODB.Recordset")

    '2. 'Which person is assigned to the project 'From Helpstar'?
    SQL="Select ixPersonOwner from Project where ixProject=8"
    Set RS4 = Conn.Execute(SQL)

    '3. 'Get Recordset of All Closed Req (bugs) from Helpstar Access DB 'by
    executing the MSAccess query listed in the 'QueryString above
    AllOpenedReqForAppDev

    Querystring = "SELECT tblServiceRequest.ID, tblServiceRequest.QueueID, "
    Querystring=Querystring&" tblUser.EMailAddress, tblMemo.ID,
    tblServiceRequest.Title, tblMemo.fldMemo,"
    Querystring=Querystring&" tblServiceRequest.Status,
    tblServiceRequest.TimeLogged, tblServiceRequest.TimeClosed"
    Querystring=Querystring&" FROM (tblServiceRequest INNER JOIN tblMemo ON
    tblServiceRequest.ID = tblMemo.RequestID)"
    Querystring=Querystring&" INNER JOIN tblUser ON
    tblServiceRequest.RequesterID = tblUser.ID"
    Querystring=Querystring&" WHERE (((tblServiceRequest.QueueID)=36 Or
    (tblServiceRequest.QueueID)=35"
    Querystring=Querystring&" Or (tblServiceRequest.QueueID)=21 Or
    (tblServiceRequest.QueueID)=531 Or"
    Querystring=Querystring&" (tblServiceRequest.QueueID)=550) AND
    ((tblServiceRequest.TimeClosed)=#12/31/9999#))"
    Querystring=Querystring&" ORDER BY tblServiceRequest.ID DESC ,
    tblServiceRequest.TimeLogged DESC;"

    rs.Open Querystring, db, 3, 3

    '4. Insert one row of header data into the BUG table 'Response.Write
    rs("fldMemo"):Response.end
    do
    idx=0
    OpenCloseStatus=1 '0 for closed and 1 for opened
    DTOPEN=rs("TimeLogged")
    DtRsolved=null 'rs("TimeClosed")
    DtClosed=null 'rs("TimeClosed")
    Stitle=rs("Title")
    ixproj=8
    ixArea=20
    ixPersonOpened=rs4("ixPersonOwner")
    ixPersonAssigned=rs4("ixPersonOwner") 'Use 1 to signify CLOSED otherwise
    use a valid ID
    ixstatus=1 '1 for opened(active) and 2 for resolved(closed)
    ixPriority=1
    ixFixFor=1
    sVersion=""
    sComputer= "HELPSTAR #"& rs("tblServiceRequest.ID")
    hrsOrigEst=0
    hrsCurrEst=0
    hrsElapsed=0
    c=0
    sCustomerEmail=""
    sGPFID=""
    ixMailBox=0
    ixCategory=1 '1=bug
    SbugDispacho=""

    SQL="Insert into BUG values("'& Idx&","
    SQL=SQL&OpenCloseStatus&",'"& DTOPEN &"','"& DtRsolved &"','"& DtClosed
    &"',"
    SQL=SQL&"'"&RemoveQuotes(Stitle)&"'," & ixproj &"," & ixArea &","
    SQL=SQL&ixPersonOpened &","&ixPersonAssigned&","
    SQL=SQL&ixstatus&","&ixPriority&"," & ixfixFor &",'"
    SQL=SQL&sVersion&"','" & sComputer&"'," & hrsOrigEst &","
    SQL=SQL&hrsCurrEst&","&hrsElapsed&","&c&",'"&sCustomerEmail&"','" &
    sGPFID&"',"
    SQL=SQL&ixMailBox&","&ixCategory&",'"&sbugDispacho&"'"
    SQL=SQL&")"

    '5. Double check to make sure you are not inserting a duplicate helpstar
    request #
    'This function call will return either a 0 for not already there or a 1
    if the entry exists
    X=AlReadyInFogbugz(rs("tblServiceRequest.ID"))

    if x=0 then
    Set RS2 = Conn.Execute(SQL)
    'if err<>0 then response.write SQL:response.end
    'SendNotifyMail rs4("ixPersonOwner")
    end if

    'Now get the BugIndex number for Fogbugz to insert in the next table. This
    index is self generated
    'within the BUG table everytime a new row is inserted. SQLServer keeps
    track of this bug
    SQL="Select ixbug from BUG where
    substring(sComputer,11,(len(sComputer)))='" & mid(sComputer,11) &"'"
    Set RS3 = Conn.Execute(SQL)' Insert in to FogBug.BUG table one row case
    header

    '6. Insert into the BUGEVENT table. This table can contain # of rows for
    each bug event

    if X=0 then
    BUGID=rs("tblServiceRequest.ID")
    do while rs("tblServiceRequest.ID")=BUGID or rs.eof
    sFilename=""
    ixBugEvent=0
    ixBug=rs3("ixbug") 'BUGID
    sVerb="Opened"
    dt=DTOPEN 'now
    sfileStatus=""
    ixPerson=rs4("ixPersonOwner")

    s=trim(RemoveQuotes(rs("fldMemo"))) &" From: "&rs("EMailAddress")
    fmail=0
    fExternal=0
    sChanges=""
    SQL="Insert into BUGEVENT values('"
    SQL=SQL&sFilename&"',"& ixBug &",'"& sVerb &"','"& dt &"',"
    SQL=SQL&"'"&sfileStatus&"'," & ixPerson &",'" & s &"',"
    SQL=SQL&fmail &","&fExternal&",'" & sChanges&"'"
    SQL=SQL&")"

    Set RS2 = Conn.Execute(SQL) 'Insert into the FogBugz.BUGEVENT table
    rs.movenext
    loop
    end if 'if duplicate not found
    'Response.Write "HELLO":Response.end
    rs.movenext
    loop until RS.eof
    response.write "COMPLETE "& NOW
    '-------------------------------------------------
    Function RemoveQuotes(Val)
    'This function will replace double or single quotes with a single
    'or double hash mark instead
    err=0:eek:n error resume next

    Dim CleanValue,ChrVal

    CleanValue=""
    For I = 1 to len(VAL)
    if mid(Val,I,1)=chr(39) or mid(val,I,1)=chr(34) then
    Chrval="`"
    else
    Chrval= mid(Val,I,1)
    end if
    CleanValue=CleanValue & Chrval
    Next

    RemoveQuotes=CleanValue
    End Function

    '--------------------------------------------------
    Function AlReadyInFogbugz(idx)
    SQL1="Select * from BUG where sComputer='" & sComputer &"'"

    Set RS0 = Conn.Execute(SQL1)' Insert in to FogBug.BUG table one row case
    header
    err=0:eek:n error resume next

    rs0.movefirst
    x=rs0("ixbug")

    if err=0 then
    AlReadyInFogbugz=1
    else
    AlReadyInFogbugz=0
    end if
    End Function

    '--------------------------------------------------
    Sub SendNotifyMail(PersonID)
    dim objEMail

    err=0:eek:n Error resume next
    SQL1="Select * from Person where ixperson=" & PersonID &" and fnotify=1"

    Set RS00 = Conn.Execute(SQL1)
    rs00.movefirst
    x=rs00("semail")
    If err=0 then
    'Ok to send mail
    set objEMail = Server.CreateObject("cdonts.NewMail")
    objEMail.To = trim(x)
    objEMail.From = "test"
    objEMail.subject = "Mail Subject"
    objEMail.body = "Body text"
    objEmail.BodyFormat = 0
    objEmail.MailFormat = 0
    response.write x:response.end
    objEMail.send
    set objEMail = nothing
    end if
    End Sub
     
    JackV, Feb 25, 2004
    #1
    1. Advertisements

  2. JackV

    middletree Guest

    An answer was provided for you in the SQLServer Programming group. Use a SQL
    Server job.
     
    middletree, Feb 25, 2004
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.