Monthly Subscriptions

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

Monitoring replication from subscriber

Hi,
I have a replication scenario where multiple laptops merge replicate with a
central server. The subscriptions are set up as push subscriptions on the
distributor. My users want a tool to tell when they last replicated, and if
possible, how many records are waiting to be replicated. They need this
while disconnected from the server. Is there a way to get this information,
without having access to the publisher/distributor?
Thanks,
John Loveland
John,
have a look on my site for a script to find pending merge changes. There is
some discussion about the reliability of the data in it, but I've found it a
useful bit of data to use.
For the last time merged using a push, perhaps look at
sysmergesubscriptions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks. That looks like exactly what I need.
John Loveland
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23A7Ma05FGHA.2568@.TK2MSFTNGP10.phx.gbl...
> John,
> have a look on my site for a script to find pending merge changes. There
> is some discussion about the reliability of the data in it, but I've found
> it a useful bit of data to use.
> For the last time merged using a push, perhaps look at
> sysmergesubscriptions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>