0

So...Im running Microsoft SQL server management studio...ive created a stored procedure [ResetOrganisationPaymentPackage] which resets a users account. Tried and tested works fine. Now I would like to set this stored procedure to run as a schedule task.

I've read the article on https://blogs.msdn.microsoft.com/sqlagent/2010/10/14/creating-a-regularly-scheduled-job/ explaining how to setup a schedule task to a job, which is all good. So i've created a new job....my Q being how do I assign the job to run this stored procedure? I dont see any options to do so

Paolo
  • 2,224
  • 1
  • 15
  • 19
John
  • 3,965
  • 21
  • 77
  • 163
  • Create a Job - name it - go to Steps - add new step - enter step name - chose database - in command field type `EXEC dbo.MySP` – gofr1 Apr 18 '16 at 11:50
  • thanks for the reply I see now. However when I tried this, the schedule didnt work so I ran it manually and upon inspection I get the error 'An exception occued while executing Transact-SQL statement or batch, SQL ServerAgent is not currently running so it cannot be notified of this action(microsoft SQL server error 22022) – John Apr 18 '16 at 12:17
  • Please, try steps from https://support.microsoft.com/en-us/kb/911841 the part with **CAUSE** title – gofr1 Apr 18 '16 at 12:21
  • yea thanks that resolved that issue, now when I run it im getting another error. Iv checked the details of it in view history and it is telling me could not find stored procedure dbo.reset iv tried changing it in the steps to [dbo].[reset] but this didnt work either. the SP is definitely there, why cant the server agent see it? do I need to specify in the command field what db to check? any ideas? – John Apr 18 '16 at 12:30
  • Of course you need to specify database. For sure you can use `EXEC MyDatabase.dbo.reset` or `USE MyDataBAse GO EXEC dbo.reset GO` – gofr1 Apr 18 '16 at 13:03
  • happy days that worked...thanks for all the help...if you want to stick down the answer il mark it...and if not thanks! – John Apr 18 '16 at 13:07
  • I have one more Q...if I wanted to populate a parameter @PlanId in the stored procedure...how would I do it...from the command? something like EXEC BusNames.dbo.Reset(@Planid = 3) ? – John Apr 18 '16 at 13:23
  • `EXEC BusNames.dbo.Reset @Planid = 3` or `EXEC BusNames.dbo.Reset 3` – gofr1 Apr 18 '16 at 13:37
  • again, thanks for all the help G – John Apr 18 '16 at 13:53

0 Answers0