connection timeout in ADO and DTS package SQL 2000

• mipo

  Ten Centuries

  Points: 1346

  More actions

  April 8, 2003 at 1:12 am

  #175576

  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

• dgermundson

  Hall of Fame

  Points: 3175

  More actions

  April 10, 2003 at 8:47 pm

  #453827

  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