• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6311
  • Last Modified:

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

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
As
Begin
            Drop table dbo.FTPBackup
            Select * into FTPBackup from FTP
End
0
Aparanjith
Asked:
Aparanjith
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
DultonCommented:
I think you need the SQL server agent running to get any job (SSIS/T-SQL/SSRS, etc) to run on a schedule.
0
 
AparanjithAuthor Commented:
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?
0
 
DultonCommented:
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.
http://technet.microsoft.com/en-us/library/ms189237(v=sql.100).aspx

-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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
1
 
AparanjithAuthor Commented:
No i am using SSMS 2008 R2, but i do not have the access to SQL server Agent to schedule the jobs.
0
 
Anthony PerkinsCommented:
No i am using SSMS 2008 R2
But that was not the question.  Please post the results of this:
SELECT  SERVERPROPERTY('Edition')
0
 
AparanjithAuthor Commented:
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)
0
 
Anthony PerkinsCommented:
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.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now