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. Advertising

  2. JackV

    middletree Guest

    An answer was provided for you in the SQLServer Programming group. Use a SQL
    Server job.


    "JackV" <> wrote in message
    news:OY4Ryj9%...
    > 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
    >
    >
    middletree, Feb 25, 2004
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Zeng
    Replies:
    1
    Views:
    387
    Lucas Tam
    Apr 20, 2005
  2. Memi Lavi

    Schedule Class Execution

    Memi Lavi, Jul 1, 2004, in forum: Java
    Replies:
    7
    Views:
    504
    Memi Lavi
    Jul 2, 2004
  3. jobs
    Replies:
    1
    Views:
    1,227
    sloan
    Jan 20, 2007
  4. Virgil Stokes

    How to schedule execution of code?

    Virgil Stokes, Mar 15, 2011, in forum: Python
    Replies:
    1
    Views:
    231
    Irmen de Jong
    Mar 15, 2011
  5. cct
    Replies:
    1
    Views:
    364
    [MSFT]
    Jun 24, 2005
Loading...

Share This Page