Conditional update

Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished?

Here is my sproc:
CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
GO

Here is my app code:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

(1) Get the quantity into a variable and check if its > 0, only then update.

(2) You can use an OUTPUT parameter to return values back to the application. To retrieve the value returned through an OUTPUT parameter add the parameter to the collection and set its direction to OUTPUT. Check the second part ofthis articlefor some code.

CREATE PROCEDURE [webuser].[cssp_removeItem] (
@.lblID int
,@.result int OUTPUT )
AS

BEGIN
SET NOCOUNT ON

DECLARE @.qty int

SET @.result = 0

SELECT
@.qty = qtyonhand
FROM
cstb_inventory
WHERE
Id = @.lblID

IF @.qty > 0
BEGIN

UPDATE
cstb_inventory
SET
qtyonhand = qtyonhand -1
WHERE
Id = @.lblID
SET @.result = @.@.ROWCOUNT

END

SET NOCOUNT OFF
END
GO

|||

Thanks! that helped out a lot.

|||CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblIDAND qtyonhand>=1
GO

You can also use qtyonhand>0 if the field is an integer type.

|||

Thank you for responding. For those watching, this works.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue =qtyonhand
FROM cstb_inventory
where Id = @.lblID
GO

And this:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

cmd.Parameters.Add("@.newValue", SqlDbType.Int).Direction = ParameterDirection.Output

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

lblMessage.Text = cmd.Parameters("@.newValue").Value.ToString()

If lblMessage.Text = 0Then

lblMessage2.Text ="There are no more parts at this location"

EndIf

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

ProductGrid.DataBind()

EndTry

|||

Of course, if you had 1 before your call, you would still return the message that there are no more parts available, which I don't believe you want.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue=@.@.Rowcount
GO

I think is what you want, not only does it save you the database query, but it will return 1 if there was a part available, or 0 if qtyonhand was already 0.

|||Way to go Motley! Your code is a better solution.

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 logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.