ActiveX Script Task Timeout Expired Error?

Question

ActiveX Script Task Timeout Expired Error?

SinLam

Right there with Babe

Points: 744
More actions
January 22, 2004 at 11:24 am

#59931

I have a DTS that execute an ActiveX Script Task. In that task, it execute a stored procedure that would take at least > 1 min. to run. It currently giving me a timeout error every time I run the task. How can I fix this problem?
Error say:
"Error Source = MS OLD DB provider for SQL Server"
Error Description: Timeout expired"
Line 38"
That is the line where it do " db.execute(mySQLString)"
Please help... thanks.
Sin

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jon Russell Hall of Fame Points: 3048 More actions · Answer 1

SinLam,

The first thing that popped into my head was that you have a run-away loop or an inefficent query. How is the performance of the stored proc in Query Analyzer?

JR

SinLam Right there with Babe Points: 744 More actions · Answer 2

I don't think this is a run away loop because I had it working once before and hasn't been changed since then.

The stored procedure however has been updated to be more complex now, and it does take a long time to run even in SQL Analyser. It has transcation and lots of conditions, and also call out to other stored procedures to get data, etc. Basically it is a long complex process. So it now takes about 1:33 miniutes to run in SQL Analyser.

Thanks.

Sin

Jon Russell Hall of Fame Points: 3048 More actions · Answer 3

I will assume you checked out the Execution Plan to look for performance issues. Well, since you do get data in Query Analyzer, we know the stored procedure is works.

You mentioned that you are calling out to other procedures. I would run through all the procedures involved and double check the security permissions, keeping in mind of the security context you are in and which machine you logged into. Also, run through all the steps of the package and double check the persmissions and security context.

You can also try to see if you can execute each step, one at a time. See if that reveals anything.

Is the SQL Server on Windows 2000? If you're on NT4, is your package trying to execute some type of function on another server? If there are any NT4 machines involved this may pose a problem.

This should have been the first question. Can you find any information in Event Viewer and SQL Server Logs? Make sure you have Logging turned on in the Package Properties. Also, in the same dialog, set an error file to see if that reveals anything.

SinLam Right there with Babe Points: 744 More actions · Answer 4

1) Good point, I have not checked the Execution Plan. I should try to improve its performance anyways. How would I do that?

2) I am running SQL Server 2000 on Windows 2000. There is only one step in the DTS. Like I said before this DTS works before when the stored procedure was able to finish in less then a min in the past. So I don't think there is a permission issue here. Here is the error log from DTS log, nothing found in SQLServer log and event viewer:

Step Error Source: Microsoft Data Transformation Services (DTS) Package

Step Error Description:Error Code: 0

Error Source= Microsoft OLE DB Provider for SQL Server

Error Description: Timeout expired

Error on Line 36

(Microsoft OLE DB Provider for SQL Server (80040e31): Timeout expired)

Step Error code: 800403FE

Step Error Help File:sqldts80.hlp

Step Error Help Context ID:1100

3) I am wondering if there is a limitation on ADO object in VB Script using in DTS like this.?????? Don't you think that it should allow a long running query to execute over that connection? But I even try to set the ADO connection object's timeout property it didn't do anything. (db.ConnectionTimeout = 300)

HELP!!!!

Sin

Jon Russell Hall of Fame Points: 3048 More actions · Answer 5

1.) In Query Analyzer, select "Display Estimated Execution Plan" from the Query menu. Take a look at these articles to help:

http://www.sqlservercentral.com/columnists/RDyess/displayingexecutionplans_printversion.asp

http://www.sqlservercentral.com/columnists/rmarda/performanceaddinghints_printversion.asp

2.) Yeah, it doesn't look like there is a permission issue with the package, but I will still double check what your store procedures are doing an see if there is any permissions or access issue there. I could not find and exact error message on microsoft.com, but there were a couple of similar articles that suggested you get the latest service packs for SQL Server. You should not that anyway if you have not.

3.) If you have a Transform Data Task in your package that is using an ActiveX script then it might be possible you have some bad logic in that script. But wait, didn't you say you only have on step or task in you package? If so, then that means you are just executing the stored procedure in that step. Or, do you have another task that uses the Transform Data Task?

JR

SinLam Right there with Babe Points: 744 More actions · Answer 6

Reply to #2) I will check tomorrow.

Reply to #3) I don't have a Transform Data Task, all I have is one Task called ActiveX Script Task in that DTS package. My script is like this:

Function Main()

dim myID, myPID, myDiv

Set db = CreateObject("ADODB.Connection")

db.ConnectionTimeout = 300

db.open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=test;pwd=test;Initial Catalog=Test_dev;Data Source=test"

sDiv = DTSGlobalVariables( "division" ).Value

sUserID = DTSGlobalVariables( "userid" ).Value

dDate = Now()

' ***** Do the re-run ones first. ******

SQL = " WR_Batch_GetNeedRerunBatchByDiv '" & sDiv & "'"

Set rs = db.Execute(SQL)

i = 0

Do While Not rs.EOF

i = i + 1 'counter

InsertSQL= " WR_RR_CreateReconReportMain '" & sDiv & "', " & rs("requestID") & ", '" & sUserID & "'" & ", NULL, NULL"

msgbox i & ", " & InsertSQL

db.Execute (InsertSQL)

rs.MoveNext

Loop 'loop each pending Recon Report Record

msgbox "Done re-run"

.....

thanks.

SL

philcart SSC-Forever Points: 47794 More actions · Answer 7

If all you have is this one task, why are you using DTS?

How about breaking up the stored procedure execution into a few steps. Then use workflow and looping to perform the job.

You can return a resultset in an ExecuteSQL task and loop through it. See the following,

http://www.sqldts.com/default.aspx?298

--------------------
Colt 45 - the original point and click interface

cneuhold Hall of Fame Points: 3375 More actions · Answer 8

hi!

you'll have to set the CommandTimeout property of your connection. ConnectionTimeout property just relates to the actual build-up of a database connection, whereas CommandTimeout sets the time-out for each command executed on that connection.

best regards, chris.

SinLam Right there with Babe Points: 744 More actions · Answer 9

Thanks for everyone's friendly input. I learnt many different things.

The CommandTimeout Property did it. It works now. THANKS.

Sin

wsteele SSC Rookie Points: 37 More actions · Answer 10

wsteele

SSC Rookie

Points: 37

November 11, 2008 at 5:39 pm

#897237

GOOD STUFF! Thank you!