locked
Could not find stored procedure RRS feed

  • Question

  • Good Morning,

    I have a scheduled job, which has 5 steps for 5 cities.Recently we added a new city and The command is

    EXECUTE dbo.usp_DailyProcessing @cityAbbrev = 'LA'; 

    The scheduled job has 5 steps , one for  each city. When executed from the sql server agent only the last step for the new city fails, with the following error

    Executed as user: cecon\svc_SQLServerAgent. Could not find stored procedure 'dbo.usp_DailyProcessing'. [SQLSTATE 42000] (Error 2812).  The step failed.

    But when i execute from SSMS it updates the records. Please suggest an idea.

    Thanks


    SV

    Tuesday, October 10, 2017 3:24 PM

Answers

  • Make sure the database context of the newly added T-SQL step has the correct database specified.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 3:28 PM
  • Can you script out your job to check the difference of each step in your job?
    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:12 PM
    Tuesday, October 10, 2017 3:26 PM
  • > Default database chosen could different from the database containing the procedure

    Use fully qualified name and see.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:16 PM
  • Checked and i have the 

    USE [MAINDB]

    GO 

    before the Execute

    Thanks


    SV

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:23 PM
  • I would guess the sp name is mispelled, if you're 100% certain that the jobstep is in the correct database context before executing sp.

    Even an extra non-printing character (like extra space) in the execution string could cause a difference from the real sp name that will cause it to error. Try copy-pasting the execute statement again from one of the other jobsteps and see if that resolves.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, October 10, 2017 5:37 PM
    • Proposed as answer by Xi Jin Wednesday, October 11, 2017 1:36 AM
    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:58 PM
  • SV,

    Please mark answers that help you, to close this thread.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 6:24 PM

All replies

  • Can you script out your job to check the difference of each step in your job?
    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:12 PM
    Tuesday, October 10, 2017 3:26 PM
  • Make sure the database context of the newly added T-SQL step has the correct database specified.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 3:28 PM
  • > Default database chosen could different from the database containing the procedure

    Use fully qualified name and see.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:16 PM
  • Thanks Li

    I did and they are the same no differences i forgot to mention that before the execute statement all steps have

    USE [MAINDB]

    GO


    SV

    Tuesday, October 10, 2017 4:23 PM
  • Checked and i have the 

    USE [MAINDB]

    GO 

    before the Execute

    Thanks


    SV

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:23 PM
  • I would guess the sp name is mispelled, if you're 100% certain that the jobstep is in the correct database context before executing sp.

    Even an extra non-printing character (like extra space) in the execution string could cause a difference from the real sp name that will cause it to error. Try copy-pasting the execute statement again from one of the other jobsteps and see if that resolves.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, October 10, 2017 5:37 PM
    • Proposed as answer by Xi Jin Wednesday, October 11, 2017 1:36 AM
    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 4:58 PM
  • Thanks Sarat

    SV

    Tuesday, October 10, 2017 5:24 PM
  • Thanks Philfactor and everyone

    Dumbest thing was never to copy and paste it there was a trailing space. Dumber 


    SV

    Tuesday, October 10, 2017 5:24 PM
  • SV,

    Please mark answers that help you, to close this thread.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by saivenkat77 Wednesday, October 11, 2017 5:11 PM
    Tuesday, October 10, 2017 6:24 PM
  • Hi SV,

    Did you resolve your issue? If so, please kindly mark the corresponding reply. It will help others with the relevant issue find the answer more easily.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 1:36 AM