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 Rollback

I have inherited a lot of SQL2005 TSQL, that has the following generic catch structure:

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

From BOL, @.@.TRANCOUNT returns the number of active transactions for the current connection and XACT_STATE()) is a scalar function that reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

My question is: Should the tests be OR'd rather than AND'd?

The line in question would then read:

IF @.@.TRANCOUNT > 0 OR (XACT_STATE()) <> 0

BOL says,

Both the XACT_STATE and @.@.TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @.@.TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

ie,

Instead of using the @.@.Trancount you can always check the XACT_STATE. Bcs @.@.TRANCOUNT never tells you wheather you have to do Commit or rollback. It only gives the number of transaction begin.

But

XACT_STATE = 0 no transaction active

XACT_STATE = 1 active transaction available & can be writable & commitable/rollbackable -- Commit/rollback

XACT_STATE = -1 active transaction available & uncommitable -- Rollbacked

|||

Thankyou Manivannan,

The TSQL in question runs after an error within the try section has been caught. My question relates as whether the functions should be OR'd instead of AND'd. Possibly only one of them should be used.

|||

Ok now, My direct answer is XACT_STATE is more enough to use....(trustable). Need not have or/and only XACT_STATE is enough to validate the condition.

|||

If I may summerise

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH
should become

BEGIN CATCH
IF (XACT_STATE()) <> 0 -- < Suggested simpler construction.
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

Is this your recommendation?

|||Yes...Exactly..

Concurrent snapshot processing

How can i check if i have concurrent snapshot processing enabled thru TSQL ?
I dont see an option to set it during the initial setup using the GUI
Hasan,
have a look at sp_helppublication (synchronization method). This can be set
after finishing the publication wizard, and before you set up a subscriber.
Rgds,
Paul Ibison

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.