I am using a stored procedure to delete some tables that have huge data in them. This stored procedure when run on a query analyzer runs fine but when made to run using a activex script (which i need to use inside a dts package) gives a time out error. here is the active x
Const adOpenKeyset = 1
dim myConn, gFilePath, Fso, deleteSQL
gFilePath = DTSGlobalVariables("gPathFol").Value
Set Fso = CreateObject("Scripting.FileSystemObject")
Main = DTSTaskExecResult_Success
If (Fso.FileExists(gFilePath & "\CoreLoadEnd.trg")) Then
set myConn = CreateObject("ADODB.Connection")
myConn.Open = "Provider=SQLOLEDB.1;Data Source='server_name'; Initial Catalog=DBNAME; user id = 'username' ; password = 'password'"
deleteSQL = "exec USP_DeleteTables"
The default value for CommandTimeout property of the connection object is 30 seconds. I presume the procedure runs a little longer, so you need to change this value appropriately befor doing Open on the connection.