April 8, 2003 at 1:12 am
I am running the activeX script below in a SQL 2000 DTS package to delete some data in a db called opms through a stored procedure.
Although the connectiontimeout is set it gets me a timeout after ca. 60secs
and stating the line oConn.Execute sSQL
timed out. If I only delete few rows it works but If the number is big it times out. Is there something I missed out?
Thanks for your help
mipo
Function Main()
Dim oFS ' filesystem object
Dim oFile ' File object
Dim strNextLine ' text line from file
Dim TableToBeCleaned
Dim KeepMonthsInDatabase
Dim BackUpPath
Dim sSQL ' SQL string
Dim GetPara
Dim sConn ' ADO Connection string
Dim oConn ' connection object
Set oConn = CreateObject("ADODB.Connection")
Set oFS = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
sConn = "Provider=SQLOLEDB.1;Persist Security Info=True;"
sConn = sConn & "Password=smpo;"
sConn = sConn & "User ID=opms;"
sConn = sConn & "Initial Catalog=opms;"
sConn = sConn & "Data Source=Win2000"
oConn.ConnectionTimeout =180
oConn.Open sConn
set oFile = oFS.OpenTextFile("C:\winnt\opms.ini", ForReading)
strNextLine = ""
Do until oFile.AtEndOfStream
strNextLine = ofile.Readline
If Left(strNextLine, 16) ="TableToBeCleaned" THEN
Temp=split(strNextLine,"=")
GetPara = split(Temp (1),",")
DBCleanerBackUpPath = "D:\MSSQL2000\BackUpFlatFiles"
sSQL = "EXEC p_DBCleanerDeleteRecords "
sSQL = sSQL & GetPara (0) & ", "
sSQL = sSQL & GetPara (1) & ", "
sSQL = sSQL & "'" & DBCleanerBackUpPath & "'"
oConn.Execute sSQL
End If
Loop
' Clean up objects
Set oConn = Nothing
Set oFile = Nothing
Set oFS = Nothing
Main = DTSTaskExecResult_Success
End Function
April 10, 2003 at 8:47 pm
If at all possible, move the execute outside the loop. Concatenate a where clause if you need to then perform the execute once. There is also a way to execute asynchronously. Like this "cmd.Execute "delete text here", conn, adAsyncExecute"
If you need more explanation search Books Online for "ado asynchronous"
Darren
Darren
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply