Post reply

DTS and Agent issue

  • June 4, 2004 at 7:31 am

    #161597

    A colleague has developed an application which uses a DTS package to load several tables.  The DTS package uses several ActiveX tasks to retrieve the data.  It works great when executed directly (from the server), but when executed through a SQL Agent job (whether directly or scheduled), it fails.  The id it runs under is a local administrator on the box, and system administrator in SQL.  The only thing I can come up with is that because the job executes DTS from the DTSRUN utility, that perhaps there is an issue there ("DOS" vs Windows)?  Has anyone else faced this issue?  Any suggestions?

    Steve

  • June 4, 2004 at 8:03 am

    #509017

    I've had the same problem.  Try running the job with the SA user account.  For some reason, the jobs will not run unless they use this account.  I'm not sure why, I assume it's a Microsoft bug.

    Dana

  • June 4, 2004 at 8:18 am

    #509022

    Dana,

    Thanks for the quick response!

    I tried changing the job owner to sa, still failed.  It should be noted that the DTS package is running under the agent startup account regardless of the job owner.  This account is a domain account, local administrator, and has sa level rights.

    Steve

  • June 4, 2004 at 8:23 am

    #509024

    Hey, sorry.  I remember having the same issue and the only way we could resolve it was by changing the owner of the job to SA.

    I was just reading an article on microsoft's website.  Maybe it will help (or you may have already read it)

    http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

    If I think of anything else I'll let you know.

    Dana

     

  • June 4, 2004 at 8:50 am

    #509030

    Thanks, Dana!

    I hadn't seen that before.  I haven't read all the way through it yet, but it looks like a great "checklist" for diagnosing a DTS job.

    Steve

  • June 7, 2004 at 2:11 am

    #509181

    log on to the server using your sqlagent account and then try to run the package from EM\edit dts package.

    You'll get more detailed errorinfo.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • June 7, 2004 at 1:55 pm

    #509317

    alzdba,

    Thanks, the problem is that it runs perfectly from the package itself.  It even runs if I copy the dtsrun statement from the job and shell out to a command prompt and execute it there, and from the agent account.  I've exhausted every possibility that I can think of and some that I couldn't think of, but others did.  There is a thread on Microsoft's forums that addresses the same error message, but from a different application.  Microsoft is looking at it.  I think it may be a bug (activex related).

    Thanks,

    Steve

  • June 7, 2004 at 4:02 pm

    #509347

    You say that the DTS is running from an application. If it is a VB app maybe this may help. It is Microsoft Knowledge Base Article 323685.

    http://search.microsoft.com/search/results.aspx?st=b&na=88&View=en-us&qu=323685

    Happy hunting

  • June 7, 2004 at 4:10 pm

    #509349

    Phoenix,

    Thanks.  No, its just scheduled through SQL Agent, but it contains activex scripts.  Today, another colleague suggested trying to run it through a command exec batch file.  Once again, if you execute the batch file from the command prompt, it works fine.  But, if it executes from SQL Agent (scheduled) it fails.  I'm really starting to think that activex and SQL agent just aren't playing nice.

    Steve

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply