Scheduling Subscription On Day 30 of a Month

I tried to setup a monthly subscription, to be run on Day 30 of a month.
After clicking the radio button for "Month", then click the radio button for
"On Calendar Days", then enter the day 30, and press OK. The subscription
will return the following error message:To create a schedule that runs on
multiple days, you must choose which days to use.
This message will not appear, if I enter any other day, up to and including
day 29.I have to deselect Month of Februrary, which does not have 30 days
"LBJOHN" wrote:
> I tried to setup a monthly subscription, to be run on Day 30 of a month.
> After clicking the radio button for "Month", then click the radio button for
> "On Calendar Days", then enter the day 30, and press OK. The subscription
> will return the following error message:To create a schedule that runs on
> multiple days, you must choose which days to use.
> This message will not appear, if I enter any other day, up to and including
> day 29.
>

Scheduled reboot of SQL Server on Windows 2000

I am descerning whether it is a good idea (or necessary) to reboot our SQL
Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
there any performance benefits in doing this? Does this help to defragment
memory?
There's no real benefit. However, you will likely incur a performance
penalty each time, since it will have to repopulate the data cache.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Cajun" <Cajun@.discussions.microsoft.com> wrote in message
news:FECF4E55-A44B-42CE-86E0-9D0915BD6490@.microsoft.com...
I am descerning whether it is a good idea (or necessary) to reboot our SQL
Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
there any performance benefits in doing this? Does this help to defragment
memory?
|||Hi -
Experts says :-
"This is a fairly common question, and the answer is fairly straight
forward, no. Assuming you are using Windows NT Server 4.0 SP6a, or Windows
2000 Server SP4, or Windows 2003 Server, and are running SQL Server 7.0 or
2000 (any service packs), there is no reason to automatically reboot your
server. Doing so will not offer you any performance benefits or enhance
reliability.
There is a common myth among many IS people that Windows Server software
needs to be rebooted regularly for it to work efficiently. There may have
been a grain of truth to this in previous versions of Windows NT Server
(before 4.0), but since 4.0, there has not been any need to reboot Windows
Server on a regular basis.
I have seen many, many Windows servers that are virtually never rebooted,
and they never have any problems due to the OS.
On the other hand, I have seen poorly written applications written for
Windows Server that have memory leaks that have force the need to reboot the
server on a regular basis. But this problem is the fault of the application,
not the OS. It is very possible that less knowledgeable IS staff in general
have improperly diagnosed the cause of a server's problem and blamed it on
the OS, and not the application as they should, which is perpetuating this
myth.
Since your SQL Server is not having any problems, leave it alone, and tell
the other people on your staff to stop listening to urban legends."
"Cajun" wrote:

> I am descerning whether it is a good idea (or necessary) to reboot our SQL
> Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
> there any performance benefits in doing this? Does this help to defragment
> memory?

Scheduled reboot of SQL Server on Windows 2000

I am descerning whether it is a good idea (or necessary) to reboot our SQL
Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
there any performance benefits in doing this? Does this help to defragment
memory?There's no real benefit. However, you will likely incur a performance
penalty each time, since it will have to repopulate the data cache.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Cajun" <Cajun@.discussions.microsoft.com> wrote in message
news:FECF4E55-A44B-42CE-86E0-9D0915BD6490@.microsoft.com...
I am descerning whether it is a good idea (or necessary) to reboot our SQL
Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
there any performance benefits in doing this? Does this help to defragment
memory?|||Hi -
Experts says :-
"This is a fairly common question, and the answer is fairly straight
forward, no. Assuming you are using Windows NT Server 4.0 SP6a, or Windows
2000 Server SP4, or Windows 2003 Server, and are running SQL Server 7.0 or
2000 (any service packs), there is no reason to automatically reboot your
server. Doing so will not offer you any performance benefits or enhance
reliability.
There is a common myth among many IS people that Windows Server software
needs to be rebooted regularly for it to work efficiently. There may have
been a grain of truth to this in previous versions of Windows NT Server
(before 4.0), but since 4.0, there has not been any need to reboot Windows
Server on a regular basis.
I have seen many, many Windows servers that are virtually never rebooted,
and they never have any problems due to the OS.
On the other hand, I have seen poorly written applications written for
Windows Server that have memory leaks that have force the need to reboot the
server on a regular basis. But this problem is the fault of the application,
not the OS. It is very possible that less knowledgeable IS staff in general
have improperly diagnosed the cause of a server's problem and blamed it on
the OS, and not the application as they should, which is perpetuating this
myth.
Since your SQL Server is not having any problems, leave it alone, and tell
the other people on your staff to stop listening to urban legends."
"Cajun" wrote:

