?
Solved

Executing a Stored Procedure on certain days/dates

Posted on 2013-06-13
3
Medium Priority
?
296 Views
Last Modified: 2013-06-14
Hi,

I need to trigger a stored procedure only on a Monday, or on the 1st of the month.
Unfortunately, I have no access to the scheduled jobs in the SQL Server Agent, so cannot set up my own schedule. There is a scheduled job that runs daily, and all it does is trigger all daily stored procedures, so I can only execute my stored procedures from here. I think all I really need to do run a quick check at the start of the sp, and see if the current date is either a Monday, or a 1st of the month. I guess, something like this (and forgive me, I have very limited exposure to SQL) -- Below I am trying to trigger spTEST, only if the current day is a "Thursday"

SELECT
CASE WHEN DATENAME(DW,CONVERT(DATE,REPLACE(GETDATE(),'-',''),103)) = 'Thursday'
THEN EXEC ([mi].[spTEST])
0
Comment
Question by:BananaFury
[X]
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 Comments
 
LVL 2

Accepted Solution

by:
vivekkumarSharma earned 450 total points
ID: 39244969
two solutions

select case when datepart(dw,GETDATE())= 2 or datepart(dd,GETDATE()) =1 then EXEC ([mi].[spTEST] else print 'No Execution of any SP' end


OR

if datepart(dw,GETDATE())= 2 or datepart(dd,GETDATE()) =1
begin
EXEC ([mi].[spTEST]
end
else
begin
print 'No Execution of any SP'
end
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39245227
The method below will not be affected by DATEFIRST or language settings:


IF DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 --Monday (0=Mon,1=Tue,...,6=Sun).
OR DATEPART(DAY, GETDATE()) = 1 --first day of any month
    EXEC [mi].[spTEST]
0
 

Author Closing Comment

by:BananaFury
ID: 39247619
Great, thanks for your help!
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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