Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Classic ASP schedule execution on server

Reply
Thread Tools

Classic ASP schedule execution on server

 
 
JackV
Guest
Posts: n/a
 
      02-25-2004
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 (#.
'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=0n 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=0n 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=0n 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


 
Reply With Quote
 
 
 
 
middletree
Guest
Posts: n/a
 
      02-25-2004
An answer was provided for you in the SQLServer Programming group. Use a SQL
Server job.


"JackV" <(E-Mail Removed)> wrote in message
news:OY4Ryj9%(E-Mail Removed)...
> 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 (#.
> '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=0n 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=0n 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=0n 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
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to schedule execution of code? Virgil Stokes Python 1 03-15-2011 07:30 PM
private data stashed in local/global execution context of PyEval_EvalCode disappears down the execution stack sndive@gmail.com Python 9 11-14-2007 10:31 PM
Classic ASP + ASP.NET 2.0 on Windows Server 2003 64 bit: possible? thehobbit Windows 64bit 0 11-22-2006 09:55 PM
Schedule Class Execution Memi Lavi Java 7 07-02-2004 06:17 AM
Reboot / Restart Server in Schedule Task =?Utf-8?B?RnJhbmNpcw==?= MCSE 7 05-11-2004 05:24 PM



Advertisments