> I am descerning whether it is a good idea (or necessary) to reboot our SQL
> Server 2000 servers on a regular basis (E.g., monthly or quarterly). Are
> there any performance benefits in doing this? Does this help to defragmen
t
> memory?sql

Schedule Monthly Job -Skip first friday of every mon- Run on Mon

Hi,

I need to schedule a job that runs monthly on Mondays after the first friday on the month. How do i setup the schedule. let me know.

Thanks

There is no feature yet in SQL Server but I have posted a workaround from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=707244&SiteID=1 which you can have a look. What is your job doing?

|||

One 'simple' way is to create two jobs.

For the Job you want to run on the first Monday following the First Friday of each month:

Set its' schedule to Monday of each week,

AND set the Job Schedule enabled to [False] (clear the Enabled checkbox on the Job Schedule properties form).

Verify that the Job itself is enabled.

Add a NEW last step that will (verify that the job will step into the last step.)

EXECUTE sp_update_jobschedule @.job_name = 'MyJobName', @.enabled= 0

--(0=FALSE) Check Books Online for full syntax

The Second job is scheduled to run on the FIRST Friday of each month. It has only one step, and that step will

EXECUTE sp_update_jobschedule @.job_name = 'MyJobName', @.enabled= 1

In operation, the Second Job will run on the first Friday of each month. It will then enable (Turn On) the first job so that the first job will then run on the next Monday. After the first job completes its' task, it will turn its schedule off and nothing will happen until the next month when it gets turned on again.|||I dont think i understand the above example. I need to run an SSIS package every month on a monday after the 1st friday of the month.|||

Arnie is saying that you create two jobs: The first one does what you need to do and then disables itself. This is scheduled to run every Monday. The second job runs on the first Friday of the month and just enables the first job.

The first job is initially disabled. Each Monday comes and the job doesn't run because it is disabled. The first Friday of the month comes and the second job enables the first job. On the next Monday, the first job runs because it's Monday and the job is enabled. After the Monday job completes, it disables itself so it won't run until the second job enables it again next month.

Thanks,

Steve

|||Thanks Steven.|||

Thanks for your responses. Will the below example do the same things

1) I will create a step1 with the below sql code and in my second step run the job. Configure the step 1 to quit job on failure and on success go to the next step. Let me know how it looks.

Thanks

declare @.dt datetime
set @.dt= DATEADD(wk, DATEDIFF(wk,4,dateadd(dd,2-datepart(day,getdate()),getdate())), 7)
if (getdate() <> @.dt)
BEGIN
SELECT @.dt, 'Monday After 1st Friday'
RAISERROR (N'Not a First Monday after Friday', 16,1)
END

Schedule Monthly Job -Skip first friday of every mon- Run on Mon

Hi,

I need to schedule a job that runs monthly on Mondays after the first friday on the month. How do i setup the schedule. let me know.

Thanks

There is no feature yet in SQL Server but I have posted a workaround from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=707244&SiteID=1 which you can have a look. What is your job doing?

|||

One 'simple' way is to create two jobs.

For the Job you want to run on the first Monday following the First Friday of each month:

Set its' schedule to Monday of each week,

AND set the Job Schedule enabled to [False] (clear the Enabled checkbox on the Job Schedule properties form).

Verify that the Job itself is enabled.

