Solved

Executing a Stored Procedure on certain days/dates

Posted on 2013-06-13
3
290 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 150 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

740 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