Scheduled job failing 1

[Eyespi20]

I've created a DTS job to produce a comma delimited file and when I run it manually from Enterprise Manager, it runs just fine, but when I run it from the scheduled job, I get this error:

DTSRun:  Loading...      Error:  -2147221005 (800401F3); Provider Error:  0 (0)      Error string:  Invalid class string         Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts.hlp      Help context:  713.  Process Exit Code 1.  The step failed.

Can someone advise what I need to adjust?

Thanks!

Margaret

 

[mrdenny]

Are you doing anything funky with Active X classes that are registered on your workstation, but not on the server?

Try logging into the server with the same account that runs the SQL Server. Then run the DTS package and see what comes up.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Eyespi20]

No Active X on this DTS job. It's one that I had on another server and copied it over to this one. (by copy, I mean I duplicated each step that was in the other job).

It's a straightforward query that produces a comma delimited file.

I'll check log ins,though.

Thanks.

Margaret

 

[mrdenny]

Check the rights that the SQL Service has. It may not have rights to the folder and returned a funky error for some reason.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rstum2005]

Thats funny, Im actually having the same problem...

http://www.tek-tips.com/viewthread.cfm?qid=1158276&page=1

 

[Eyespi20]

For my job, I was already aware of the pitfall of using a mapped drive to designate where the file is created, so it already has the full path in it. \\ServerName\FolderName\.....

I was already running this same job off our reporting server, but due to some changes in business, we decided to run it from the production server in order to get up-to-the-minute data. The production server is a SQL 7.0 server where the reporting server is a SQL 2000 server. I don't know if that would have anything to do with it, but if so, still leaves me wondering why it will run from Enterprise Manager just fine but won't run from the scheduled job.

I did change the permissions on the folder that the job is to write to -- it was already set to everyone, but I added the production server specifically and the job still fails with the same error when run from the scheduled job.

I have another one that I created fresh that is doing the same thing and several other DTS jobs that run just fine. I've checked the ones that run fine against the ones that fail and just don't see any differences in set up at this point.

I'll just have to keep on digging.

Thanks everyone for your help.

Margaret

 

[mrdenny]

Did you save your SQL 2000 DTS package to the SQL 7 server? You'll need to recreate the DTS package using the SQL 7 Enterprise Manager. SQL 7 doesn't always read the SQL 2000 DTS packages correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Eyespi20]

No, I didn't save it to the SQL7 server, I esentially recreated the job on the SQL 7 server by copying the queries that made up the steps (there are only 3) and setting up my dynamic properties job which gives the file a unique name every time the job is run.

Margaret

 

[mrdenny]

But did you use the SQL 2000 Enterprise Manager or the SQL 7 Enterprise Manager to create the DTS package?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Eyespi20]

The SQL 7 Enterprise Manager.

M

 

[mrdenny]

OK, well that's just strange. What happens if you run it from the servers console logged in as the same account that SQL runs as?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Eyespi20]

Ok have the reason for the failure.

The server is a SQL 7.0 server and has a dynamic properties task on it.

Evidently, my version of SQL 7.0 data transformation does not support dynamic properties.

When, as Mr. Denny suggested, I went up to the server console and tried to go into the design mode, I got the same error (invalid class string).

So, to work around this, I did the first part of the job (gather the data into it's table), then went to my SQL 2000 server to do the dynamic properties task. The two servers are connected anyway, so this was my solution to my problem.

Thanks, Mr. Denny, for putting me on the path