Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS activeX time out. Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2007 7:39 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
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
Post #434295
Posted Thursday, December 20, 2007 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #435256
Posted Thursday, December 20, 2007 11:11 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
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
Post #435326
Posted Friday, December 21, 2007 6:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 17, 2014 6:34 AM
Points: 260, Visits: 196
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
Post #435619
Posted Friday, December 21, 2007 7:36 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 10:47 AM
Points: 81, Visits: 280
Cheers Mike,

I'll give it a go..

Dave
Post #435636
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse