Alert for any Job that Fails

This may be a silly question, but I am having trouble setting up an alert to
email an operator about any job that may fail for any reason. Is there an
easy way to set this up?
ThanksHi,
Take a look at this:
http://support.microsoft.com/defaul...kb;EN-US;315886
hth
DeeJay Puar
"Cooper" wrote:

> This may be a silly question, but I am having trouble setting up an alert
to
> email an operator about any job that may fail for any reason. Is there an
> easy way to set this up?
> Thanks|||Oh Ok.
I don't think there is a easy way to do that. I am using Reporting Services
as well and there is nothing in reporting services either.
However, at the risk of your jobs failing, you could query the 'sysjobs'
table in the msdb database and update the notifications (notify_*_* columns)
.
This should do the trick.
I have not tested this yet...lack of time, but give it a shot and post your
findings.
hth
DeeJay Puar
"Cooper" wrote:
[vbcol=seagreen]
> I'm sorry I should have stated a bit more than I did. I can make alerts ju
st
> fine. I just can't figure out how to specifically set one up to catch any
job
> that fails for any reason. I have many other alerts that are working and
> emailing me just fine. This particualr one I want to email me if any job
> fails for any reason. I know this can be done inside the job itself. But w
ith
> reporting services subscriptions are made that make jobs and I do not want
to
> check through all of them to set up the alert. Instead I want to make an
> alert that will email when any job fails for any reason without me having
to
> denote in the job itself to email me upon failure.
> "DeeJay Puar" wrote:
>sql

Alert for any Job that Fails

This may be a silly question, but I am having trouble setting up an alert to
email an operator about any job that may fail for any reason. Is there an
easy way to set this up?
Thanks
Hi,
Take a look at this:
http://support.microsoft.com/default...b;EN-US;315886
hth
DeeJay Puar
"Cooper" wrote:

> This may be a silly question, but I am having trouble setting up an alert to
> email an operator about any job that may fail for any reason. Is there an
> easy way to set this up?
> Thanks
|||Oh Ok.
I don't think there is a easy way to do that. I am using Reporting Services
as well and there is nothing in reporting services either.
However, at the risk of your jobs failing, you could query the 'sysjobs'
table in the msdb database and update the notifications (notify_*_* columns).
This should do the trick.
I have not tested this yet...lack of time, but give it a shot and post your
findings.
hth
DeeJay Puar
"Cooper" wrote:
[vbcol=seagreen]
> I'm sorry I should have stated a bit more than I did. I can make alerts just
> fine. I just can't figure out how to specifically set one up to catch any job
> that fails for any reason. I have many other alerts that are working and
> emailing me just fine. This particualr one I want to email me if any job
> fails for any reason. I know this can be done inside the job itself. But with
> reporting services subscriptions are made that make jobs and I do not want to
> check through all of them to set up the alert. Instead I want to make an
> alert that will email when any job fails for any reason without me having to
> denote in the job itself to email me upon failure.
> "DeeJay Puar" wrote:

Alert doesn't work on SQL 2005

Hi,
I have trouble getting the alerts to work on SQL 2005.
Below I define a custom message and a custom alert.
When I raise the error though the alert never fires.
Any ideas why that would be?
Thanks,
Patrick
----
USE master
GO
EXEC sp_addmessage 50001, 16, N'The version of SQL Server has been
requested.'
GO
---
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @.name=N'TEST Alert',
@.message_id=50001,
@.severity=0,
@.enabled=1,
@.delay_between_responses=5,
@.include_event_description_in=5,
@.notification_message=N'TEST',
@.category_name=N'[Uncategorized]',
@.job_id=N'00000000-0000-0000-0000-000000000000'
---
RAISERROR (50001, 16, 1)
---On sp_addmessage set @.with_log to true, the default is false.
Hope this helps,
Ben Nevarez
"Patrick" wrote:
> Hi,
> I have trouble getting the alerts to work on SQL 2005.
> Below I define a custom message and a custom alert.
> When I raise the error though the alert never fires.
> Any ideas why that would be?
> Thanks,
> Patrick
> ----
> USE master
> GO
> EXEC sp_addmessage 50001, 16, N'The version of SQL Server has been
> requested.'
> GO
> ---
> USE [msdb]
> GO
> EXEC msdb.dbo.sp_add_alert @.name=N'TEST Alert',
> @.message_id=50001,
> @.severity=0,
> @.enabled=1,
> @.delay_between_responses=5,
> @.include_event_description_in=5,
> @.notification_message=N'TEST',
> @.category_name=N'[Uncategorized]',
> @.job_id=N'00000000-0000-0000-0000-000000000000'
> ---
> RAISERROR (50001, 16, 1)
> ---
>|||On Jan 29, 4:06 pm, Ben Nevarez
<bneva...@.no.spam.please.sunamerica.com> wrote:
> On sp_addmessage set @.with_log to true, the default is false.
> Hope this helps,
> Ben Nevarez
Hi Ben,
thanks that worked. Maybe you would also know why none of my
replication alerts is working.
They are all set up but when I look on the history section of each
alert none of them has ever been called (even though we are using
replication :))
Thanks,
Patricksql

Alem

Hi freinds

I have a trouble plz help me

how to create table in SQL programming.

Thank you inadvance

Quote:

Originally Posted by alem

Hi freinds

I have a trouble plz help me

how to create table in SQL programming.

Thank you inadvance

Use the Qry Create table table name and fields field datatypesql

Aggregation advice

Hi, I am having some trouble with changing the default aggregation behavior of my cube. I need to be able to change the aggregation at different levels of a dimension.

I am currently exploring the possibility of using custom member formulas, placing MDX script into a column within my dimension meaning that I could define a custom roll up at each level in my hierarchy – is this the best way?

I have two Dimensions as part of a pivot table:
DIMTime = [Year].[Quarter].[Hour]
DIMSpatial = [Group_1].[Group_0].[Route].[Link]
And a calculated measure = SUM(x)/SUM(N)

When I ‘roll up’ (total) this is what I am currently getting:

DIM Time

Group_0 2006

1 0.9398

2 0.9471

Total 0.9424

I want to change the roll up to be 0.9398 + 0.9471 = 1.8869

Could anyone give me some guidance on how the MDX Script should look? I have been trying lots of combinations but keep getting a #Value! Error. I haven’t yet been able to implement a custom aggregation so if I am going down the wrong avenue or if you think there is a better way of doing this please let me know…

Thanks in advance for any advice you can give me.

Simon

If you want the roll up to occur from fact table granularity, you could define a Named Calculation like 'x/N', then define a "sum" measure on it (assuming that the 2 measures are in the same fact table/measure group). If they are in different fact tables/measure groups, you might be able to use a measure expression|||

Thanks for your post Deepak and sorry for the slow response.

You are right in assuming that the 2 measures are in the same fact table. In trying to explain the problem I have over simplified my example in the first post. I have tried to put together this illustration

to explain further:

[On the left is what I am trying to achieve and on the right is what I am getting right now]

Click Here

I see from your description above that the default ‘SUM’ aggregation will work. But if I now wanted to ‘/n’ at each level in the hierarchy how would I go about changing this behaviour?

edit: Adding link to diagram instead

Aggregation advice

Hi, I am having some trouble with changing the default aggregation behavior of my cube. I need to be able to change the aggregation at different levels of a dimension.

I am currently exploring the possibility of using custom member formulas, placing MDX script into a column within my dimension meaning that I could define a custom roll up at each level in my hierarchy – is this the best way?

I have two Dimensions as part of a pivot table:
DIMTime = [Year].[Quarter].[Hour]
DIMSpatial = [Group_1].[Group_0].[Route].[Link]
And a calculated measure = SUM(x)/SUM(N)

When I ‘roll up’ (total) this is what I am currently getting:

DIM Time

Group_0 2006

1 0.9398

2 0.9471

Total 0.9424

I want to change the roll up to be 0.9398 + 0.9471 = 1.8869

Could anyone give me some guidance on how the MDX Script should look? I have been trying lots of combinations but keep getting a #Value! Error. I haven’t yet been able to implement a custom aggregation so if I am going down the wrong avenue or if you think there is a better way of doing this please let me know…

Thanks in advance for any advice you can give me.

Simon

If you want the roll up to occur from fact table granularity, you could define a Named Calculation like 'x/N', then define a "sum" measure on it (assuming that the 2 measures are in the same fact table/measure group). If they are in different fact tables/measure groups, you might be able to use a measure expression|||

Thanks for your post Deepak and sorry for the slow response.

You are right in assuming that the 2 measures are in the same fact table. In trying to explain the problem I have over simplified my example in the first post. I have tried to put together this illustration

to explain further:

[On the left is what I am trying to achieve and on the right is what I am getting right now]

Click Here

I see from your description above that the default ‘SUM’ aggregation will work. But if I now wanted to ‘/n’ at each level in the hierarchy how would I go about changing this behaviour?

edit: Adding link to diagram instead

aggregate function in the argument to another aggregate function

Hello all,
I am new to SQL Reporting services and I am having trouble performing a
group sum. I Currently have a group within another group the embedded
group (group2) is performing a group sum
(=3DCount(Fields!FacilityTemp1.V=ACalue/Fields!Temp2.Value) and this works
fine but now I would like to use the outside group to perform a sum of
all values within group2 however SQL reporting Services does not allow
aggregate function in the argument to another aggregate function. Does
anyone no how I can simply get around this. Expression that does not
work:
=3DSUM(Count(Fields!FacilityTemp=AC1.Value/Fields!Temp2.Value)
Thanks=20
ChrisSince this question is specific to SQL Server Reporting Services, you
should post it in the microsoft.public.sqlserver.reportingsvcs group.
Razvan