Fix - Cannot create COM Server to load and execute DTS Package

  • Rhonda Veit

    Mr or Mrs. 500

    Points: 591

    One of my production servers had showed all non-backup jobs had failed for that exact same reason.  They would run fine from the DTS section, but not as a scheduled job.

    The error was:

    “Executed as user: DOMAIN\Administrator. 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.”

    I researched on the internet and tried a couple things, but none of them helped.  (Re-installing MDAC, reviewing local security policies…)  But most of the info I found indicated it was a security issue and not a problem with the software.

    After much looking around, I stumbled upon the answer.  I went to the properties of the SQL Server Agent and reviewed the rights on it.  They were correctly set to DOMAIN\Administrator.  But on the last tab it listed a proxy user – and was using Windows authentication.  I switched it to SQL authentication and gave it the sa username and password.  The service restarted and lo-and-behold! the jobs started working.

  • GeBlack

    Ten Centuries

    Points: 1223

    I take it this is not a question, simply a public celebration

     

    Congratulations

  • Rhonda Veit

    Mr or Mrs. 500

    Points: 591

    Definitely a celebration! 

    Of course, I discovered more than one thing can cause that particular error.  But figured since I stumbled upon this solution, I better post it somewhere for others to find.

     

  • Steve Collins

    SSC Journeyman

    Points: 85

    What are the other fixes? I tried this one and it didn't work.

  • hamishahern

    SSC-Addicted

    Points: 498

    I too am receiving a similiar error on a couple of my dts schedules out of 1,000.

    When I restart the agent service the errors go away for a few days.

    Executed as user: TPOWER\TMERADSQL. 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.

    Something is leaking somewhere, I'm thinking maybe memory or com handles, I'm not too sure.

    Either way, I think its something that MS should have fixed, considering we're now on SP3a.

    I'm running Windows Server 2003, and this server only runs dts jobs.

    Is it possible that had you not changed the security for the agent and restarted the service it could have worked from restarting the service?

    I will try and change the agents login on mine and I'll let you know how I get on.

    Anybody else got any possible working solutions?

  • Peter Gadsby

    SSCrazy

    Points: 2658

    Hi Grasshopper, I have exactly the same problem (Windows 2003). for days no problem then I get this failure which stops all jobs from running, the only cure seems to be restarting the agent.

    Any help much appreciated

    Peter G

    Peter.Gadsby@ntl.com

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • hamishahern

    SSC-Addicted

    Points: 498

    Its definately memory related, and the fact that restarting the Agent suggests that its the agent that is leaking the memory.

    If anybody can reproduce it easily, perhaps we can send something to Microsoft to work on.

  • linabran

    Valued Member

    Points: 67

    Muchas gracias, he buscado esta solucion hace mucho, acabo de realizar todo el proceso, espero que funcione bien, lastimosamente no puedo correr el proceso ya, pero mañana les confirmo.

    Saludos.

    Lina M.:D

  • marina-342327

    SSC Journeyman

    Points: 75

    Thanks for explanation!

    Marina

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

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