I am having trouble executing a DTS package from a SQL Job. It works OK for a
while but then starts failing with the following error message:

Executed as user: domain\SQLSvcAccount. DTSRun: Cannot create COM Server to
load and execute DTS Package. Error -2147221008 (800401F0): CoInitialize
has not been called. Process Exit Code -2147221008. The step failed.

To correct this I need to restart the SQL services.

I can run the DTS Package directly using Enterprise Manager from my PC and I
can also using the DTSRUN.exe command to execute the package from my PC using
the exact command line that the SQL job is using. This works OK, even when
the jobs are failing.

Can anyway shed any light on this and come up with a solution?

In brief, my setup is SQL 2000 with SP3 running on Windows Server 2003.

Thanks
Matt

I have a similar problem, only difference is my ActiveX Script says It cannot
create object. If you find a solution, Please do let me know.

Thanks and Regards,
Karunakaran

Are you not experiencing this

http://support.microsoft.com/?kbid=269074






"Matt" <mattk (AT) news (DOT) postalias> wrote

No Allan, I dont get that message, I get error stating ActiveX Cant Create
Object.

"Allan Mitchell" wrote:

OK When it is scheduled it will run on the server.

If you go to the server and try to run the package interactively what
happens?

What is in the package? Could you be missing a dll on the server that
is on your PC?




"Karunakaran" <Karunakaran (AT) discussions (DOT) microsoft.com> wrote

I don't think it is related to this article.

I have tried running the DTS package on the server whilst logged on as the
SQL Agent account and it also works when run from the command line with
DTSRUN.EXE or through the SQL Server Enterprise Manager.

It still fails when executed from a Job.

I've also noticed that another job which just runs an ActiveX Script is
failing with the following message:

Executed as user: domain\username. Error Code: 0 Error Source= Microsoft
VBScript runtime error Error Description: Out of memory Error on Line 2.
The step failed.

The server is certainly not out of memory by far.


"Allan Mitchell" wrote:

Hello,

Based on my research, there are several possible causes of this issue.
Please follow these steps to see if the issue can be resolved:

Action Plan 1 - Modify the path of Dtsrun.exe

1. Expand the server that is running SQL Server in SQL Server
Enterprise Manager.

2. Expand Management, expand SQL Server Agent, and then expand
Jobs.

3. Right-click the problematic Job, and then click Properties.

4. Click the Steps tab, and then click Edit.

5. Modify the run line to include the full path to DTSRun.exe. If
the path contains a space, you have to use quotes (" ") for the
path. For example:

"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe"
/~E080989ED67CC54E9931C07DC3282FDA

Please search for the Dtsrun.exe file on your computer to
determine the correct path.

6. Run the job manually by right-clicking the job and then
clicking Start Job. Does it still fail?


Action Plan 2 - Run the DTS Package on the server rather than on
a client workstation.

Is the DTS Package run from a client workstation or directly on
the Server? When the package is run as a scheduled job, the
package is always run on the server. When you run the package
from the DTS Designer in SQL Enterprise Manager (SEM), the
package is running on the computer where you are seated.

To isolate this issue, we need to have the package run on the
server. Please logon the server from console as the SQL Server
service startup account and run the DTS package manually, thus
the package will be running on the server. Does it fail at this
point?


Action Plan 3 - Have a member of sysadmin own the job.

The security context in which the job is run is determined by the
owner of the job. To isolate any permission issues, please make
the necessary changes so that:

1. The SQL Server service startup account and the SQL Server
Agent service startup account are the same Windows account which
has local administrative privileges.

2. Logon the SQL Server box as the account mentioned above.

3. Create a scheduled job for the DTS package. Verify that the
owner of the job is the account mentioned above.

4. Run the package manually and run the job manually, what is the
result?


Action Plan 4 - Place all the data resources on the SQL Server
box.

If the database resides on another computer, please create
a new database and a new table on the SQL Server box,
create a new DTS package to import data from the newly created
table to a SQL table, run the package manually and then run it as
a job, what is the result?


Action Plan 5 - Apply the latest service pack of SQL Server 2000.

Please refer to this article to do this:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
http://support.microsoft.com/?id=290211


For additional information, please read the following articles:

318819 PRB: A DTS Package Raises Exceptions or Stops Responding
When You Run It
http://support.microsoft.com/?id=318819

290077 FIX: DTS Scheduled Job Does Not Complete After First Run
http://support.microsoft.com/?id=290077

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Sorry about the delay in response but I've only jsut been able to try these
out. The results of the tests are as follows:

Action Plan 1: Yes, it still fails.

Action Plan 2: It runs fine on the server when run from the DTS Designer.

Action Plan 3: It fails. It is already configured as required.

Action PLan 4: Database already on the server.

Action Plan 5: Already on SP3.


Thanks
Matt

"Sophie Guo [MSFT]" wrote:

Hello,

The latest SQL server service pack (SP) is SQL server SP4. I suggest that
you perform the following steps to narrow down the problem:

Step 1: Install SQL server SP4, install the latest MDAC:

Microsoft Data Access Components (MDAC) Installation

<http://msdn.microsoft.com/library/de...-us/dnmdac/htm
l/data_mdacinstall.asp>

Step 2: Make sure SQL server agent works fine.

Please create a new job and check if the job works fine. Please check if
the issue only occurs on one job.

Step 3: Log on to the server locally, schedule the package as a job and
then run the job manually.

Let me know if the job works when running manually. If the job fails,
perform the following steps:

1. Right-click the problematic Job, click Properties.

2. Click the Steps tab, and then click Edit.

3. Copy the code in command box and run the code in Query Analyzer (QA).
Let me know if the code can run fine in QA.

Step 4: Make sure SQL Server and Agent startup account are the same user.
To make sure the issue isn't account related, log on as a new created
administrative user and create a new DTS package to test.

Step 5: Create a new test database on another known working machine which
has SQL server installed. Check if you can reproduce the issue on another
machine.

Step 6: Please check the job history, NT event logs, SQL error logs and DTS
logs to see if there is anything relates to this issue.

Step 7: Use FileMon log and RegMon log

You can use FileMon and RegMon to check if there is a file or registry
problem. They can be downloaded from www.sysinternals.com:

FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml

RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml

Please perform the following steps:

a. Start FileMon to monitor the file system events.
b. Reproduce the issue.
c. Stop FileMon and save the log to a file named filemon.log.
d. Start RegMon to monitor the registry events.
e. Reproduce the issue.
f. Stop RegMon and save the log to a file named regmon.log.

I hope the information is helpful.


Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi,

Thanks for your help.

We have actually now managed to identify the problem and resolved it.

There were several jobs running ActiveX Script which were using the Run
method of the Wscript.Shell object to load a web page. (i.e. WshShell.Run
("http://xxxx") ).

This used to work on the old NT4/SQL 7 server but now appears to be leaving
IE running after it completes. After a while there are a number of copies of
IE running which then causes the error we were having. I imagine that this is
due to the enhanced security in IE6.

I have sent an example of the same script usnig WinHTTP to the originator of
the jobs and now that they have been changed, it appears to be behaving much
better.

Thanks
Matt

"Sophie Guo [MSFT]" wrote: