Re: How to Run ASP natively in SQLServer7

From: James Hokes (nospam_nothanks__at_nospamforme_nothanks.com)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 14:45:22 -0500

JackV,

In SQL Agent, make a new job, and the step type is 'ActiveX Script'

James Hokes

"JackV" <vituja@consumer.org> wrote in message
news:exx09U9%23DHA.2432@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I created an ASP page (using vbscript) which processes data. Instead of
> having the web page run nighlty using the schedule in Win2000 server to
> start this page, is there a way to run the asp code natively in
SQLServer7?
> I remember hearing something about this a while back. Any info would be
> helpful. Thanks.
>
> Here 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"
> 'response.write SQL:responsd.end
> Set RS4 = Conn.Execute(SQL)
> 'rs4("ixPersonOwner")
>
> '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
> 'response.write rs("EMailAddress"):response.end
> '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
> 'err=0:on error resume next
> 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) &"'"
> 'response.write SQL:responsd.end
> Set RS3 = Conn.Execute(SQL)' Insert in to FogBug.BUG table one row case
> header
> 'rs3("ixbug")
>
> '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(mid(RemoveQuotes(rs("fldMemo")),1,128))
> 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&")"
> 'Response.Write SQL:Response.end
> 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 &"'"
> 'response.write SQL:response.end
> 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
> 'This subroutine will send notification email to the admin of the
project
> "From Helpstar"
> 'IF the notify flag is ok to send that email
> err=0:on Error resume next
> SQL1="Select * from Person where ixperson=" & PersonID &" and fnotify=1"
> 'response.write sql1:response.end
> Set RS00 = Conn.Execute(SQL1)' Insert in to FogBug.BUG table one row
case
> header
>
> 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
> %>
>
>

Relevant Pages

  • [ANN] rack-cache 0.3.0
    ... Responses marked as explicitly public are cached even when the ... request includes an Authorization or Cookie header. ... BUG: ...
    (comp.lang.ruby)
  • How to Run ASP natively in SQLServer7
    ... 'This vbscript communicates with the helpstar database located locally at ... 'Otherwise new request will be inserted. ... Insert one row of header data into the BUG table ... 'within the BUG table everytime a new row is inserted. ...
    (microsoft.public.sqlserver.programming)
  • [REVS] Forging HTTP Request Headers with Flash ActionScript
    ... Forging HTTP Request Headers with Flash ActionScript ... A similar syntax will send POST request (with the same header, ...
    (Securiteam)
  • Write-up by Amit Klein: "Forging HTTP request headers with Flash"
    ... Forging HTTP request headers with Flash ... A similar syntax will send POST request (with the same header, ...
    (Bugtraq)
  • Re: if-modified-since question (protocol problem?)
    ... request, but in response you are interested only if your request ... To base the decision on whether there is something 'new to show' would require knowledge about something 'old' - a cached resource. ... No Last-Modified header is sent in the response, ...
    (comp.infosystems.www.authoring.html)