Results 1 to 12 of 12
  1. #1
    Join Date
    May 2002
    Posts
    46

    Unanswered: SQL Agent Job fails-"Can't Create Object"

    I have read many threads on this issue & tried multiple solutions to no avail.

    Here's my environment: SQL 2000, Logged directly into server via VPN (so it should be like I am physically at the server).

    I've set up a DTS Package that executes an Access procedure. The package runs fine when I execute manually. When I execute via schedule, it fails on the error "ActiveX component can't create object: 'Access.Application'. The actual statement is:

    Set objDB = CreateObject("Access.Application")

    I know that Scheduled jobs default back to the SQL Agent permissions. I have reset SQL Agent to use my Windows NT account login so it should have the exact same permissions, etc. that I have when I execute manually?

    Any help will be GREATLY appreciated!!!

    Thanks mucho,
    Brian

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Do you have a reference to the Active X libraty in your Access project?
    Cathy

  3. #3
    Join Date
    May 2002
    Posts
    46
    I do - but I don't believe the VBScript is even getting that far. It's like the permissions for the SQL Agent are different and it can't perform the CreateObject - yet I've set the SQL Agent Logon the same as my Windows Account that I use when I successfully execute the package manually.

    Thanks for the reply!
    Brian

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Who is listed as the owner of the job in the job properties? Also, why don't you create a log file in Access to log the progress being made so you can know exactly where it's breaking. It sounds like you don't know for sure.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    May 2002
    Posts
    46
    The owner in the job properties is set to my Windows domain account that I have assigned as the logon account for SQL Agent.

    The job is failing before it even opens an instance of Access - not sure if a log file would help there.

    Thanks for the reply
    Brian

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    When you run a DTS package from the designer, it uses all the components loaded on your computer. When you schedule it to run as a job, it uses the components stored on the server. If you don't have Access, or the necessary components related to Access, loaded on the SQL Server, the job will not be able to intantiate the object.
    That which does not kill me postpones the inevitable.

  7. #7
    Join Date
    May 2002
    Posts
    46
    I'm actually logged into the SQL Server machine via the VPN connection. MS Access is installed on the server. It executes fine manually (which in this case with the VPN connection and remote terminal connection) is the same as sitting at the machine.

    With the SQLAgent Logon being the same as my Windows NT account, I don't understand why/how any permissions can be different that would prevent an instance of Access to be opened within the VBScript when it's scheduled as opposed to when I execute it manually?

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Have you tried scheduling it and letting it run as scheduled "while" you are logged into the server itself? If not, try that. I have a sneaking suspicion I want to see if it's true. (grin)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    May 2002
    Posts
    46
    Derrick - actually every time I've tested/run the scheduled package, I've been logged in. Are you thinking that may be an issue? I'm open to sneaking suspicions at this point!

    Thanks

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Then just shove my sneaking suspicion out the window. Have you had the chance to login actually sitting at the machine? When you have tested it have you let it actually just run because it hit the scheduled time, or have you hit run each time to run it?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    May 2002
    Posts
    46
    I've tried scheduling it a couple of minutes ahead of time & running it on the spot - both to no avail.

    No - I haven't physically gone to the server to do it. That may be the next step.

  12. #12
    Join Date
    May 2002
    Posts
    46
    Just to see if I could use the CreateObject on anything, I replaced:
    Set objDB = CreateObject("Access.Application") with
    Set objDB = CreateObject("Excel.Application")
    and removed the other lines to see if it would work and it did.

    There's something specific about Access that's not allowing the job to create an instance of it.

    Any ideas???

    Thanks while continuing to bang head,
    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •