Looking for assistance out of a difficult spot. I have several subscriptions
that need to run on the last day of each month. However, Reporting Services
has a problem if I set the schedule to run on the 30th or 31st of each month.
Has anyone figured out how to do this? Currently, I must manually reset the
schedule on each subscription at the beginning of each month to run on the
last day. I must be overlooking somthing obvious as I can't imagine
Reporting Services would have such an oversight. Can anyone help?
Thanks very muchThis is a multi-part message in MIME format.
--=_NextPart_000_0015_01C5CF46.69DFEB70
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
This is a hack, but for every subscription you set up, RS creates a SQL =Server Agent job with (amazingly!) the same schedule. You could and ="could" is the operative word, have another sql agent job that runs once =a day at a specific time and if it's the end of the month, then push the =same job step as is in the subscription.
HTH
-- TIM ELLISON
"Andy" <Andy@.discussions.microsoft.com> wrote in message =news:A21B735C-ACE2-4402-87EA-5CA15CB71DE5@.microsoft.com...
Looking for assistance out of a difficult spot. I have several =subscriptions that need to run on the last day of each month. However, Reporting =Services has a problem if I set the schedule to run on the 30th or 31st of each =month. Has anyone figured out how to do this? Currently, I must manually =reset the schedule on each subscription at the beginning of each month to run on =the last day. I must be overlooking somthing obvious as I can't imagine Reporting Services would have such an oversight. Can anyone help?
Thanks very much
--=_NextPart_000_0015_01C5CF46.69DFEB70
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
This is a hack, but for every =subscription you set up, RS creates a SQL Server Agent job with (amazingly!) the same =schedule. You could and "could" is the operative word, have another sql agent job =that runs once a day at a specific time and if it's the end of the month, =then push the same job step as is in the subscription.
HTH
-- TIM ELLISON
"Andy" wrote in message news:A21=B735C-ACE2-4402-87EA-5CA15CB71DE5@.microsoft.com...Looking for assistance out of a difficult spot. I have several =subscriptions that need to run on the last day of each month. However, =Reporting Services has a problem if I set the schedule to run on the 30th or =31st of each month. Has anyone figured out how to do this? =Currently, I must manually reset the schedule on each subscription at the =beginning of each month to run on the last day. I must be overlooking =somthing obvious as I can't imagine Reporting Services would have such an oversight. Can anyone help?Thanks very =much
--=_NextPart_000_0015_01C5CF46.69DFEB70--|||Thanks Tim for the suggestion. Unfortunately, I don't have access to the SQL
Server to be able to access the agent.
Any other thoughts?
Anyone?
"Tim Ellison" wrote:
> This is a hack, but for every subscription you set up, RS creates a SQL Server Agent job with (amazingly!) the same schedule. You could and "could" is the operative word, have another sql agent job that runs once a day at a specific time and if it's the end of the month, then push the same job step as is in the subscription.
> HTH
> --
> TIM ELLISON
> "Andy" <Andy@.discussions.microsoft.com> wrote in message news:A21B735C-ACE2-4402-87EA-5CA15CB71DE5@.microsoft.com...
> Looking for assistance out of a difficult spot. I have several subscriptions
> that need to run on the last day of each month. However, Reporting Services
> has a problem if I set the schedule to run on the 30th or 31st of each month.
> Has anyone figured out how to do this? Currently, I must manually reset the
> schedule on each subscription at the beginning of each month to run on the
> last day. I must be overlooking somthing obvious as I can't imagine
> Reporting Services would have such an oversight. Can anyone help?
> Thanks very much|||I would use a data-driven subscription.
A data-driven subscription runs for each record returned in a specified
query (set when you set it up).
Set the schedule to run every day and set the query to be:
SELECT 1
WHERE CASE DATEPART(month, @.DATE)
WHEN DATEPART(month, DATEADD(day, 1, @.DATE)) THEN 0
ELSE 1
END = 1
This will return a record anytime that it is the last day of the month.
Hope it helps.
"Andy" wrote:
> Thanks Tim for the suggestion. Unfortunately, I don't have access to the SQL
> Server to be able to access the agent.
> Any other thoughts?
> Anyone?
> "Tim Ellison" wrote:
> > This is a hack, but for every subscription you set up, RS creates a SQL Server Agent job with (amazingly!) the same schedule. You could and "could" is the operative word, have another sql agent job that runs once a day at a specific time and if it's the end of the month, then push the same job step as is in the subscription.
> >
> > HTH
> >
> > --
> > TIM ELLISON
> > "Andy" <Andy@.discussions.microsoft.com> wrote in message news:A21B735C-ACE2-4402-87EA-5CA15CB71DE5@.microsoft.com...
> > Looking for assistance out of a difficult spot. I have several subscriptions
> > that need to run on the last day of each month. However, Reporting Services
> > has a problem if I set the schedule to run on the 30th or 31st of each month.
> > Has anyone figured out how to do this? Currently, I must manually reset the
> > schedule on each subscription at the beginning of each month to run on the
> > last day. I must be overlooking somthing obvious as I can't imagine
> > Reporting Services would have such an oversight. Can anyone help?
> > Thanks very much|||Not sure but: Change the @.DATE in my previous post to GETDATE(). I think I
posted something I was testing.
"Andy" wrote:
> Thanks Tim for the suggestion. Unfortunately, I don't have access to the SQL
> Server to be able to access the agent.
> Any other thoughts?
> Anyone?
> "Tim Ellison" wrote:
> > This is a hack, but for every subscription you set up, RS creates a SQL Server Agent job with (amazingly!) the same schedule. You could and "could" is the operative word, have another sql agent job that runs once a day at a specific time and if it's the end of the month, then push the same job step as is in the subscription.
> >
> > HTH
> >
> > --
> > TIM ELLISON
> > "Andy" <Andy@.discussions.microsoft.com> wrote in message news:A21B735C-ACE2-4402-87EA-5CA15CB71DE5@.microsoft.com...
> > Looking for assistance out of a difficult spot. I have several subscriptions
> > that need to run on the last day of each month. However, Reporting Services
> > has a problem if I set the schedule to run on the 30th or 31st of each month.
> > Has anyone figured out how to do this? Currently, I must manually reset the
> > schedule on each subscription at the beginning of each month to run on the
> > last day. I must be overlooking somthing obvious as I can't imagine
> > Reporting Services would have such an oversight. Can anyone help?
> > Thanks very much
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment