I have a DTS job that accesses a registered dll via the following code
method in an ActiveX script task: set AxCDownloadManager1 =
CreateObject("DSTHTTPDL.DownloadManager.1")

It works great in development (SQL Server 2000 on Windows 2000). I
have scheduled the DTS to run in a job. Both the DTS package and the
job execute fine.

In production (SQL Server 2000 on Windows 2000), the DTS package runs
but when I try to execute the job, it comes right back with a success
status when it should take minutes to run. The first thing my code
does is create the object above. I know it's not getting past this
because I can check for return values, and I'm just not getting
anything back. For some reason, I believe it's not getting a handle to
it. I'm thinking it could be a user rights issue, but are not for
certain. The job runs under 'sa' account. It is setup identical to
how we are running it in development. We have checked the registry on
both boxes to ensure the appropriate class id's, program id's, etc are
in place.

Any thoughts, suggestions would be greatly appreciated!!

Patrick

I would log onto the server itself, under the same account as used by
the SQL Server agent service and manually run the package and see if you
get any more error info from the execution dialog. Also turn on the
package log file and data pump log fle, and also the log to SQL server
option.


--
Darren Green
http://www.sqldts.com

"PD" <pdaniel (AT) commonwealth (DOT) com> wrote

Thanks for the info! I set the logging on and unfortunately since it
returns a 'success' status I'm not getting any additional info in the
log files.

The owner of the job is 'sa'. Is this the id I should be logging onto
the actual server with to test it out? This is a SQL Server id which is
not registered on the Windows side. Should I be using another id? How
can I find the appropriate SQL Server agent service id?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You need to physically connect and logon to the server itself, using the
service account of the SQL Server Agent service. This means that you are on
the server under the same security context as used by the service, so both
location and context match for the test.


--
Darren Green
http://www.sqldts.com


"P Daniel" <pdaniel (AT) commonwealth (DOT) com> wrote

We tried it with no luck. The job still does not execute completely. It
returns back 'success' so there is nothing to look for in the log files.
We're going to look into adding more checks and balances in the code to
see if we can get anything back from the CreateObject line. We have
tried adjusting from user to server authentication and vice versa. We
have tried assigning various owners to the job. What I don't get is
that it works great on our development server but not on the production
server. I'll keep you posted if I find anything. Thanks again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How about taking DTS out of the equation. Get logged onto the server with
the correct security, then just run a standalone vbs file to create and test
the object.


--
Darren Green
http://www.sqldts.com

"P Daniel" <pdaniel (AT) commonwealth (DOT) com> wrote

I believe I performed what you are suggesting. I copied the actual
"command" line text (i.e DTSRun
/~Z0x7A6A5263B86BA964897F5789914295E89BAA7BF2BFE5A1 07E0220536E1499ED3F58
5FBCFE9B42FABB877A6BE0B2619FE5246C694FE8FBD8B7A456 5CB27CBDB10E77A05E3A58
15C2716C4ED86FB8202EEE99CD944781E298F6 ) that is generated in the job
and executed it via a command prompt on the actual server and it worked
great. This was outside the SQL Server Agent.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This wasn't what I meant exactly, but you're right it does test the
scenario. So essentially the problem is with the DLL, but only when
scheduled. Using the same DLL on the same machine with the same NT account
as SQL Server Agent works fine. So it must be something to do with the
environment that is different. Only thing I can think of is that anything
run under the service must have no UI stuff at all, otherwise it chokes. May
also be something like environment variables or registry ACLs. Apart from
that I don't know what to suggest next apart from a call to PSS.

Darren

"P Daniel" <pdaniel (AT) commonwealth (DOT) com> wrote

Ok, we pretty much exausted all avenues at this point. We decided to
try to run the job on another production box and it ran great. So it
looks like we'll have to leave these questions open ended for now. I
still am very curious as to why it does not work on one but it runs on
the others. Unfortunately, I have time constraints so I won't be able
to look into more right now. Darren, I do appreciate all your feedback
as it gave me other options to consider in debugging this. A sincere
thanks!

PD


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!