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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Conditional formatting for missing numeric data

I created a matrix report which includes numeric data to be formatted as
currency. In cases where the data is missing, the empty cell displays "$".
If I use the iif() function for conditional formatting, it inserts the entire
expression into all of the cells. Does anyone know how I can format the
cells that do not contain any data so that nothing will be displayed?
thanks!It sounds like you need Nothing:
=iif(Fields!MyValue.Value Is Nothing,"","C0")
Put the expression in the Custom field in the Textbox Properties window.
I think you can use "". If not, choose something else.
"Anna" wrote:
> I created a matrix report which includes numeric data to be formatted as
> currency. In cases where the data is missing, the empty cell displays "$".
> If I use the iif() function for conditional formatting, it inserts the entire
> expression into all of the cells. Does anyone know how I can format the
> cells that do not contain any data so that nothing will be displayed?
> thanks!

Concatination of empty field and removal of "" symbols

Hello...

I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:

"ABC NUTRITIONAL PRODUCTS","550","","","N","FAIR OAKS","","","COLORADO SPRINGS","C0","","","","","","","","A","","",""

My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:

"550""""N""FAIR OAKS"""

I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!

Thank you...

SSIS has a powerful expression language that allows you to carry out many operations, including text manipulation.

In your case, you can use this expression language in a Derived Column Transform.

-Jamie

Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or

Lately there seems to be quite a few posts regarding the
concatenating of column data in sql.Faq2559,written by a very
knowledgable MVP,is often cited as a reference for this
problem's solution.This problem is interesting as it has
spurred debate on a number of issues including client vs. server,
static vs. dynamic sql and the nature of the sql solutions.
Perhaps there is even more of interest here between the code.
For example, the author starts out with the following:

>This question is asked quite frequently. People want to take a column like
>this:
Color
--
red
orange
blue
green
And return a resultset like this:
Colors
--
>red,orange,blue,green
by stating:
>This isn't exactly relational...
Has anyone wondered exactly what 'isn't exactly relational' means?
I assume I'm safe in assuming he is referring to the resultset.
Which part is relational and which part is not relational?(Putting
aside the idea whether it even makes sense to refer to a resultset
as being 'relational').Is it the comma's that cause the problem?
What about :
1,100,100
which could be money?Has anyone said this is not exactly relational?
Or John's Pizza Hut
Is the apostrophe objectionable?
Nothing wrong with:
Lastname+','+Firstname
is there?
Or does the fuzzy part of relational come from the fact that the
result came from a single column?This is interesting since 2V logic,
whether a result is relational or quasi-relational,is predicated on
'where' the result came from.So we don't know if '1,100,100' is
relational or almost relational unless we know 'exactly' where it
came from.What if we don't know where it came from?Is the result
null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
that a 'pure' sql solution to concatenation 'would never destroy 1NF
by building a list structure..'.Sounds real good but it is Bizarre!
Perhaps he's suggesting a new type of check constraint.The 99.9%
ivory soap constraint:)
Anyone on what this phrase means?
Here's another one.The author states:
'SQL Server 2005 adds some interesting options for this kind of
non-relational
query.'
What is a 'non-relational' query?Is it a query that produces a result
that isn't exactly relational?Is it a query that some dislike?You can
see where this is going:)
(Note that another MVP referred to the xml solution as a 'set based
solution'.
but that is a subject for another thread:)
Anyone on what a non-relational query is?
It's obviously easier to code this stuff than it is to talk about it.
Which would explain why sql remains such a mystery to so many.Be that
as it may, to try to get some clarity in all the haze is much more
interesting,challenging and fun than coding it.You just gotta love
the glass:)
(The author of the faq is to be recognized for his great contributions
and assistance to the server community.His words have impact and the
bar is set high in such a case).
musings from
www.rac4sql.netHi
In my opinion the list of contacanation values ( if you want to store it in
the table) would destroy 1NF
However , you can generate a report using this technique but it would be
better if we build such reports on the client side .
One reason is that you cannot guarantee the ordering of the result set

> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
When you design the database you would like to have two separated colums
fo Firstname and Lastname as well.
In result set as a report you may contacanate these columns depends upon
requirements
I have my doubt i have answered your question , so It is my opinion only
"05ponyGT" <noname@.overwood.com> wrote in message
news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Lately there seems to be quite a few posts regarding the
> concatenating of column data in sql.Faq2559,written by a very
> knowledgable MVP,is often cited as a reference for this
> problem's solution.This problem is interesting as it has
> spurred debate on a number of issues including client vs. server,
> static vs. dynamic sql and the nature of the sql solutions.
> Perhaps there is even more of interest here between the code.
> For example, the author starts out with the following:
>
> Color
> --
> red
> orange
> blue
> green
> And return a resultset like this:
> Colors
> --
> by stating:
> Has anyone wondered exactly what 'isn't exactly relational' means?
> I assume I'm safe in assuming he is referring to the resultset.
> Which part is relational and which part is not relational?(Putting
> aside the idea whether it even makes sense to refer to a resultset
> as being 'relational').Is it the comma's that cause the problem?
> What about :
> 1,100,100
> which could be money?Has anyone said this is not exactly relational?
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
> Or does the fuzzy part of relational come from the fact that the
> result came from a single column?This is interesting since 2V logic,
> whether a result is relational or quasi-relational,is predicated on
> 'where' the result came from.So we don't know if '1,100,100' is
> relational or almost relational unless we know 'exactly' where it
> came from.What if we don't know where it came from?Is the result
> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
> that a 'pure' sql solution to concatenation 'would never destroy 1NF
> by building a list structure..'.Sounds real good but it is Bizarre!
> Perhaps he's suggesting a new type of check constraint.The 99.9%
> ivory soap constraint:)
> Anyone on what this phrase means?
> Here's another one.The author states:
> 'SQL Server 2005 adds some interesting options for this kind of
> non-relational
> query.'
> What is a 'non-relational' query?Is it a query that produces a result
> that isn't exactly relational?Is it a query that some dislike?You can
> see where this is going:)
> (Note that another MVP referred to the xml solution as a 'set based
> solution'.
> but that is a subject for another thread:)
> Anyone on what a non-relational query is?
> It's obviously easier to code this stuff than it is to talk about it.
> Which would explain why sql remains such a mystery to so many.Be that
> as it may, to try to get some clarity in all the haze is much more
> interesting,challenging and fun than coding it.You just gotta love
> the glass:)
> (The author of the faq is to be recognized for his great contributions
> and assistance to the server community.His words have impact and the
> bar is set high in such a case).
> musings from
> www.rac4sql.net
>
>
>|||Anybody wanting concatenation like that and is using SQL Server 2005 should
be using the FOR XML in there first instance, it scales, is fully documented
in books online and works really well.

> One reason is that you cannot guarantee the ordering of the result set
Yes you can in SQL Server 2005!
Use the FOR XML syntax...
declare @.colour table (
name nvarchar( 50 ) not null primary key
)
insert @.colour ( name ) values( 'Red' )
insert @.colour ( name ) values( 'Green' )
insert @.colour ( name ) values( 'Blue' )
select *
from @.colour
-- Now concatenated
select (
select name + ', ' as [text()]
from @.colour
order by name
for xml path( '' ) ) as colour_name_concatentated
-- More powerful grouping example
select distinct type,
( select name + ', ' as [text()]
from sys.objects s2
where s2.type = s1.type
order by s2.name desc
for xml path( '' ) ) as concatenated_names
from sys.objects s1
order by type desc
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODn2yrhLGHA.2912@.tk2msftngp13.phx.gbl...
> Hi
> In my opinion the list of contacanation values ( if you want to store it
> in the table) would destroy 1NF
> However , you can generate a report using this technique but it would be
> better if we build such reports on the client side .
> One reason is that you cannot guarantee the ordering of the result set
>
> When you design the database you would like to have two separated colums
> fo Firstname and Lastname as well.
> In result set as a report you may contacanate these columns depends upon
> requirements
> I have my doubt i have answered your question , so It is my opinion only
>
>
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Probably best reading this http://en.wikipedia.org/wiki/Relational_model, it
gives a good explanation.
As for the concatenation being non-relational, it depends on what you are
doing.
If 'red, green, blue' where stored in a description column does that then
mean that column is now non-relational?
My take is that just because we merge rows in this way does not break normal
form because the new data becomes something else, its no longer a set of
colours its a set of descriptors, probably for data export or for
scalability reasons so the client or middle tier doesn't have to do the
work.
Now consider the SQL in SQL Server 2005 that does concatenation...
select distinct type,
( select name + ', ' as [text()]
from sys.objects s2
where s2.type = s1.type
order by s2.name desc
for xml path( '' ) ) as concatenated_names
from sys.objects s1
order by type desc
This still yeilds a set of data, that set can be used in other queries,
inserted - whatever!
I hear arguments that this type of thing should be done in the client
application, imho thats just plain wrong now, the FOR XML extensions are
extremely powerful and scale extremely well, and the code is very succinct
and maintainable. It would take dozens of lines of code to do this client
side, which is one maintanence and development overhead, but the other
overhead is that you need to do it in each application that wants that data,
that might be ASP.NET in one instance, RS in another etc...
This leads me into the debate on where do you put business logic now, Jim
Gray has done some really good research on this and his take is that the SQL
Server (not the database because SQL Server 'the product' is more than just
a data store) is becoming the central place to hold logic that can be shared
via multitudes of applications - it saves you coding a middle tier and
having all that infrastructure. SQL Server can be a web service now, has CLR
etc... The biggest single thing that people will need to change their
thinking on is should SQL Server be exposed that close to the internet say,
there are ways to proxy stuff - but its a discussion.
Anyway, I'm rambling completely off topic.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <noname@.overwood.com> wrote in message
news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Lately there seems to be quite a few posts regarding the
> concatenating of column data in sql.Faq2559,written by a very
> knowledgable MVP,is often cited as a reference for this
> problem's solution.This problem is interesting as it has
> spurred debate on a number of issues including client vs. server,
> static vs. dynamic sql and the nature of the sql solutions.
> Perhaps there is even more of interest here between the code.
> For example, the author starts out with the following:
>
> Color
> --
> red
> orange
> blue
> green
> And return a resultset like this:
> Colors
> --
> by stating:
> Has anyone wondered exactly what 'isn't exactly relational' means?
> I assume I'm safe in assuming he is referring to the resultset.
> Which part is relational and which part is not relational?(Putting
> aside the idea whether it even makes sense to refer to a resultset
> as being 'relational').Is it the comma's that cause the problem?
> What about :
> 1,100,100
> which could be money?Has anyone said this is not exactly relational?
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
> Or does the fuzzy part of relational come from the fact that the
> result came from a single column?This is interesting since 2V logic,
> whether a result is relational or quasi-relational,is predicated on
> 'where' the result came from.So we don't know if '1,100,100' is
> relational or almost relational unless we know 'exactly' where it
> came from.What if we don't know where it came from?Is the result
> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
> that a 'pure' sql solution to concatenation 'would never destroy 1NF
> by building a list structure..'.Sounds real good but it is Bizarre!
> Perhaps he's suggesting a new type of check constraint.The 99.9%
> ivory soap constraint:)
> Anyone on what this phrase means?
> Here's another one.The author states:
> 'SQL Server 2005 adds some interesting options for this kind of
> non-relational
> query.'
> What is a 'non-relational' query?Is it a query that produces a result
> that isn't exactly relational?Is it a query that some dislike?You can
> see where this is going:)
> (Note that another MVP referred to the xml solution as a 'set based
> solution'.
> but that is a subject for another thread:)
> Anyone on what a non-relational query is?
> It's obviously easier to code this stuff than it is to talk about it.
> Which would explain why sql remains such a mystery to so many.Be that
> as it may, to try to get some clarity in all the haze is much more
> interesting,challenging and fun than coding it.You just gotta love
> the glass:)
> (The author of the faq is to be recognized for his great contributions
> and assistance to the server community.His words have impact and the
> bar is set high in such a case).
> musings from
> www.rac4sql.net
>
>
>|||Could it be that in IT 'I code therefore I am' has replaced 'I think
therefore I am':)
We need more knowledgable people 'rambling' about the things we often
take for granted.I hope you ramble more for all our sakes:)
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uRLVZqiLGHA.500@.TK2MSFTNGP15.phx.gbl...
> Probably best reading this http://en.wikipedia.org/wiki/Relational_model,
> it gives a good explanation.
> As for the concatenation being non-relational, it depends on what you are
> doing.
> If 'red, green, blue' where stored in a description column does that then
> mean that column is now non-relational?
> My take is that just because we merge rows in this way does not break
> normal form because the new data becomes something else, its no longer a
> set of colours its a set of descriptors, probably for data export or for
> scalability reasons so the client or middle tier doesn't have to do the
> work.
> Now consider the SQL in SQL Server 2005 that does concatenation...
> select distinct type,
> ( select name + ', ' as [text()]
> from sys.objects s2
> where s2.type = s1.type
> order by s2.name desc
> for xml path( '' ) ) as concatenated_names
> from sys.objects s1
> order by type desc
> This still yeilds a set of data, that set can be used in other queries,
> inserted - whatever!
> I hear arguments that this type of thing should be done in the client
> application, imho thats just plain wrong now, the FOR XML extensions are
> extremely powerful and scale extremely well, and the code is very succinct
> and maintainable. It would take dozens of lines of code to do this client
> side, which is one maintanence and development overhead, but the other
> overhead is that you need to do it in each application that wants that
> data, that might be ASP.NET in one instance, RS in another etc...
> This leads me into the debate on where do you put business logic now, Jim
> Gray has done some really good research on this and his take is that the
> SQL Server (not the database because SQL Server 'the product' is more than
> just a data store) is becoming the central place to hold logic that can be
> shared via multitudes of applications - it saves you coding a middle tier
> and having all that infrastructure. SQL Server can be a web service now,
> has CLR etc... The biggest single thing that people will need to change
> their thinking on is should SQL Server be exposed that close to the
> internet say, there are ways to proxy stuff - but its a discussion.
> Anyway, I'm rambling completely off topic.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||It depends on how accurately you want to define the operation as relational.
In certain contexts, many general SQL operations are deemed relational while
in some other formal discourses they are not.
SQL's concept of types/domains is a bit strange. All it provides is exposed
representations using primitive built-in datatypes that are subject to
different user interpretations.
As we all know, a type is a defined set of values as associated operators on
those values -- this concept on associated operators is important. For
instance, consider some commonly understood types like HEIGHT, QUANTITY,
TEMPERATURE, DISTANCE etc. In a language like SQL that cannot adequately
support such types/domains, the user is forced to use built-in datatypes
like say NUMERIC or INTEGER. Thus, while multiplying values of TEMPERATURE
to that of HEIGHT is meaningless, a system where these are all represented
using INTEGER datatype would allow such multiplications.
Due to insufficient type support in such systems, the type specificity and
validity of such operations is often maintained and external to the DBMS;
and often subject to different interpretations.
With a resultset of concatenated values, it boils down to whether each
column value in the resultset is treated as a scalar or not. Technically,
all values in SQL are scalar; however the user interpretations of these
values are not. Thus the value "red,orange,blue,green", while a VARCHAR
value -- technically scalar in SQL -- might be interpreted by the user as a
list of 4 smaller strings that represent color values arranged in some
predetermined order.
( Another perspective is that concatenation of row values as a "relational"
operation relies on essential ordering of values which relational model
explicitly prohibits. Thus one could question the relevance of the results
being outside the realms of relational model. So should the results be
"red,orange,blue,green" or "orange,blue,red,green" or
"blue,green,red,orange" or something else? )
Coming to the questions in your post,
Something that is not exactly specific to theory of relational data
management.
Precisely because SQL cannot support anything beyond its built-in datatypes.
If SQL supported a list type then it wouldn't be an issue at all.
A query formulation that deviates from prescribed operations of relations,
generally in a semi-relational language like SQL. Some use the term for
query expressions that violate relational principles like data independence
as well.
Oh yeah, and I am sure all these are all Gr & Latin to you :-)
Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best one
I have seen so far on this topic.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OOla6coLGHA.3732@.TK2MSFTNGP10.phx.gbl..
>SQL's concept of types/domains is a bit strange. All it provides is exposed
>representations using primitive built-in datatypes that are subject to
>different user interpretations.

