Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Problem with store Procedure

    Hi,

    I am trying to get this store procedure to run that a developer created.

    The job never ran, code is: EXEC proc_goodNight
    The error I get is stated below:

    SQL Server Scheduled Job 'eWFM GoodNight Process' (0xA0BEC5BE8C6B3A43BBE0590883BF6F01) - Status: Failed - Invoked on: 2004-08-06 11:19:09 - Message: The job failed. The Job was invoked by User MAMSI\lwilliams. The last step to run was step 1 (Update Agent Data).

    This messages doesn't tell me anything.

    Please help.

    Lystra

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a lot more about your system setup in general and the procedure in specific, all I can give is some very general observations.

    It appears that the stored procedure is launching a SQL job name 'eWFM GoodNight Process', and that job is failing when it tries to execute step 1. If you go into SQL Enterprise Mangler, open the server, then Management, then SQL Server Agent, then Jobs you should find a job with that name. Right click on the job and select the "View Job History..." menu item. On the dialog box that appears, check the "Show step details" checkbox, then look at the information displayed in the view. This should give you more "breadcrumbs" as to what went wrong, and possibly even why it went wrong.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    did yo utry and run the sproc by itself?

    It'll usually give you a better error message....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2004
    Posts
    191
    Problem solve.

    SQL server Agent Job fails weh the job uses a Linked server and the Job owner is not a System Administrator.

    So to work around, here is my solution I had three choices to choose from, which are listed below:

    1. Make the system administrator the owner of the job.

    2. Use mapped security context for the linked server and modify the job to run as OSQL.

    --Right-click the linked server, and then click 'Properties'
    --Click the 'Security' tab.
    --Select either of the following options
    ---'Be made using the login's current security context'
    ---'Be made using this security context'

    To modify the job to run as OSQL:
    --Right-click the job, and then click 'Properties'.
    --In the 'Steps' tab, click the step name that you want to edit, and then click 'edit'.
    --On the General tab of the Edit Job Step dialog box, click 'Operating System COmmand (CMDEXeC)' in the 'Type' list.
    --In the 'Command' text box, type osql -E -Q "Exec StoreProc".
    --In the 'Edit Job Step' dialog box, click 'OK'
    --In the 'Properties' dialog box, click 'OK'

    And it work, I was able to research to problem from previous threads.

    Thank All

    Lystra

  5. #5
    Join Date
    Jul 2004
    Posts
    191
    Problem solve.

    SQL server Agent Job fails weh the job uses a Linked server and the Job owner is not a System Administrator.

    So to work around, here is my solution I had 2 choices to choose from, which are listed below:

    1. Make the system administrator the owner of the job.

    2. Use mapped security context for the linked server and modify the job to run as OSQL.

    --Right-click the linked server, and then click 'Properties'
    --Click the 'Security' tab.
    --Select either of the following options
    ---'Be made using the login's current security context'
    ---'Be made using this security context'

    To modify the job to run as OSQL:
    --Right-click the job, and then click 'Properties'.
    --In the 'Steps' tab, click the step name that you want to edit, and then click 'edit'.
    --On the General tab of the Edit Job Step dialog box, click 'Operating System COmmand (CMDEXeC)' in the 'Type' list.
    --In the 'Command' text box, type osql -E -Q "Exec StoreProc".
    --In the 'Edit Job Step' dialog box, click 'OK'
    --In the 'Properties' dialog box, click 'OK'

    And it work, I was able to research to problem from previous threads.

    Thank All

    Lystra

  6. #6
    Join Date
    Jul 2004
    Posts
    191
    Sorry, had to re-post this reply, the first time I post it the site was experiencing trouble.

    Problem solve.

    SQL server Agent Job fails weh the job uses a Linked server and the Job owner is not a System Administrator.

    So to work around, here is my solution I had three choices to choose from, which are listed below:

    1. Make the system administrator the owner of the job.

    2. Use mapped security context for the linked server and modify the job to run as OSQL.

    --Right-click the linked server, and then click 'Properties'
    --Click the 'Security' tab.
    --Select either of the following options
    ---'Be made using the login's current security context'
    ---'Be made using this security context'

    To modify the job to run as OSQL:
    --Right-click the job, and then click 'Properties'.
    --In the 'Steps' tab, click the step name that you want to edit, and then click 'edit'.
    --On the General tab of the Edit Job Step dialog box, click 'Operating System COmmand (CMDEXeC)' in the 'Type' list.
    --In the 'Command' text box, type osql -E -Q "Exec StoreProc".
    --In the 'Edit Job Step' dialog box, click 'OK'
    --In the 'Properties' dialog box, click 'OK'

    And it work, I was able to research to problem from previous threads.

    Thank All

    Lystra

Posting Permissions

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