Add a NEW last step that will (verify that the job will step into the last step.)

EXECUTE sp_update_jobschedule @.job_name = 'MyJobName', @.enabled= 0

--(0=FALSE) Check Books Online for full syntax

The Second job is scheduled to run on the FIRST Friday of each month. It has only one step, and that step will

EXECUTE sp_update_jobschedule @.job_name = 'MyJobName', @.enabled= 1

In operation, the Second Job will run on the first Friday of each month. It will then enable (Turn On) the first job so that the first job will then run on the next Monday. After the first job completes its' task, it will turn its schedule off and nothing will happen until the next month when it gets turned on again.|||I dont think i understand the above example. I need to run an SSIS package every month on a monday after the 1st friday of the month.|||

Arnie is saying that you create two jobs: The first one does what you need to do and then disables itself. This is scheduled to run every Monday. The second job runs on the first Friday of the month and just enables the first job.

The first job is initially disabled. Each Monday comes and the job doesn't run because it is disabled. The first Friday of the month comes and the second job enables the first job. On the next Monday, the first job runs because it's Monday and the job is enabled. After the Monday job completes, it disables itself so it won't run until the second job enables it again next month.

Thanks,

Steve

|||Thanks Steven.|||

Thanks for your responses. Will the below example do the same things

1) I will create a step1 with the below sql code and in my second step run the job. Configure the step 1 to quit job on failure and on success go to the next step. Let me know how it looks.

Thanks

declare @.dt datetime
set @.dt= DATEADD(wk, DATEDIFF(wk,4,dateadd(dd,2-datepart(day,getdate()),getdate())), 7)
if (getdate() <> @.dt)
BEGIN
SELECT @.dt, 'Monday After 1st Friday'
RAISERROR (N'Not a First Monday after Friday', 16,1)
END

Schedule monthly job

I have a need to set up an odd schedule.

I want to run a job ever saturday of the month EXCEPT the 3rd saturday (where I need to run the job on Sunday).

So I set up 4 jobs -- using monthly frequency I selected the first Sat, the second Sat, the third Sun, and the forth Sat.

Sounds good. Right? Not quite. Look at a calendar for September 2006... it has FIVE saturday's. There is no option for the "fifth" of a day and if I use the "last" day I suspect that my job will run twice when there are only four instances of a day.

Any ideas?

Jason,

Thanks for your feedback. SQL Agent Scheduling options available in SQL 2005 may not meet your specific need. Could you please log this issue as a feature request through Connect web site so that our product team could take a look at your feature request?

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

You could try a different approach. You could create T-SQL job step add the first job step in your job step sequence and let the T-SQL code determine if it was the 3rd saturday

1) Create a monthly schedule that runs every Saturday and Sunday

2) Let the first job step be a T-SQL job step that executes T-SQL code to determine

a) if current date was a third saturday and issue a RAISEERROR

b) if current date was not a fourth Sunday then issue a RAISERROR

Example t-sql code:

declare @.tmpdate datetime

SET @.tmpdate = '09/10/2006' --GETDATE()

declare @.firstday_in_month datetime

SET @.firstday_in_month = DATEADD(DD, 1 - DATEPART(dd, @.tmpdate), @.tmpdate)

declare @.week_for_firstday_in_month int

SET @.week_for_firstday_in_month = DATEPART(wk, @.firstday_in_month)

declare @.week_for_currentday int

SET @.week_for_currentday = DATEPART(wk, @.tmpdate)

declare @.week_in_this_month int

SET @.week_in_this_month = @.week_for_currentday - @.week_for_firstday_in_month + 1

-- check if date is 3rd saturday in this month

IF(DATEPART(dw, @.tmpdate) = 7) AND ( @.week_in_this_month = 3)

BEGIN

SELECT @.tmpdate, 'Saturday', @.week_in_this_month

RAISERROR (N'Third Saturday', 16,1)

END

-- check if date is not the 4th Sunday in this month

IF(DATEPART(dw, @.tmpdate) = 1) AND ( @.week_in_this_month <> 4)

