Classic ASP schedule execution on server

[JackV]

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\FogBugzDailyUploadfromHelpSt ar\HSW.MDB;persist
security info=false"
connectionstring = "provider=microsoft.jet.oledb.4.0;data source=
\\Devadp\cdrive\Inetpub\wwwroot\applications\helps tar\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&",'"&sCust omerEmail&"','" &
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:on 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:on 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:on 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]

An answer was provided for you in the SQLServer Programming group. Use a SQL
Server job.
"JackV" <vi****@consumer.org> wrote in message
news:OY****************@TK2MSFTNGP11.phx.gbl...

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\FogBugzDailyUploadfromHelpSt ar\HSW.MDB;persist
security info=false"
connectionstring = "provider=microsoft.jet.oledb.4.0;data source=
\\Devadp\cdrive\Inetpub\wwwroot\applications\helps tar\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&",'"&sCust omerEmail&"','" &
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:on 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:on 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:on 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