Re: How to Run ASP natively in SQLServer7

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: JackV (vituja_at_consumer.org)
Date: 02/26/04 

Date: Thu, 26 Feb 2004 10:37:18 -0500

Thanks James. I just fond it. However my script is large then what the
system accepts. When I pasted in or opened my vbs code I was told it is
larger then the 3200 characters size.

Is there any way to increase this size? My code requires 8351...

"James Hokes" <nospam_nothanks_@nospamforme_nothanks.com> wrote in message
news:On1pUh9%23DHA.2308@tk2msftngp13.phx.gbl...
> 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

  • Re: Problem with ASP on Webserver
    ... There is no ActiveX object involved in this problem, we only request a ASP ... function in the script ... (Accept-Language:) ...
    (microsoft.public.windowsce.platbuilder)
  • Re: Problem with ASP on Webserver
    ... There is no ActiveX object involved in this problem, we only request a ASP ... function in the script ... (Accept-Language:) ...
    (microsoft.public.windowsce.platbuilder)
  • RE: export user accounts from NT 4.0 domain
    ... ATTENTION THE SCRIPT MUST BE RUNNED FROM A COMPUTER WHERE EXCEL IS ... from the information in a Microsoft Excel spreadsheet. ... Dim strLast, strFirst, strMiddle, strPW, intRow, intCol ... On Error GoTo 0 ...
    (microsoft.public.windows.server.scripting)
  • Re: Password Expire
    ... We have one fron end Edge server in our DMZ which passes email onto two ... I used to schedule a script to run every 24 hours on my Exchange 2003 ... Dim fso, txtarray, BodyText ... Call ProcessFolder (objContainer, numDays) ...
    (microsoft.public.exchange.admin)
  • Re: LDAP query information
    ... a "Dim" statement. ... execution of the script. ... ' Filter on computer object. ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)