BEGIN

SELECT @.tmpdate, 'Sunday', @.week_in_this_month

RAISERROR (N'Not a Third Sunday', 16,1)

END

3) In the job step defined in step 2, In Advanced Tab, "On Failure Action", select "Quit the job reporting success"

4) In Job step defined in step 2, In Advanced Tab, "On Success Action", select "Goto next step"

5) Add all other job steps specific to this job after the first job step

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Excellent workaround Sethu. Thanks.

And I will submit it as an enhancement.

|||

Sethu,

I have a similar schedule issue. I need to run a job every month on mondays after the 1st friday of the month. How do I accomplish this. Let meknow

|||

I created a simple script using VBScript with a combination of TSQL to check whether I should run a weekly or monthly job. Here's a sample syntax I used

Dim objShell

SET objShell = CreateObject("Wscript.Shell")

'change this value to any number between 1 to 7 being Sunday to Saturday

If Weekday(Date)=1 Then
If DatePart("m",(DateAdd("d",7,Date()))) <> DatePart("m",Date()) Then
'Run this batch file if it is NOT the last Sunday of the month
objShell.Run("C:\weekly_backup.bat")
Else
'Run this batch file if it is the LAST Sunday of the month
objShell.Run("C:\monthly_backup.bat")
End If
Else
'Run this batch file if it is a weekday
objShell.Run("C:\daily_backup.bat")

End if

SET objShell=NOTHING

My batch files are simply calls to either osql or sqlcmd which does a full backup

Schedule monthly job

I have a need to set up an odd schedule.

I want to run a job ever saturday of the month EXCEPT the 3rd saturday (where I need to run the job on Sunday).

So I set up 4 jobs -- using monthly frequency I selected the first Sat, the second Sat, the third Sun, and the forth Sat.

Sounds good. Right? Not quite. Look at a calendar for September 2006... it has FIVE saturday's. There is no option for the "fifth" of a day and if I use the "last" day I suspect that my job will run twice when there are only four instances of a day.

Any ideas?

Jason,

Thanks for your feedback. SQL Agent Scheduling options available in SQL 2005 may not meet your specific need. Could you please log this issue as a feature request through Connect web site so that our product team could take a look at your feature request?

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

You could try a different approach. You could create T-SQL job step add the first job step in your job step sequence and let the T-SQL code determine if it was the 3rd saturday

1) Create a monthly schedule that runs every Saturday and Sunday

2) Let the first job step be a T-SQL job step that executes T-SQL code to determine

a) if current date was a third saturday and issue a RAISEERROR

b) if current date was not a fourth Sunday then issue a RAISERROR

Example t-sql code:

declare @.tmpdate datetime

SET @.tmpdate = '09/10/2006' --GETDATE()

declare @.firstday_in_month datetime

SET @.firstday_in_month = DATEADD(DD, 1 - DATEPART(dd, @.tmpdate), @.tmpdate)

declare @.week_for_firstday_in_month int

SET @.week_for_firstday_in_month = DATEPART(wk, @.firstday_in_month)

declare @.week_for_currentday int

SET @.week_for_currentday = DATEPART(wk, @.tmpdate)

declare @.week_in_this_month int

SET @.week_in_this_month = @.week_for_currentday - @.week_for_firstday_in_month + 1

-- check if date is 3rd saturday in this month

IF(DATEPART(dw, @.tmpdate) = 7) AND ( @.week_in_this_month = 3)

BEGIN

SELECT @.tmpdate, 'Saturday', @.week_in_this_month

RAISERROR (N'Third Saturday', 16,1)

END

-- check if date is not the 4th Sunday in this month

IF(DATEPART(dw, @.tmpdate) = 1) AND ( @.week_in_this_month <> 4)

BEGIN

SELECT @.tmpdate, 'Sunday', @.week_in_this_month

RAISERROR (N'Not a Third Sunday', 16,1)

END

3) In the job step defined in step 2, In Advanced Tab, "On Failure Action", select "Quit the job reporting success"

