SP won't execute when called from a job

Question

SP won't execute when called from a job

Justin-14543

SSCrazy

Points: 2017
More actions
August 9, 2009 at 4:36 am

#134579

I have a SP which works great when run independently from Mgmt Studio. The same SP, when called from a job, it does not get executed. The job succeeds as if everything went fine.
It is a simple SP that runs a query and inserts results into a table. It then sends out an email based on if the result satisfies a condition. This SP runs great when executed from Management studio but when called from a job, the job succeeds but nothing happens. It is as if the SP was not executed. Please help.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

It would probably help a great deal if you could script out the procedure and the job from Management Studio and attach them to a post. Feel free to edit out any sensitive information, it's just that it will be difficult to do much except guess otherwise.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Annadurai.S SSC Enthusiast Points: 180 More actions · Answer 2

It may be a rights issue, which id you have used to open the mgmt studio and the job runs in which account check it or post your query

Regards

Anna

Justin-14543 SSCrazy Points: 2017 More actions · Answer 3

Annadurai - I believe you are right. Although i am using Sysadmin account to login/create job etc.. So, that is what is confusing me.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Hello Justin, i had the same issue before...after spending hours troubleshooting i found for few procedures when it runs through mgmt studio works fine but throug a job fails because some special characters get converted while running through the job...i used profiler trace to look exactly wats exactly running inside the procedure and when i copy and pasted in the mgmt studio it failed...some characters get converted while using through job...you might want to try using trace..thanks,

david.tyler SSCrazy Points: 2206 More actions · Answer 5

Double check the account used for SQL Agent has rights to execute the procedure. When you run the procedure from SSMS either your AD account or the SQL account is used (depending how you log into SSMS) where jobs rely on the SQL Agent account.