SQL Server Performance

Timeout Expired

Discussion in 'SQL Server DTS-Related Questions' started by hjyoungii, Nov 28, 2006.

  1. hjyoungii New Member

    I have a Visual Basic ActiveX Script that keeps timing out. This is in a DTS package that only contains the ActiveX.<br /><br />We are using MS SQL 2000 with SP 2.<br /><br />'**********************************************************************<br />' Visual Basic ActiveX Script<br />' 2/4/06<br />'mkb - Generates ongoing production .csv file<br />' <br />''************************************************************************<br /><br />Function Main()<br /><br />dim oConn<br />dim oRS<br /><br />'mkb - instantiate the ADO objects<br />set oConn = CreateObject("ADODB.Connection")<br />set oRS = CreateObject("ADODB.Recordset")<br /><br />Set pkg = DtsGlobalVariables.Parent<br />Set s = p.steps(0) ' assumes step(0) is the activeX task<br />s.ConnectionTimeout = 180<br /><br /><br />'mkb - set the connection properties<br />oConn.Open = "Provider=SQLOLEDB.1;Data Source=COLSQL; Initial Catalog=MPP;user id = 'sa'<img src='/community/emoticons/emotion-4.gif' alt=';p' />assword='doGGone' "<br />strSQL = "exec mpp_sopproductioncurrent2"<br /><br />strCurrentDate=CStr(DatePart( "m", Date))+"."+CStr(DatePart( "d", Date))+"."+CStr(DatePart( "yyyy", Date))<br /><br />'mkb - set up for file creation<br />Set fs = CreateObject("Scripting.FileSystemObject")<br />strPath = "\pvnt2esopBackups and data load filesProduction ongoing " + strCurrentDate +".csv"<br />varPath = strPath<br /><br /><br />oRS.Open strSQL, oConn<br /><br />Set TextFile = fs.CreateTextFile(varPath, true)<br /><br />Do While oRS.EOF = False<br /><br />'mkb - get inventory<br />set Flds = oRS.Fields<br />set varModel_Desc = Flds("Model_Desc")<br />set varLine_Desc = Flds("Line_Desc")<br />set varType_Desc = Flds("Type_Desc")<br />set varExpr1 = Flds("type")<br />set varl1month = Flds("l1month")<br /><br /><br />'mkb - populate csv file<br /><br />TextFile.WriteLine varType_Desc + ", " + varLine_Desc + ", " + varModel_Desc + ", " + CStr(varExpr1)+ ", " + CStr(varl1month) + ", " + ", "+", "+ ", " + ", " + ", "+", "+ ", "+ ", " + ", "+", "+ ", "+ ", " + ", "+", "+ ", "+ ", " + ", "+", "+ ", "+ ", " + ", "+", "+ ", "<br /><br />'mkb - next file<br />oRS.movenext<br /><br />loop<br /><br />TextFile.Close<br /><br />'mkb - close connections<br />oRS.Close<br />oConn.Close<br /><br />'mkb - report success<br />Main = DTSTaskExecResult_Success<br />End Function<br /><br /><br /><br />
  2. satya Moderator

    What that DTS package supposed to do?
    Check where it is failing by enabling the DTS package log for more information.
    Also test and apply latest service pack SP4 for SQL server.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. ranjitjain New Member

    try this:

    1>s.ConnectionTimeout = 0
    2>there is a property of recordset object where we can specify the cursortype to be of clientside.
    i.e.
    oRS.cursortype=aduseclient
    something similar, but check the exact syntax.
  4. hjyoungii New Member

    The DTS package runs a stored procedure and creates a file that we then format and send to another server as an Excel file.
  5. MohammedU New Member

    I believe it can done without VB script too...
    Don't you think simple trasformation won't work out for this by executing procedure at at the source.



    Mohammed U.

Share This Page