December 18, 2007 at 7:39 am
Windows 2000 server std
SQL 2000 std
Hello all,
I having a timing out issue, not sure best way to resolve it
I've got a daily import running that imports the ISA log files into a table.
I Then have this ActiveX script embeddeed in a DTS, this runs once a week.
What it does is..
-Loops through all records in a table and retrieves their a users name.
-Finds the name in AD and retrieves the email address for the user
-Then gets all the records for that user in a table
-Then emails the list to the user
It happily does 1 days worth of data ( 130k records )
But with two days in (265k records) it just hangs and times out.
i've tried clearing out any unwanted data as part of the import routine, but it didnt make the slightest bit of difference
any ideas?
My next step would be to Create an 32bit app and have that manage the data better.
i really didnt want to go down that path thou 🙁
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
' db connection
set dbConn= createObject("ADODB.connection")
dbConn.mode = adModeReadWrite
dbConn.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sql.user;Initial Catalog=ITservices;Data Source=172.20.1.20; network library=dbmssocn"
dbConn.open
'on error resume next
Set RSList = createObject("adodb.recordset")
SQL = "SELECT Tbl_ISALOG.[cs-username] AS UN FROM "&_
" Tbl_ISALOG "&_
" LEFT OUTER JOIN tbl_Exclusion ON Tbl_ISALOG.[cs-username] <> tbl_Exclusion.ExclusionName "&_
" GROUP BY Tbl_ISALOG.[cs-username] ORDER BY Tbl_ISALOG.[cs-username] "
set RSList = dbconn.execute(SQL)
while not rsList.eof
UserN =rslist("UN")
' GET THE EMAIL ADDRESS FROM A.D.
'#########################################################################################
' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
'objConnection.Properties("User ID") = "domainname\username"
'objConnection.Properties("Password") = ""
objConnection.Open "Active Directory Provider"
' Search for all user objects. Sort recordset by DisplayName.
strBase = " "
strFilter = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & UserN & "))"
strAttributes = "mail"
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
Set objRecordSet = objConnection.Execute(strQuery)
If objRecordSet.EOF Then
' NO EMAIL ADDRESS IS FOUND SO MOVE TO NEXT
rslist.movenext
End If
' Loop through results
Do Until objRecordSet.EOF
UseEmailAddress = objRecordSet.Fields("mail")
objRecordSet.MoveNext
Loop
msgbox( UseEmailAddress)
' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objConnection = Nothing
Set objRecordSet = Nothing
'#########################################################################################
SQL2 = "SELECT [cs-username] AS username, [date] AS date, [r-host] AS Rhost, [action] as action, [r-port] as rport "&_
" FROM Tbl_ISALOG "&_
" WHERE ([cs-username] = '" & UserN & "') "&_
" GROUP BY [r-host], [cs-username], [date], [action], [r-port] "
Set RSWeb = createObject("adodb.recordset")
Set RSWeb = dbconn.execute(SQL2)
Do Until RSWeb.eof
UserName = rsweb("Username")
URLDate = rsweb("Date")
URL = rsweb("rhost")
rport = rsweb("rport")
action = rsweb("action")
' CREATE HTML BODY
WriteLine = WriteLine & " "
WriteLine = WriteLine & "<td"
if action = "Failed" then
WriteLine = WriteLine & " bgcolor='red' "
end if
WriteLine = WriteLine & "> "
WriteLine = WriteLine & "<td "
if action = "Failed" then
WriteLine = WriteLine & " bgcolor='red' "
end if
WriteLine = WriteLine & "> "
WriteLine = WriteLine & "<td "
if action = "Failed" then
WriteLine = WriteLine & " bgcolor='red' "
end if
WriteLine = WriteLine & "> "
WriteLine = WriteLine & "<td "
if action = "Failed" then
WriteLine = WriteLine & " bgcolor='red' "
end if
WriteLine = WriteLine & "> "
WriteLine = WriteLine & "<td "
if action = "Failed" then
WriteLine = WriteLine & " bgcolor='red' "
end if
WriteLine = WriteLine & "> " & VbCrLf
WriteLine = WriteLine & " "
loop
set RSWeb = nothing
' STICK MY EMAILER IN HERE.
WriteLine = " "
WriteLine = " "
set msg = CreateObject("CDO.Message")
msg.From = "sql.server@2sfg.com"
msg.To = UseEmailAddress
msg.Subject = "Your Web Log for " & date()
msg.HTMLBody = WriteLine
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "2sfg-exchange"
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
set msg = nothing
rslist.movenext
wend
set rslist = nothing
set dbConn=nothing
Main = DTSTaskExecResult_Success
End Function
December 20, 2007 at 9:11 am
First, moved this to the DTS area.
Second, do the resources on the server get beatan up? Memory low, disk/CPU high? I might try moving this to a workstation or another server, running it and seeing if it works.
Does it work interactively or always hang with 2 days of data?
I lean towards resource issues, but it could be something else. Not sure what it would be. Are you putting this in Excel? 130k rows should cause issues, so I don't think that is it.
December 20, 2007 at 11:11 am
Thanks for the reply Steve, Sorry, I didnt know there was a DTS area.. Thanks for moving it for me.
yep. after looking into the SQL server specs, its running on an Dell server (1.6ghz, with 390mb ram ) tsk.... after complaining about the hardware that was being used, I moved it to a much faster server it works a lot better, but still times out with 5 days worth of data in
🙁
cheers
Dave
December 21, 2007 at 6:59 am
Just a thought...You may want to instantiate and initialize your objects outside of your while loop and modify the properties inside the loop. Your current approach will cause for performance nightmares with the constant build up and tear down of the objects. WSH did not have the most robust garbage collection and this can often cause problems with looping operations.
-Mike Gercevich
December 21, 2007 at 7:36 am
Cheers Mike,
I'll give it a go..
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply