Issue with DTS and ActiveX task
This one is a mystery to me.
I have a DTS package which consists of one ActiveX task . This task just opens a connection, goes through a loop and in that loop a stored procedure is called and the result is streamed to a file which is saved to disk.
This all works fine and well. My problem is that this task currently takes 9minutes. However if I open up SQL Query Analyser and point to the database which the ActiveX task references this task takes 12.5 seconds.
What am I missing here. The funny thing is if I open up SQL Query analyser and point to the database the ActiveX task references (I don't run any queries whatsover) and then immediately CLOSE SQL query analyser and then run the package it still takes only 12.5 seconds.
If close SQL enterprise altogether, re-open it and then run the package (without opening SQL query analyser) the task takes 9 minutes. How can I get the task to take 12.5 seconds without having to open SQL query analyser.
Just a point: I have now created a win process task which loads up SQL Query analyser and points to the database and set the timeout on this task to 20seconds. This way I can simulate opening SQL query analyser and the ActiveX task is quick but it is by no means an elegant solution.
My first thought is caching, but it would be helpful if you posted the code your using in both the activex script and the sql script.
I'm guessing there's some kind of a timeout issue, where the program is waiting for something for 9 minutes, finally times out, then continues. I can't imagine what that would be, however. It might be worth looking into your settings to see if any of them are defaulting to (or set to) ~9 minutes.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL