conditional subscriptions / business day logic

I need to know if there's any way to refrain from sending an email if the
tSQL behind the rdl is empty? it's a trading report, clearing-type
information is sent out in a report via reporting services. i do not want to
send a blank email if no trades exist. but i must have it in
place/enabled/scheduled, such that the report does capture trades if/when
they occur. i've got the report and subscription created, i just now
received a blank email. i cannot do that once this is deployed, which needs
to happen asap
similarly, and slightly less urgent, can i add business day logic to the sql
agent job? i use this piece in many of my sql agent jobs:
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
BEGIN
...whatever...
END
this way i can remain consistent with the business days defined in my
calendar table. You know...I don't send a blank report on christmas day.
Is there any reason why i cannot put the same condition into the RS-created
SQL Agent jobs ?
I worry about this because I am still a bit of an RS newbie. I've several
reports working beautifully...but, I also have one report with multiple
subscriptions -- 4 email notices daily -- for some reason, the first one of
the day always sends a dupe. two identical emails...the other 3
subscriptions work just fine.
So, I'm just a little hesitant to change what I get once the subscription is
created.
Is anyone able to provide some guidance with these things? The blank report
thing is very urgent, everything else I have some time on.
-- Lynnthe hitchiker's guide to RS says it would seem i can set the NoRows property
to just give a message if/when the report returns no data. i'd prefer
nothing was sent, but this will do for now. but can anybody tell me where
that property is set?
-- Lynn
"Lynn" wrote:
> I need to know if there's any way to refrain from sending an email if the
> tSQL behind the rdl is empty? it's a trading report, clearing-type
> information is sent out in a report via reporting services. i do not want to
> send a blank email if no trades exist. but i must have it in
> place/enabled/scheduled, such that the report does capture trades if/when
> they occur. i've got the report and subscription created, i just now
> received a blank email. i cannot do that once this is deployed, which needs
> to happen asap
> similarly, and slightly less urgent, can i add business day logic to the sql
> agent job? i use this piece in many of my sql agent jobs:
> SET DATEFIRST 7
> IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> BEGIN
> ...whatever...
> END
> this way i can remain consistent with the business days defined in my
> calendar table. You know...I don't send a blank report on christmas day.
> Is there any reason why i cannot put the same condition into the RS-created
> SQL Agent jobs ?
> I worry about this because I am still a bit of an RS newbie. I've several
> reports working beautifully...but, I also have one report with multiple
> subscriptions -- 4 email notices daily -- for some reason, the first one of
> the day always sends a dupe. two identical emails...the other 3
> subscriptions work just fine.
> So, I'm just a little hesitant to change what I get once the subscription is
> created.
> Is anyone able to provide some guidance with these things? The blank report
> thing is very urgent, everything else I have some time on.
> -- Lynn|||NoRows is a property of the report objects and is in most object types
(Table, List, etc) except text,image and rectangle.
"Lynn" wrote:
> the hitchiker's guide to RS says it would seem i can set the NoRows property
> to just give a message if/when the report returns no data. i'd prefer
> nothing was sent, but this will do for now. but can anybody tell me where
> that property is set?
> -- Lynn
>
> "Lynn" wrote:
> > I need to know if there's any way to refrain from sending an email if the
> > tSQL behind the rdl is empty? it's a trading report, clearing-type
> > information is sent out in a report via reporting services. i do not want to
> > send a blank email if no trades exist. but i must have it in
> > place/enabled/scheduled, such that the report does capture trades if/when
> > they occur. i've got the report and subscription created, i just now
> > received a blank email. i cannot do that once this is deployed, which needs
> > to happen asap
> >
> > similarly, and slightly less urgent, can i add business day logic to the sql
> > agent job? i use this piece in many of my sql agent jobs:
> >
> > SET DATEFIRST 7
> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > BEGIN
> > ...whatever...
> > END
> >
> > this way i can remain consistent with the business days defined in my
> > calendar table. You know...I don't send a blank report on christmas day.
> > Is there any reason why i cannot put the same condition into the RS-created
> > SQL Agent jobs ?
> >
> > I worry about this because I am still a bit of an RS newbie. I've several
> > reports working beautifully...but, I also have one report with multiple
> > subscriptions -- 4 email notices daily -- for some reason, the first one of
> > the day always sends a dupe. two identical emails...the other 3
> > subscriptions work just fine.
> >
> > So, I'm just a little hesitant to change what I get once the subscription is
> > created.
> >
> > Is anyone able to provide some guidance with these things? The blank report
> > thing is very urgent, everything else I have some time on.
> >
> > -- Lynn|||Yes, I was able to find NoRows - while it won't let me refrain from sending
blank emails, if no data exists, it will let me send a nicer, helpful little
message, if / when no rows are found. do you have any thoughts about my
business day logic piece?
> similarly, and slightly less urgent, can i add business day logic to the sql
> > agent job? i use this piece in many of my sql agent jobs:
> >
> > SET DATEFIRST 7
> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > BEGIN
> > ...whatever...
> > END
> >
> > this way i can remain consistent with the business days defined in my
> > calendar table. You know...I don't send a blank report on christmas day.
> > Is there any reason why i cannot put the same condition into the RS-created
> > SQL Agent jobs ?
-- Lynn
"William" wrote:
> NoRows is a property of the report objects and is in most object types
> (Table, List, etc) except text,image and rectangle.
> "Lynn" wrote:
> > the hitchiker's guide to RS says it would seem i can set the NoRows property
> > to just give a message if/when the report returns no data. i'd prefer
> > nothing was sent, but this will do for now. but can anybody tell me where
> > that property is set?
> > -- Lynn
> >
> >
> > "Lynn" wrote:
> >
> > > I need to know if there's any way to refrain from sending an email if the
> > > tSQL behind the rdl is empty? it's a trading report, clearing-type
> > > information is sent out in a report via reporting services. i do not want to
> > > send a blank email if no trades exist. but i must have it in
> > > place/enabled/scheduled, such that the report does capture trades if/when
> > > they occur. i've got the report and subscription created, i just now
> > > received a blank email. i cannot do that once this is deployed, which needs
> > > to happen asap
> > >
> > > similarly, and slightly less urgent, can i add business day logic to the sql
> > > agent job? i use this piece in many of my sql agent jobs:
> > >
> > > SET DATEFIRST 7
> > > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > > BEGIN
> > > ...whatever...
> > > END
> > >
> > > this way i can remain consistent with the business days defined in my
> > > calendar table. You know...I don't send a blank report on christmas day.
> > > Is there any reason why i cannot put the same condition into the RS-created
> > > SQL Agent jobs ?
> > >
> > > I worry about this because I am still a bit of an RS newbie. I've several
> > > reports working beautifully...but, I also have one report with multiple
> > > subscriptions -- 4 email notices daily -- for some reason, the first one of
> > > the day always sends a dupe. two identical emails...the other 3
> > > subscriptions work just fine.
> > >
> > > So, I'm just a little hesitant to change what I get once the subscription is
> > > created.
> > >
> > > Is anyone able to provide some guidance with these things? The blank report
> > > thing is very urgent, everything else I have some time on.
> > >
> > > -- Lynn|||It would be nice if MS would find a way to pass the NoRows status up to the
report level so that we could optionally suppress emails. Maybe next release.
I am not very familiar with SQL Agent so I cannot advise on how to apply
your date logic.
"Lynn" wrote:
> Yes, I was able to find NoRows - while it won't let me refrain from sending
> blank emails, if no data exists, it will let me send a nicer, helpful little
> message, if / when no rows are found. do you have any thoughts about my
> business day logic piece?
> > similarly, and slightly less urgent, can i add business day logic to the sql
> > > agent job? i use this piece in many of my sql agent jobs:
> > >
> > > SET DATEFIRST 7
> > > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > > BEGIN
> > > ...whatever...
> > > END
> > >
> > > this way i can remain consistent with the business days defined in my
> > > calendar table. You know...I don't send a blank report on christmas day.
> > > Is there any reason why i cannot put the same condition into the RS-created
> > > SQL Agent jobs ?
> -- Lynn
>
> "William" wrote:
> > NoRows is a property of the report objects and is in most object types
> > (Table, List, etc) except text,image and rectangle.
> >
> > "Lynn" wrote:
> >
> > > the hitchiker's guide to RS says it would seem i can set the NoRows property
> > > to just give a message if/when the report returns no data. i'd prefer
> > > nothing was sent, but this will do for now. but can anybody tell me where
> > > that property is set?
> > > -- Lynn
> > >
> > >
> > > "Lynn" wrote:
> > >
> > > > I need to know if there's any way to refrain from sending an email if the
> > > > tSQL behind the rdl is empty? it's a trading report, clearing-type
> > > > information is sent out in a report via reporting services. i do not want to
> > > > send a blank email if no trades exist. but i must have it in
> > > > place/enabled/scheduled, such that the report does capture trades if/when
> > > > they occur. i've got the report and subscription created, i just now
> > > > received a blank email. i cannot do that once this is deployed, which needs
> > > > to happen asap
> > > >
> > > > similarly, and slightly less urgent, can i add business day logic to the sql
> > > > agent job? i use this piece in many of my sql agent jobs:
> > > >
> > > > SET DATEFIRST 7
> > > > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > > > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > > > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > > > BEGIN
> > > > ...whatever...
> > > > END
> > > >
> > > > this way i can remain consistent with the business days defined in my
> > > > calendar table. You know...I don't send a blank report on christmas day.
> > > > Is there any reason why i cannot put the same condition into the RS-created
> > > > SQL Agent jobs ?
> > > >
> > > > I worry about this because I am still a bit of an RS newbie. I've several
> > > > reports working beautifully...but, I also have one report with multiple
> > > > subscriptions -- 4 email notices daily -- for some reason, the first one of
> > > > the day always sends a dupe. two identical emails...the other 3
> > > > subscriptions work just fine.
> > > >
> > > > So, I'm just a little hesitant to change what I get once the subscription is
> > > > created.
> > > >
> > > > Is anyone able to provide some guidance with these things? The blank report
> > > > thing is very urgent, everything else I have some time on.
> > > >
> > > > -- Lynn|||Well, maybe I just need to try and see. Thank you, William, for your input
w/this. I do appreciate it.
-- Lynn
"William" wrote:
> It would be nice if MS would find a way to pass the NoRows status up to the
> report level so that we could optionally suppress emails. Maybe next release.
> I am not very familiar with SQL Agent so I cannot advise on how to apply
> your date logic.
> "Lynn" wrote:
> > Yes, I was able to find NoRows - while it won't let me refrain from sending
> > blank emails, if no data exists, it will let me send a nicer, helpful little
> > message, if / when no rows are found. do you have any thoughts about my
> > business day logic piece?
> >
> > > similarly, and slightly less urgent, can i add business day logic to the sql
> > > > agent job? i use this piece in many of my sql agent jobs:
> > > >
> > > > SET DATEFIRST 7
> > > > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > > > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > > > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > > > BEGIN
> > > > ...whatever...
> > > > END
> > > >
> > > > this way i can remain consistent with the business days defined in my
> > > > calendar table. You know...I don't send a blank report on christmas day.
> > > > Is there any reason why i cannot put the same condition into the RS-created
> > > > SQL Agent jobs ?
> >
> > -- Lynn
> >
> >
> > "William" wrote:
> >
> > > NoRows is a property of the report objects and is in most object types
> > > (Table, List, etc) except text,image and rectangle.
> > >
> > > "Lynn" wrote:
> > >
> > > > the hitchiker's guide to RS says it would seem i can set the NoRows property
> > > > to just give a message if/when the report returns no data. i'd prefer
> > > > nothing was sent, but this will do for now. but can anybody tell me where
> > > > that property is set?
> > > > -- Lynn
> > > >
> > > >
> > > > "Lynn" wrote:
> > > >
> > > > > I need to know if there's any way to refrain from sending an email if the
> > > > > tSQL behind the rdl is empty? it's a trading report, clearing-type
> > > > > information is sent out in a report via reporting services. i do not want to
> > > > > send a blank email if no trades exist. but i must have it in
> > > > > place/enabled/scheduled, such that the report does capture trades if/when
> > > > > they occur. i've got the report and subscription created, i just now
> > > > > received a blank email. i cannot do that once this is deployed, which needs
> > > > > to happen asap
> > > > >
> > > > > similarly, and slightly less urgent, can i add business day logic to the sql
> > > > > agent job? i use this piece in many of my sql agent jobs:
> > > > >
> > > > > SET DATEFIRST 7
> > > > > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > > > > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > > > > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > > > > BEGIN
> > > > > ...whatever...
> > > > > END
> > > > >
> > > > > this way i can remain consistent with the business days defined in my
> > > > > calendar table. You know...I don't send a blank report on christmas day.
> > > > > Is there any reason why i cannot put the same condition into the RS-created
> > > > > SQL Agent jobs ?
> > > > >
> > > > > I worry about this because I am still a bit of an RS newbie. I've several
> > > > > reports working beautifully...but, I also have one report with multiple
> > > > > subscriptions -- 4 email notices daily -- for some reason, the first one of
> > > > > the day always sends a dupe. two identical emails...the other 3
> > > > > subscriptions work just fine.
> > > > >
> > > > > So, I'm just a little hesitant to change what I get once the subscription is
> > > > > created.
> > > > >
> > > > > Is anyone able to provide some guidance with these things? The blank report
> > > > > thing is very urgent, everything else I have some time on.
> > > > >
> > > > > -- Lynn|||What you should do is in the stored procedure that supplies the emails is to
actually check the data to see if there is any to return. If there isn't,
don't supply any emails - then you will not get any emails that have no
data.
=-Chris
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
>I need to know if there's any way to refrain from sending an email if the
> tSQL behind the rdl is empty? it's a trading report, clearing-type
> information is sent out in a report via reporting services. i do not want
> to
> send a blank email if no trades exist. but i must have it in
> place/enabled/scheduled, such that the report does capture trades if/when
> they occur. i've got the report and subscription created, i just now
> received a blank email. i cannot do that once this is deployed, which
> needs
> to happen asap
> similarly, and slightly less urgent, can i add business day logic to the
> sql
> agent job? i use this piece in many of my sql agent jobs:
> SET DATEFIRST 7
> IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> BEGIN
> ...whatever...
> END
> this way i can remain consistent with the business days defined in my
> calendar table. You know...I don't send a blank report on christmas day.
> Is there any reason why i cannot put the same condition into the
> RS-created
> SQL Agent jobs ?
> I worry about this because I am still a bit of an RS newbie. I've several
> reports working beautifully...but, I also have one report with multiple
> subscriptions -- 4 email notices daily -- for some reason, the first one
> of
> the day always sends a dupe. two identical emails...the other 3
> subscriptions work just fine.
> So, I'm just a little hesitant to change what I get once the subscription
> is
> created.
> Is anyone able to provide some guidance with these things? The blank
> report
> thing is very urgent, everything else I have some time on.
> -- Lynn|||Interesting timing, Chris. I actually just now did it. I think it worked.
I changed this:
exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
@.EventData='8141add6-a259-484c-9416-63321181686b'
to this:
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt =CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN
exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
@.EventData='8141add6-a259-484c-9416-63321181686b'
END
today's DATEPART is 3, so i figured this would pretty much make it invalid
sql agent says it ran, my job log says this:
Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing 2006-11-14
12:15:00
not much, yes, i know
in report manager, the subscription page, Last Run is blank. no
date...so...kinda like the agent job ran, but it didn't send the subscribed
email
-- Lynn
"Chris Conner" wrote:
> What you should do is in the stored procedure that supplies the emails is to
> actually check the data to see if there is any to return. If there isn't,
> don't supply any emails - then you will not get any emails that have no
> data.
> =-Chris
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
> >I need to know if there's any way to refrain from sending an email if the
> > tSQL behind the rdl is empty? it's a trading report, clearing-type
> > information is sent out in a report via reporting services. i do not want
> > to
> > send a blank email if no trades exist. but i must have it in
> > place/enabled/scheduled, such that the report does capture trades if/when
> > they occur. i've got the report and subscription created, i just now
> > received a blank email. i cannot do that once this is deployed, which
> > needs
> > to happen asap
> >
> > similarly, and slightly less urgent, can i add business day logic to the
> > sql
> > agent job? i use this piece in many of my sql agent jobs:
> >
> > SET DATEFIRST 7
> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> > BEGIN
> > ...whatever...
> > END
> >
> > this way i can remain consistent with the business days defined in my
> > calendar table. You know...I don't send a blank report on christmas day.
> > Is there any reason why i cannot put the same condition into the
> > RS-created
> > SQL Agent jobs ?
> >
> > I worry about this because I am still a bit of an RS newbie. I've several
> > reports working beautifully...but, I also have one report with multiple
> > subscriptions -- 4 email notices daily -- for some reason, the first one
> > of
> > the day always sends a dupe. two identical emails...the other 3
> > subscriptions work just fine.
> >
> > So, I'm just a little hesitant to change what I get once the subscription
> > is
> > created.
> >
> > Is anyone able to provide some guidance with these things? The blank
> > report
> > thing is very urgent, everything else I have some time on.
> >
> > -- Lynn
>
>|||Interesting...
So you actually created the job or did you modify the job the subscription
created when you created the subscription itself?
=-Chris
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
> Interesting timing, Chris. I actually just now did it. I think it
> worked.
> I changed this:
> exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> @.EventData='8141add6-a259-484c-9416-63321181686b'
> to this:
> SET DATEFIRST 7
> IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
> AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt => CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
> BEGIN
> exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> @.EventData='8141add6-a259-484c-9416-63321181686b'
> END
> today's DATEPART is 3, so i figured this would pretty much make it invalid
> sql agent says it ran, my job log says this:
> Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
> 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing 2006-11-14
> 12:15:00
> not much, yes, i know
> in report manager, the subscription page, Last Run is blank. no
> date...so...kinda like the agent job ran, but it didn't send the
> subscribed
> email
> -- Lynn
>
> "Chris Conner" wrote:
>> What you should do is in the stored procedure that supplies the emails is
>> to
>> actually check the data to see if there is any to return. If there isn't,
>> don't supply any emails - then you will not get any emails that have no
>> data.
>> =-Chris
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
>> >I need to know if there's any way to refrain from sending an email if
>> >the
>> > tSQL behind the rdl is empty? it's a trading report, clearing-type
>> > information is sent out in a report via reporting services. i do not
>> > want
>> > to
>> > send a blank email if no trades exist. but i must have it in
>> > place/enabled/scheduled, such that the report does capture trades
>> > if/when
>> > they occur. i've got the report and subscription created, i just now
>> > received a blank email. i cannot do that once this is deployed, which
>> > needs
>> > to happen asap
>> >
>> > similarly, and slightly less urgent, can i add business day logic to
>> > the
>> > sql
>> > agent job? i use this piece in many of my sql agent jobs:
>> >
>> > SET DATEFIRST 7
>> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
>> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt =>> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
>> > BEGIN
>> > ...whatever...
>> > END
>> >
>> > this way i can remain consistent with the business days defined in my
>> > calendar table. You know...I don't send a blank report on christmas
>> > day.
>> > Is there any reason why i cannot put the same condition into the
>> > RS-created
>> > SQL Agent jobs ?
>> >
>> > I worry about this because I am still a bit of an RS newbie. I've
>> > several
>> > reports working beautifully...but, I also have one report with multiple
>> > subscriptions -- 4 email notices daily -- for some reason, the first
>> > one
>> > of
>> > the day always sends a dupe. two identical emails...the other 3
>> > subscriptions work just fine.
>> >
>> > So, I'm just a little hesitant to change what I get once the
>> > subscription
>> > is
>> > created.
>> >
>> > Is anyone able to provide some guidance with these things? The blank
>> > report
>> > thing is very urgent, everything else I have some time on.
>> >
>> > -- Lynn
>>|||i modified the job created by the subscription. i was paranoid to do that
cuz those darned subscriptions are so hyper-active...but, it worked.
-- Lynn
"Chris Conner" wrote:
> Interesting...
> So you actually created the job or did you modify the job the subscription
> created when you created the subscription itself?
> =-Chris
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
> > Interesting timing, Chris. I actually just now did it. I think it
> > worked.
> > I changed this:
> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> >
> > to this:
> > SET DATEFIRST 7
> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt => > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
> > BEGIN
> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> > END
> >
> > today's DATEPART is 3, so i figured this would pretty much make it invalid
> > sql agent says it ran, my job log says this:
> > Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
> > 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing 2006-11-14
> > 12:15:00
> >
> > not much, yes, i know
> > in report manager, the subscription page, Last Run is blank. no
> > date...so...kinda like the agent job ran, but it didn't send the
> > subscribed
> > email
> >
> > -- Lynn
> >
> >
> > "Chris Conner" wrote:
> >
> >> What you should do is in the stored procedure that supplies the emails is
> >> to
> >> actually check the data to see if there is any to return. If there isn't,
> >> don't supply any emails - then you will not get any emails that have no
> >> data.
> >>
> >> =-Chris
> >>
> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
> >> >I need to know if there's any way to refrain from sending an email if
> >> >the
> >> > tSQL behind the rdl is empty? it's a trading report, clearing-type
> >> > information is sent out in a report via reporting services. i do not
> >> > want
> >> > to
> >> > send a blank email if no trades exist. but i must have it in
> >> > place/enabled/scheduled, such that the report does capture trades
> >> > if/when
> >> > they occur. i've got the report and subscription created, i just now
> >> > received a blank email. i cannot do that once this is deployed, which
> >> > needs
> >> > to happen asap
> >> >
> >> > similarly, and slightly less urgent, can i add business day logic to
> >> > the
> >> > sql
> >> > agent job? i use this piece in many of my sql agent jobs:
> >> >
> >> > SET DATEFIRST 7
> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> >> > BEGIN
> >> > ...whatever...
> >> > END
> >> >
> >> > this way i can remain consistent with the business days defined in my
> >> > calendar table. You know...I don't send a blank report on christmas
> >> > day.
> >> > Is there any reason why i cannot put the same condition into the
> >> > RS-created
> >> > SQL Agent jobs ?
> >> >
> >> > I worry about this because I am still a bit of an RS newbie. I've
> >> > several
> >> > reports working beautifully...but, I also have one report with multiple
> >> > subscriptions -- 4 email notices daily -- for some reason, the first
> >> > one
> >> > of
> >> > the day always sends a dupe. two identical emails...the other 3
> >> > subscriptions work just fine.
> >> >
> >> > So, I'm just a little hesitant to change what I get once the
> >> > subscription
> >> > is
> >> > created.
> >> >
> >> > Is anyone able to provide some guidance with these things? The blank
> >> > report
> >> > thing is very urgent, everything else I have some time on.
> >> >
> >> > -- Lynn
> >>
> >>
> >>
>
>|||LOL - well I was thinking that if you modified the stored procedure that
called by the data-driven subscription, then next time you have to modify
the subscription, you will not have to do anything in the job itself, like
you do now.
=-Chris
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:901A249A-45E0-43E0-B76F-CCCE4555970E@.microsoft.com...
>i modified the job created by the subscription. i was paranoid to do that
> cuz those darned subscriptions are so hyper-active...but, it worked.
> -- Lynn
>
> "Chris Conner" wrote:
>> Interesting...
>> So you actually created the job or did you modify the job the
>> subscription
>> created when you created the subscription itself?
>> =-Chris
>>
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
>> > Interesting timing, Chris. I actually just now did it. I think it
>> > worked.
>> > I changed this:
>> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
>> > @.EventData='8141add6-a259-484c-9416-63321181686b'
>> >
>> > to this:
>> > SET DATEFIRST 7
>> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
>> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt =>> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
>> > BEGIN
>> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
>> > @.EventData='8141add6-a259-484c-9416-63321181686b'
>> > END
>> >
>> > today's DATEPART is 3, so i figured this would pretty much make it
>> > invalid
>> > sql agent says it ran, my job log says this:
>> > Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
>> > 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing
>> > 2006-11-14
>> > 12:15:00
>> >
>> > not much, yes, i know
>> > in report manager, the subscription page, Last Run is blank. no
>> > date...so...kinda like the agent job ran, but it didn't send the
>> > subscribed
>> > email
>> >
>> > -- Lynn
>> >
>> >
>> > "Chris Conner" wrote:
>> >
>> >> What you should do is in the stored procedure that supplies the emails
>> >> is
>> >> to
>> >> actually check the data to see if there is any to return. If there
>> >> isn't,
>> >> don't supply any emails - then you will not get any emails that have
>> >> no
>> >> data.
>> >>
>> >> =-Chris
>> >>
>> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> >> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
>> >> >I need to know if there's any way to refrain from sending an email if
>> >> >the
>> >> > tSQL behind the rdl is empty? it's a trading report, clearing-type
>> >> > information is sent out in a report via reporting services. i do
>> >> > not
>> >> > want
>> >> > to
>> >> > send a blank email if no trades exist. but i must have it in
>> >> > place/enabled/scheduled, such that the report does capture trades
>> >> > if/when
>> >> > they occur. i've got the report and subscription created, i just
>> >> > now
>> >> > received a blank email. i cannot do that once this is deployed,
>> >> > which
>> >> > needs
>> >> > to happen asap
>> >> >
>> >> > similarly, and slightly less urgent, can i add business day logic to
>> >> > the
>> >> > sql
>> >> > agent job? i use this piece in many of my sql agent jobs:
>> >> >
>> >> > SET DATEFIRST 7
>> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
>> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt =>> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
>> >> > BEGIN
>> >> > ...whatever...
>> >> > END
>> >> >
>> >> > this way i can remain consistent with the business days defined in
>> >> > my
>> >> > calendar table. You know...I don't send a blank report on christmas
>> >> > day.
>> >> > Is there any reason why i cannot put the same condition into the
>> >> > RS-created
>> >> > SQL Agent jobs ?
>> >> >
>> >> > I worry about this because I am still a bit of an RS newbie. I've
>> >> > several
>> >> > reports working beautifully...but, I also have one report with
>> >> > multiple
>> >> > subscriptions -- 4 email notices daily -- for some reason, the first
>> >> > one
>> >> > of
>> >> > the day always sends a dupe. two identical emails...the other 3
>> >> > subscriptions work just fine.
>> >> >
>> >> > So, I'm just a little hesitant to change what I get once the
>> >> > subscription
>> >> > is
>> >> > created.
>> >> >
>> >> > Is anyone able to provide some guidance with these things? The
>> >> > blank
>> >> > report
>> >> > thing is very urgent, everything else I have some time on.
>> >> >
>> >> > -- Lynn
>> >>
>> >>
>> >>
>>|||makes sense, but it's not a data-driven subscription at all. in fact, i
don't have any of those. can you let me know why i might want to do that?
convert a standard subscription to data driven?
-- Lynn
"Chris Conner" wrote:
> LOL - well I was thinking that if you modified the stored procedure that
> called by the data-driven subscription, then next time you have to modify
> the subscription, you will not have to do anything in the job itself, like
> you do now.
> =-Chris
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:901A249A-45E0-43E0-B76F-CCCE4555970E@.microsoft.com...
> >i modified the job created by the subscription. i was paranoid to do that
> > cuz those darned subscriptions are so hyper-active...but, it worked.
> > -- Lynn
> >
> >
> > "Chris Conner" wrote:
> >
> >> Interesting...
> >>
> >> So you actually created the job or did you modify the job the
> >> subscription
> >> created when you created the subscription itself?
> >>
> >> =-Chris
> >>
> >>
> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
> >> > Interesting timing, Chris. I actually just now did it. I think it
> >> > worked.
> >> > I changed this:
> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> >> >
> >> > to this:
> >> > SET DATEFIRST 7
> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt => >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
> >> > BEGIN
> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> >> > END
> >> >
> >> > today's DATEPART is 3, so i figured this would pretty much make it
> >> > invalid
> >> > sql agent says it ran, my job log says this:
> >> > Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
> >> > 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing
> >> > 2006-11-14
> >> > 12:15:00
> >> >
> >> > not much, yes, i know
> >> > in report manager, the subscription page, Last Run is blank. no
> >> > date...so...kinda like the agent job ran, but it didn't send the
> >> > subscribed
> >> > email
> >> >
> >> > -- Lynn
> >> >
> >> >
> >> > "Chris Conner" wrote:
> >> >
> >> >> What you should do is in the stored procedure that supplies the emails
> >> >> is
> >> >> to
> >> >> actually check the data to see if there is any to return. If there
> >> >> isn't,
> >> >> don't supply any emails - then you will not get any emails that have
> >> >> no
> >> >> data.
> >> >>
> >> >> =-Chris
> >> >>
> >> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> >> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
> >> >> >I need to know if there's any way to refrain from sending an email if
> >> >> >the
> >> >> > tSQL behind the rdl is empty? it's a trading report, clearing-type
> >> >> > information is sent out in a report via reporting services. i do
> >> >> > not
> >> >> > want
> >> >> > to
> >> >> > send a blank email if no trades exist. but i must have it in
> >> >> > place/enabled/scheduled, such that the report does capture trades
> >> >> > if/when
> >> >> > they occur. i've got the report and subscription created, i just
> >> >> > now
> >> >> > received a blank email. i cannot do that once this is deployed,
> >> >> > which
> >> >> > needs
> >> >> > to happen asap
> >> >> >
> >> >> > similarly, and slightly less urgent, can i add business day logic to
> >> >> > the
> >> >> > sql
> >> >> > agent job? i use this piece in many of my sql agent jobs:
> >> >> >
> >> >> > SET DATEFIRST 7
> >> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> >> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt => >> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> >> >> > BEGIN
> >> >> > ...whatever...
> >> >> > END
> >> >> >
> >> >> > this way i can remain consistent with the business days defined in
> >> >> > my
> >> >> > calendar table. You know...I don't send a blank report on christmas
> >> >> > day.
> >> >> > Is there any reason why i cannot put the same condition into the
> >> >> > RS-created
> >> >> > SQL Agent jobs ?
> >> >> >
> >> >> > I worry about this because I am still a bit of an RS newbie. I've
> >> >> > several
> >> >> > reports working beautifully...but, I also have one report with
> >> >> > multiple
> >> >> > subscriptions -- 4 email notices daily -- for some reason, the first
> >> >> > one
> >> >> > of
> >> >> > the day always sends a dupe. two identical emails...the other 3
> >> >> > subscriptions work just fine.
> >> >> >
> >> >> > So, I'm just a little hesitant to change what I get once the
> >> >> > subscription
> >> >> > is
> >> >> > created.
> >> >> >
> >> >> > Is anyone able to provide some guidance with these things? The
> >> >> > blank
> >> >> > report
> >> >> > thing is very urgent, everything else I have some time on.
> >> >> >
> >> >> > -- Lynn
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well it fits this situation perfectly, you only want to email people when
your data meets certain criteria (hence the name "Data-Driven" subscription.
With a data-driven subscription, you can set parameters - as an example -
send your dept. head and finance a report when expenditures exceed a gross
revenue amount in a given month.
=-Chris
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4E6393AF-00CF-4A52-AB84-4F70D12B9B5A@.microsoft.com...
> makes sense, but it's not a data-driven subscription at all. in fact, i
> don't have any of those. can you let me know why i might want to do that?
> convert a standard subscription to data driven?
> -- Lynn
>
> "Chris Conner" wrote:
>> LOL - well I was thinking that if you modified the stored procedure that
>> called by the data-driven subscription, then next time you have to modify
>> the subscription, you will not have to do anything in the job itself,
>> like
>> you do now.
>> =-Chris
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:901A249A-45E0-43E0-B76F-CCCE4555970E@.microsoft.com...
>> >i modified the job created by the subscription. i was paranoid to do
>> >that
>> > cuz those darned subscriptions are so hyper-active...but, it worked.
>> > -- Lynn
>> >
>> >
>> > "Chris Conner" wrote:
>> >
>> >> Interesting...
>> >>
>> >> So you actually created the job or did you modify the job the
>> >> subscription
>> >> created when you created the subscription itself?
>> >>
>> >> =-Chris
>> >>
>> >>
>> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> >> news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
>> >> > Interesting timing, Chris. I actually just now did it. I think it
>> >> > worked.
>> >> > I changed this:
>> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
>> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
>> >> >
>> >> > to this:
>> >> > SET DATEFIRST 7
>> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
>> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt =>> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
>> >> > BEGIN
>> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
>> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
>> >> > END
>> >> >
>> >> > today's DATEPART is 3, so i figured this would pretty much make it
>> >> > invalid
>> >> > sql agent says it ran, my job log says this:
>> >> > Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
>> >> > 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing
>> >> > 2006-11-14
>> >> > 12:15:00
>> >> >
>> >> > not much, yes, i know
>> >> > in report manager, the subscription page, Last Run is blank. no
>> >> > date...so...kinda like the agent job ran, but it didn't send the
>> >> > subscribed
>> >> > email
>> >> >
>> >> > -- Lynn
>> >> >
>> >> >
>> >> > "Chris Conner" wrote:
>> >> >
>> >> >> What you should do is in the stored procedure that supplies the
>> >> >> emails
>> >> >> is
>> >> >> to
>> >> >> actually check the data to see if there is any to return. If there
>> >> >> isn't,
>> >> >> don't supply any emails - then you will not get any emails that
>> >> >> have
>> >> >> no
>> >> >> data.
>> >> >>
>> >> >> =-Chris
>> >> >>
>> >> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> >> >> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
>> >> >> >I need to know if there's any way to refrain from sending an email
>> >> >> >if
>> >> >> >the
>> >> >> > tSQL behind the rdl is empty? it's a trading report,
>> >> >> > clearing-type
>> >> >> > information is sent out in a report via reporting services. i do
>> >> >> > not
>> >> >> > want
>> >> >> > to
>> >> >> > send a blank email if no trades exist. but i must have it in
>> >> >> > place/enabled/scheduled, such that the report does capture trades
>> >> >> > if/when
>> >> >> > they occur. i've got the report and subscription created, i just
>> >> >> > now
>> >> >> > received a blank email. i cannot do that once this is deployed,
>> >> >> > which
>> >> >> > needs
>> >> >> > to happen asap
>> >> >> >
>> >> >> > similarly, and slightly less urgent, can i add business day logic
>> >> >> > to
>> >> >> > the
>> >> >> > sql
>> >> >> > agent job? i use this piece in many of my sql agent jobs:
>> >> >> >
>> >> >> > SET DATEFIRST 7
>> >> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
>> >> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt
>> >> >> > =>> >> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
>> >> >> > BEGIN
>> >> >> > ...whatever...
>> >> >> > END
>> >> >> >
>> >> >> > this way i can remain consistent with the business days defined
>> >> >> > in
>> >> >> > my
>> >> >> > calendar table. You know...I don't send a blank report on
>> >> >> > christmas
>> >> >> > day.
>> >> >> > Is there any reason why i cannot put the same condition into the
>> >> >> > RS-created
>> >> >> > SQL Agent jobs ?
>> >> >> >
>> >> >> > I worry about this because I am still a bit of an RS newbie.
>> >> >> > I've
>> >> >> > several
>> >> >> > reports working beautifully...but, I also have one report with
>> >> >> > multiple
>> >> >> > subscriptions -- 4 email notices daily -- for some reason, the
>> >> >> > first
>> >> >> > one
>> >> >> > of
>> >> >> > the day always sends a dupe. two identical emails...the other 3
>> >> >> > subscriptions work just fine.
>> >> >> >
>> >> >> > So, I'm just a little hesitant to change what I get once the
>> >> >> > subscription
>> >> >> > is
>> >> >> > created.
>> >> >> >
>> >> >> > Is anyone able to provide some guidance with these things? The
>> >> >> > blank
>> >> >> > report
>> >> >> > thing is very urgent, everything else I have some time on.
>> >> >> >
>> >> >> > -- Lynn
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Well, like I said, I haven't done any data-driven subscriptions before, I
guess I'm a little blind on this right now. But yes, it does seem to be
fitting a number of the areas in which I am reporting. I guess I need to try
it out. Hopefully I can get something together relatively easily. Thank you
again, Chris, for you input. I do appreciate it.
-- Lynn
"Chris Conner" wrote:
> Well it fits this situation perfectly, you only want to email people when
> your data meets certain criteria (hence the name "Data-Driven" subscription.
> With a data-driven subscription, you can set parameters - as an example -
> send your dept. head and finance a report when expenditures exceed a gross
> revenue amount in a given month.
> =-Chris
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:4E6393AF-00CF-4A52-AB84-4F70D12B9B5A@.microsoft.com...
> > makes sense, but it's not a data-driven subscription at all. in fact, i
> > don't have any of those. can you let me know why i might want to do that?
> > convert a standard subscription to data driven?
> > -- Lynn
> >
> >
> > "Chris Conner" wrote:
> >
> >> LOL - well I was thinking that if you modified the stored procedure that
> >> called by the data-driven subscription, then next time you have to modify
> >> the subscription, you will not have to do anything in the job itself,
> >> like
> >> you do now.
> >>
> >> =-Chris
> >>
> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> news:901A249A-45E0-43E0-B76F-CCCE4555970E@.microsoft.com...
> >> >i modified the job created by the subscription. i was paranoid to do
> >> >that
> >> > cuz those darned subscriptions are so hyper-active...but, it worked.
> >> > -- Lynn
> >> >
> >> >
> >> > "Chris Conner" wrote:
> >> >
> >> >> Interesting...
> >> >>
> >> >> So you actually created the job or did you modify the job the
> >> >> subscription
> >> >> created when you created the subscription itself?
> >> >>
> >> >> =-Chris
> >> >>
> >> >>
> >> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> >> news:A0D86D56-889A-4633-92FA-4182392D6035@.microsoft.com...
> >> >> > Interesting timing, Chris. I actually just now did it. I think it
> >> >> > worked.
> >> >> > I changed this:
> >> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> >> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> >> >> >
> >> >> > to this:
> >> >> > SET DATEFIRST 7
> >> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 4 AND 6
> >> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt => >> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
> >> >> > BEGIN
> >> >> > exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
> >> >> > @.EventData='8141add6-a259-484c-9416-63321181686b'
> >> >> > END
> >> >> >
> >> >> > today's DATEPART is 3, so i figured this would pretty much make it
> >> >> > invalid
> >> >> > sql agent says it ran, my job log says this:
> >> >> > Job 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4' : Step 1,
> >> >> > 'FF8CB8A0-8BA4-4D1D-B94E-8A2D7562BDB4_step_1' : Began Executing
> >> >> > 2006-11-14
> >> >> > 12:15:00
> >> >> >
> >> >> > not much, yes, i know
> >> >> > in report manager, the subscription page, Last Run is blank. no
> >> >> > date...so...kinda like the agent job ran, but it didn't send the
> >> >> > subscribed
> >> >> > email
> >> >> >
> >> >> > -- Lynn
> >> >> >
> >> >> >
> >> >> > "Chris Conner" wrote:
> >> >> >
> >> >> >> What you should do is in the stored procedure that supplies the
> >> >> >> emails
> >> >> >> is
> >> >> >> to
> >> >> >> actually check the data to see if there is any to return. If there
> >> >> >> isn't,
> >> >> >> don't supply any emails - then you will not get any emails that
> >> >> >> have
> >> >> >> no
> >> >> >> data.
> >> >> >>
> >> >> >> =-Chris
> >> >> >>
> >> >> >> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> >> >> >> news:41132819-B36C-4978-A558-8622FF18933E@.microsoft.com...
> >> >> >> >I need to know if there's any way to refrain from sending an email
> >> >> >> >if
> >> >> >> >the
> >> >> >> > tSQL behind the rdl is empty? it's a trading report,
> >> >> >> > clearing-type
> >> >> >> > information is sent out in a report via reporting services. i do
> >> >> >> > not
> >> >> >> > want
> >> >> >> > to
> >> >> >> > send a blank email if no trades exist. but i must have it in
> >> >> >> > place/enabled/scheduled, such that the report does capture trades
> >> >> >> > if/when
> >> >> >> > they occur. i've got the report and subscription created, i just
> >> >> >> > now
> >> >> >> > received a blank email. i cannot do that once this is deployed,
> >> >> >> > which
> >> >> >> > needs
> >> >> >> > to happen asap
> >> >> >> >
> >> >> >> > similarly, and slightly less urgent, can i add business day logic
> >> >> >> > to
> >> >> >> > the
> >> >> >> > sql
> >> >> >> > agent job? i use this piece in many of my sql agent jobs:
> >> >> >> >
> >> >> >> > SET DATEFIRST 7
> >> >> >> > IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
> >> >> >> > AND NOT EXISTS (SELECT 1 FROM database.dbo.calendartable WHERE dt
> >> >> >> > => >> >> >> > CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)a
> >> >> >> > BEGIN
> >> >> >> > ...whatever...
> >> >> >> > END
> >> >> >> >
> >> >> >> > this way i can remain consistent with the business days defined
> >> >> >> > in
> >> >> >> > my
> >> >> >> > calendar table. You know...I don't send a blank report on
> >> >> >> > christmas
> >> >> >> > day.
> >> >> >> > Is there any reason why i cannot put the same condition into the
> >> >> >> > RS-created
> >> >> >> > SQL Agent jobs ?
> >> >> >> >
> >> >> >> > I worry about this because I am still a bit of an RS newbie.
> >> >> >> > I've
> >> >> >> > several
> >> >> >> > reports working beautifully...but, I also have one report with
> >> >> >> > multiple
> >> >> >> > subscriptions -- 4 email notices daily -- for some reason, the
> >> >> >> > first
> >> >> >> > one
> >> >> >> > of
> >> >> >> > the day always sends a dupe. two identical emails...the other 3
> >> >> >> > subscriptions work just fine.
> >> >> >> >
> >> >> >> > So, I'm just a little hesitant to change what I get once the
> >> >> >> > subscription
> >> >> >> > is
> >> >> >> > created.
> >> >> >> >
> >> >> >> > Is anyone able to provide some guidance with these things? The
> >> >> >> > blank
> >> >> >> > report
> >> >> >> > thing is very urgent, everything else I have some time on.
> >> >> >> >
> >> >> >> > -- Lynn
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>