Timeout Expired

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 />

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.

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

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.