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
  • 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.
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now