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

Posted on 2014-04-22
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 250 total points
ID: 40015873
Oh, you can certainly create jobs and load them into the schedule with scripts.

Here's an example:

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.
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 250 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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