schedule end of month

i'd like to schedule some reports to run just before midnight on the last day
of each month. it doesn't appear that the RS interface allows me to create
just one schedule that would do that (i end up creating 3 schedules: 1 for
months ending 30th, 1 for months ending 31st, 1 for February).
on the sql agent side of things, its interface allows scheduling on the last
day of the month, but you're not supposed to mess with that...
am i missing something? is there an easy workaround? thanksOn Jul 27, 9:12 am, burkecrosby
<burkecro...@.discussions.microsoft.com> wrote:
> i'd like to schedule some reports to run just before midnight on the last day
> of each month. it doesn't appear that the RS interface allows me to create
> just one schedule that would do that (i end up creating 3 schedules: 1 for
> months ending 30th, 1 for months ending 31st, 1 for February).
> on the sql agent side of things, its interface allows scheduling on the last
> day of the month, but you're not supposed to mess with that...
> am i missing something? is there an easy workaround? thanks
Took me a while to figure it out, but it works great. It might not be
exactly what you want, but who knows it may work out for you. I got a
request from a user that they get the previous month's report on the
first of the month at 7am (time doesn't matter here). So for example,
on August 1st at 7am, she will receive a report for July1-31st. You
can change the time to be 12:00am or whatever.
First in your report make two datasets with the following:
Dataset 1 (I called mine StartofMonth):
select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
Dataset 2 (I called mine EndofMonth):
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
In my main dataset, the code looks for data between two parameters
@.startdate and @.enddate. Default these parameters (under
Report>Report Parameters...) to the corresponding datasets. The
"field" here should just be ID. Run the report, it should pull data
from the first to the last of last month.
Now go to your subscription and make your schedule. Under Schedule
Details (when you select to make your own schedule) click the Month
radio button. Pick all the month (using the check boxes) and then
select the radio labeled "On Calendar day(s):" and place a 1 in that
box. Now just adjust your start time as you see necessary. August
1st is coming soon, so you can see the result of your effort soon!!!
Only issue with this work around is that whenever your users open the
report (if they ever will) the dates will default to the first and
last day of the previous month. Oh well... they can change it
manually I guess or you can make a specific report (duplicate) that is
just for subscriptions. Also, for sanity, you might want to display
your parameters on the report so you see that it worked correctly.
Let me know if that works out for you!|||thanks Ayman. i can see and understand your method and may use that for
future reports. i was hoping not to have to redo the report query because of
the oddities of my source data.
i don't understand why MSFT has the difference between the scheduling
interfaces of RS vs SQL Server Agent. seems like RS could have just
inherited from SQL Agent...
burke
"Ayman" wrote:
> On Jul 27, 9:12 am, burkecrosby
> <burkecro...@.discussions.microsoft.com> wrote:
> > i'd like to schedule some reports to run just before midnight on the last day
> > of each month. it doesn't appear that the RS interface allows me to create
> > just one schedule that would do that (i end up creating 3 schedules: 1 for
> > months ending 30th, 1 for months ending 31st, 1 for February).
> >
> > on the sql agent side of things, its interface allows scheduling on the last
> > day of the month, but you're not supposed to mess with that...
> >
> > am i missing something? is there an easy workaround? thanks
> Took me a while to figure it out, but it works great. It might not be
> exactly what you want, but who knows it may work out for you. I got a
> request from a user that they get the previous month's report on the
> first of the month at 7am (time doesn't matter here). So for example,
> on August 1st at 7am, she will receive a report for July1-31st. You
> can change the time to be 12:00am or whatever.
> First in your report make two datasets with the following:
> Dataset 1 (I called mine StartofMonth):
> select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> Dataset 2 (I called mine EndofMonth):
> select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> In my main dataset, the code looks for data between two parameters
> @.startdate and @.enddate. Default these parameters (under
> Report>Report Parameters...) to the corresponding datasets. The
> "field" here should just be ID. Run the report, it should pull data
> from the first to the last of last month.
> Now go to your subscription and make your schedule. Under Schedule
> Details (when you select to make your own schedule) click the Month
> radio button. Pick all the month (using the check boxes) and then
> select the radio labeled "On Calendar day(s):" and place a 1 in that
> box. Now just adjust your start time as you see necessary. August
> 1st is coming soon, so you can see the result of your effort soon!!!
> Only issue with this work around is that whenever your users open the
> report (if they ever will) the dates will default to the first and
> last day of the previous month. Oh well... they can change it
> manually I guess or you can make a specific report (duplicate) that is
> just for subscriptions. Also, for sanity, you might want to display
> your parameters on the report so you see that it worked correctly.
> Let me know if that works out for you!
>|||On Jul 27, 10:44 am, burkecrosby
<burkecro...@.discussions.microsoft.com> wrote:
> thanks Ayman. i can see and understand your method and may use that for
> future reports. i was hoping not to have to redo the report query because of
> the oddities of my source data.
> i don't understand why MSFT has the difference between the scheduling
> interfaces of RS vs SQL Server Agent. seems like RS could have just
> inherited from SQL Agent...
> burke
> "Ayman" wrote:
> > On Jul 27, 9:12 am, burkecrosby
> > <burkecro...@.discussions.microsoft.com> wrote:
> > > i'd like to schedule some reports to run just before midnight on the last day
> > > of each month. it doesn't appear that the RS interface allows me to create
> > > just one schedule that would do that (i end up creating 3 schedules: 1 for
> > > months ending 30th, 1 for months ending 31st, 1 for February).
> > > on the sql agent side of things, its interface allows scheduling on the last
> > > day of the month, but you're not supposed to mess with that...
> > > am i missing something? is there an easy workaround? thanks
> > Took me a while to figure it out, but it works great. It might not be
> > exactly what you want, but who knows it may work out for you. I got a
> > request from a user that they get the previous month's report on the
> > first of the month at 7am (time doesn't matter here). So for example,
> > on August 1st at 7am, she will receive a report for July1-31st. You
> > can change the time to be 12:00am or whatever.
> > First in your report make two datasets with the following:
> > Dataset 1 (I called mine StartofMonth):
> > select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
> > Dataset 2 (I called mine EndofMonth):
> > select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
> > In my main dataset, the code looks for data between two parameters
> > @.startdate and @.enddate. Default these parameters (under
> > Report>Report Parameters...) to the corresponding datasets. The
> > "field" here should just be ID. Run the report, it should pull data
> > from the first to the last of last month.
> > Now go to your subscription and make your schedule. Under Schedule
> > Details (when you select to make your own schedule) click the Month
> > radio button. Pick all the month (using the check boxes) and then
> > select the radio labeled "On Calendar day(s):" and place a 1 in that
> > box. Now just adjust your start time as you see necessary. August
> > 1st is coming soon, so you can see the result of your effort soon!!!
> > Only issue with this work around is that whenever your users open the
> > report (if they ever will) the dates will default to the first and
> > last day of the previous month. Oh well... they can change it
> > manually I guess or you can make a specific report (duplicate) that is
> > just for subscriptions. Also, for sanity, you might want to display
> > your parameters on the report so you see that it worked correctly.
> > Let me know if that works out for you!
I'm not that knowledgeable but I can tell you, it doesn't matter what
datasource you use the code is universal across all SQL databases.
It's an easy fix to a tedious and annoying problem. I wish there was
an easier way too, but hey it was a good coding experience for me!! I
will be using the same method across any other reports that need
subscriptions.
You don't need to change any scripts at all, well... except the main
script where you need to add the data time filter. Hmmm... not sure
what to tell you now...

schedule database growth for midnight.

Hi Everybody
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?
Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>
|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegr oups.com...
>

schedule database growth for midnight.

Hi Everybody
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
>

schedule database growth for midnight.

Hi Everybody
My MSSQL 2000 8.00.818 database stores large amounts of binary data
and can be gigabytes in size. When the databases automatically grow,
they can take a while. This causes web pages which are waiting for
results from queries, to fail. Growth has been set to 1% to decrease
grow time. But I want to know if it is possible to set a schedule to
run at night, to make sure there is at least 10% free space. Can this
be done?Why don't you just increase the database size by a LOT once, anticipating
long-term growth, instead of trying to predict reactionary growth?
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||I agree 100% with Aaron in that you should make it as large as you think it
needs to be for quite some time. In any case you can schedule growth by
using a standard SQL Agent job and ALTER DATABASE command. And don't set the
auto growth to a %. Use a fixed amount in MB or GB that you know can grow in
well under the timeout period. A percentage is useless as the size gets
larger each time the db grows and you no longer have control in how long
each one takes.
--
Andrew J. Kelly SQL MVP
"geist3" <jcockrell@.gmail.com> wrote in message
news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
> Hi Everybody
> My MSSQL 2000 8.00.818 database stores large amounts of binary data
> and can be gigabytes in size. When the databases automatically grow,
> they can take a while. This causes web pages which are waiting for
> results from queries, to fail. Growth has been set to 1% to decrease
> grow time. But I want to know if it is possible to set a schedule to
> run at night, to make sure there is at least 10% free space. Can this
> be done?
>|||Put me down for third on the "be proactive" side. Just make the darn
database HUGE once. This will also avoid disk file fragmentation and lead
to better performance in the long run, as well as providing free space so
that index maintenance can lay data back down in sequential order - again
improving performance.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORghqCIyHHA.4276@.TK2MSFTNGP05.phx.gbl...
>I agree 100% with Aaron in that you should make it as large as you think it
>needs to be for quite some time. In any case you can schedule growth by
>using a standard SQL Agent job and ALTER DATABASE command. And don't set
>the auto growth to a %. Use a fixed amount in MB or GB that you know can
>grow in well under the timeout period. A percentage is useless as the size
>gets larger each time the db grows and you no longer have control in how
>long each one takes.
> --
> Andrew J. Kelly SQL MVP
> "geist3" <jcockrell@.gmail.com> wrote in message
> news:1184679861.142847.322970@.z28g2000prd.googlegroups.com...
>> Hi Everybody
>> My MSSQL 2000 8.00.818 database stores large amounts of binary data
>> and can be gigabytes in size. When the databases automatically grow,
>> they can take a while. This causes web pages which are waiting for
>> results from queries, to fail. Growth has been set to 1% to decrease
>> grow time. But I want to know if it is possible to set a schedule to
>> run at night, to make sure there is at least 10% free space. Can this
>> be done?
>