Solved

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

Posted on 2014-04-22
9
5,643 Views
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
As
Begin
            Drop table dbo.FTPBackup
            Select * into FTPBackup from FTP
End
0
Comment
Question by:Aparanjith
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:Dulton
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.
0
 

Author Comment

by:Aparanjith
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?
0
 
LVL 6

Accepted Solution

by:
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: 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
1
 

Author Comment

by:Aparanjith
ID: 40016538
No i am using SSMS 2008 R2, but i do not have the access to SQL server Agent to schedule the jobs.
0
 
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:
SELECT  SERVERPROPERTY('Edition')
0
 

Author Comment

by:Aparanjith
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)
0
 
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.
0
 
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
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

733 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