Need to schedule a stored procedure on daily basis using T-sql statement

Posted on 2014-04-22
Medium Priority
Last Modified: 2016-02-10
Hi I am trying to schedule my store procedure which does the backup of a table, but when I am trying to use the Sql server agent, it is disabled in my SSMS. so, I need to write the T-sql statement to schedule the job for thisstore procedure which can run every day morning 9 AM. Can anyone help in writing the T-sql query for this?

My procedure is:

Create proc BackupFTP
            Drop table dbo.FTPBackup
            Select * into FTPBackup from FTP
Question by:Aparanjith
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 40015533
I think you need the SQL server agent running to get any job (SSIS/T-SQL/SSRS, etc) to run on a schedule.

Author Comment

ID: 40015846
I heard with my manager, that we can automate the schedule of jobs for SP by the T-sql statements. Do not know how to do that?

Accepted Solution

Dulton earned 750 total points
ID: 40015873
Oh, you can certainly create jobs and load them into the schedule with scripts.

Here's an example: http://sqlfool.com/2012/09/sql-agent-job-script-2/

But without the agent running, I don't believe the job will ever fire.... no matter if they were created with t-sql or with the GUI.
 See item 3 on this page, as indicated by Microsoft. The agent runs the jobs which are scheduled.

-That was kind of my point, since the Agent isn't running, your jobs aren't going to ever fire anyways.  Now if it's a training exercise or something, that's one thing, but I just thought you ought to know that they won't fire before you go through the pain of scripting those jobs.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 750 total points
ID: 40016108
Are you by chance running SQL Express? If so there is no SQL Agent in that product.

if this is the case you could use the windows scheduled task and utilize sqlcmd to fire your stored proc

regards Marten

Author Comment

ID: 40016538
No i am using SSMS 2008 R2, but i do not have the access to SQL server Agent to schedule the jobs.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40016540
No i am using SSMS 2008 R2
But that was not the question.  Please post the results of this:

Author Comment

ID: 40018569
Enterprise Edition (64-bit)

When query for version(SELECT @@VERSION) it gave me this,

Microsoft SQL Server 2008 (SP3) - 10.0.5826.0 (X64)   Sep 18 2012 03:07:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40019079
Then the only reason I can think that you do not have access to SQL Server Agent is that you do not have the appropriate permissions.
LVL 20

Expert Comment

by:Marten Rune
ID: 40019308
Ask administrator for access in msdb database, the role SQLAgentUser and make sure your account owns the job you need to manage.

the only other cause I can see is a disabled or malfunctioning SQL agent service.

Regards Marten

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question