Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

610 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