0

I have to run a stored procedure on a daily basis at a particular time. I have struggled with Windows Service and after unsuccessful attempts I started looking into schedule a SQL job or schedule an event.

In order to schedule a SQL Server job I had to create one using the SQL Server Agent. I am working on Microsoft SQL Server 2008 R2 Express (with Advanced Services) on Windows and could not locate the SQL Server Agent even though I have admin access (logged in as windows authentication).

With the given scenario, what is the best way to schedule a stored procedure and how should I go about it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sarah
  • 1,895
  • 2
  • 21
  • 39
  • 3
    The **Express** edition doesn't have the SQL Server Agent - you'll need to find another way to run this (e.g. by using a command line app that can be scheduled using the Windows scheduler), or upgrade to another edition of SQL Server which supports SQL Server Agent. – marc_s Jun 25 '14 at 11:11
  • What is the context of trying to schedule the execution of the stored procedure? Is it related to a web application (ex: sending emails to new users)? ...an overnight batch process (ex: parsing data for regular reports)? A little more context *may* help others here guide you towards a solution that works for you. – László Koller Jul 01 '14 at 14:03
  • The stored procedure queries one table, groups data by date and write it to another table. The start and end date are provided to the stored procedure and the purpose is to run this procedure on a daily basis where the start date is the current date and the end date is "tomorrow's" date – Sarah Jul 02 '14 at 06:21

3 Answers3

0

There is a one way that you can schedule procedure using another SP. Create new SP like below:

CREATE SP1
AS
WHILE (1)
BEGIN
    WAITFOR TIME '22:20';   -- Your Time
    EXECUTE 'Your SP Name'
END;
GO

You can use error handling as per your requirement.

KrushnaKPawar
  • 51
  • 2
0

SQL server Agent contains thre back end tables to hold the front end data when you are creating a job

sysjobs
sysjobschedules
sysjobsteps

Enter job related information in sysjobs , give scheduling information in schedules table and give your sql code in steps like exec procname. Note that it should be updated in msdb database only.

Azar
  • 1,852
  • 15
  • 17
  • I do not have SQL Server Agent. Any pointers? I cannot update the version of SQL on the server. – Sarah Jun 25 '14 at 13:33
0

Since SQL Server Express does not include SQL Server Agent, you will not be able to schedule SQL jobs directly in SQL Server. However, that does not mean that you cannot schedule jobs to be run.

Use sqlcmd. It is a command-line tool. (Run sqlcmd /? in a command window and it will show you the exact command-line syntax.)

For example, you could use the built-in Windows Task Scheduler to schedule and run a specific command-line task using a trusted connection (the -E argument):

sqlcmd -S {Server}\{SqlExpressInstance} -E -d {Database} -Q "EXECUTE {StoredProcedure}"

..Or using specific login credentials and a password (the -U and -P arguments):

sqlcmd -U {LoginId} -P {Password} -S {Server}\{SqlExpressInstance} -d {Database} -Q "EXECUTE {StoredProcedure}"

For more information about using sqlcmd, read: Using the sqlcmd Utility (SQL Server Express)

For more information about using the Windows Task Scheduler, read: Task Scheduler

Good luck!

László Koller
  • 1,139
  • 6
  • 15