>Due to insufficient type support in such systems, the type specificity and
>validity of such operations is often maintained and external to the DBMS;
>and often subject to different interpretations.

>With a resultset of concatenated values, it boils down to whether each
>column value in the resultset is treated as a scalar or not.
Sorry for the dely:(
Thanks for sharing your thoughts.(Where is your blog?)
I believe we are on the save wavelength here.I choose to present the
ideas in,shall we say,a slightly different style:)I regret that I
didn't use the word 'intrepretation' explicitly although I would point
out that I 'strongly' implied it everywhere including the title:)
An 'sql' db invites intrepretation and the inevitable arguments that too
often confuse the apples from the pears.Is this the result of sql
being the 'silent' type instead of the stong type?
But users have to work with what they have so lets keep the fruit coming:)
One would think that what is written about a product has importance.
Especially what is written by the vendor.It appears that there is
stuff coming from MS that is nonsense but becomes sense by merely
being repeated often enough.Funny and :)But that is only my
'intrepretation':)
You can do and get anything you want at Alice's resturant:)
Least I be accused of any ulterior motive picking on concatenating,the
next bit of fun will be:
'Is it ok to return a line number or a rank or a...'
If anyone liked this discussion you will love this one:)

>Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best
one
I have seen so far on this topic.
Thanks and thanks.