4) In Job step defined in step 2, In Advanced Tab, "On Success Action", select "Goto next step"

5) Add all other job steps specific to this job after the first job step

Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

|||

Excellent workaround Sethu. Thanks.

And I will submit it as an enhancement.

|||

Sethu,

I have a similar schedule issue. I need to run a job every month on mondays after the 1st friday of the month. How do I accomplish this. Let meknow

|||

I created a simple script using VBScript with a combination of TSQL to check whether I should run a weekly or monthly job. Here's a sample syntax I used

Dim objShell

SET objShell = CreateObject("Wscript.Shell")

'change this value to any number between 1 to 7 being Sunday to Saturday

If Weekday(Date)=1 Then
If DatePart("m",(DateAdd("d",7,Date()))) <> DatePart("m",Date()) Then
'Run this batch file if it is NOT the last Sunday of the month
objShell.Run("C:\weekly_backup.bat")
Else
'Run this batch file if it is the LAST Sunday of the month
objShell.Run("C:\monthly_backup.bat")
End If
Else
'Run this batch file if it is a weekday
objShell.Run("C:\daily_backup.bat")

End if

SET objShell=NOTHING

My batch files are simply calls to either osql or sqlcmd which does a full backup

Schedule at Month End

How do I create a schedule to run at month end?
I selected monthly, and tried to put 28,29,30,31 in calendar days, since
months vary, but I get this error "To create a schedule that runs on
multiple days, you must choose which days to use"
Any Ideas? I run monthly reports that create snapshots. I guess I
could run it at 12:01 on the 1st of the month, I but I used stored
procedures to calculate default dates and it really needs to run on the
last day of the month.
Thanks.Also, in addition to John's question, I have another question. How do I
name the report dynamically? If my report is for every month end, I want to
name it accordinly - JAN2005.csv, or something like that. In report Manager,
didn't see any option in generating filename.
Thanks
"John Geddes" wrote:
> How do I create a schedule to run at month end?
> I selected monthly, and tried to put 28,29,30,31 in calendar days, since
> months vary, but I get this error "To create a schedule that runs on
> multiple days, you must choose which days to use"
> Any Ideas? I run monthly reports that create snapshots. I guess I
> could run it at 12:01 on the 1st of the month, I but I used stored
> procedures to calculate default dates and it really needs to run on the
> last day of the month.
> Thanks.
>|||RS does not support this recurrence (last day of month). Your best bet is
to do what you suggested below. There is a way to get around this but it
has some side effects. After you create the schedule, find the
corresponding job in SQL Agent (doesn't matter what schedule you create
initially). Create the 12 schedules you will need in the sql agent job.
Now, RS will by default detect that the job is inconsistent with the RS
metadata and will update it. You can turn this off by updating the
IsSchedulingService to false in the RSReportServer.config file. Of course
by doing this RS will never check for consistency and it will be possible
for someone to update the schedules via SQL Agent (which is what you would
have done) and the recurrence will not correspond to what is being shown to
the user.
As for Vipul question, you can only accomplish this via Data Driven
subscriptions.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vipul Shah" <VipulShah@.discussions.microsoft.com> wrote in message
news:9790438A-4833-4E9C-888A-47C43930CB28@.microsoft.com...
> Also, in addition to John's question, I have another question. How do I
> name the report dynamically? If my report is for every month end, I want
> to
> name it accordinly - JAN2005.csv, or something like that. In report
> Manager,
> didn't see any option in generating filename.
> Thanks
> "John Geddes" wrote:
>> How do I create a schedule to run at month end?
>> I selected monthly, and tried to put 28,29,30,31 in calendar days, since
>> months vary, but I get this error "To create a schedule that runs on
>> multiple days, you must choose which days to use"
>> Any Ideas? I run monthly reports that create snapshots. I guess I
>> could run it at 12:01 on the 1st of the month, I but I used stored
>> procedures to calculate default dates and it really needs to run on the
>> last day of the month.
>> Thanks.
>>