Networks

Ask something
Discussion Locked

DTS ActiveX calling Excel Macro

0 helpful
2 replies

by paulkayengheng

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a DTS package ,which used ActiveX to call Excel application macro to update a few worksheets.

Each time it execute in the SQL Server Agent, it seem to go on forever. Usually takes around 5 to 7 mins. But, if I execute manually sometime it completed successfully and recently giving me a message like 'Switch' or 'Retry' because process busy.

How can I debug such and error?

2 replies

by sameer.premji

First things first...

1. Make sure you have applied latest OS patches on the server (after you have tested it elsewhere).

2. Make sure you have applied latest SQL Server patches (upto SP4).

3. After you have done the above steps, open your package and right-click on the task icon that contains the ActiveX script. Select Workflow, then Workflow Properties, click on Options tab, make sure that 'Execute on main package thread' is checked and all the rest of the items are UNchecked.

4. If that doesn't help, it could be a slower network at peak times or simply your server needs fine tuning to serve I don't know how many users.

I would talk to the Network Admin as well as check every aspect of the database - memory, backup strategy in place, scheduled jobs that might be in conflict, etc.

Good Luck,

Sam.

This discussion is closed to new replies.