Alert for a job step

hi.
I have a job with 5 steps.
I want a custom alert after the first step is executed reagrdless of whether it succeeds or fails on execution. I want to do a net send of the alert only after step1 is completed. How do i do this?
Thank you very much for the help.You can create step 2 as the send alert and go to that step on both success and failure.

You can also call your step step 2
Create step 1 as a failure alert step
Create step 3 as a success alert step
Start the job at step 2 and goto step 1 on failure, step 3 on success.
Step 1 and step 3 can call an SP to send the alert.|||thank you for the reply. Appreciate your responses and help in this forum.
Yes, I have made a custom error message/code and an custom alert for that error code.
And all i do is raiserror with the custom error code passed as parameter in the job step. this is captured by the alert and the net send is generated. By the way how do i send an alert from an SP?:confused:

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

Agent failures and custom alerting

Hi

I need to have a reliable alerting system for my merge replications I have setup on my MSSQL 2005 server.

The problem is that the build-in alert system the 'agent failure' alert only triggers when 'all databases' for that specific alert is selected. There is no failure alert triggered when I select a database and force a failure. The 'agent succes' alert does never get triggered at all.

I need an reliable succes and failure alert per database because I need to do specific actions per database.

Can someone help me out here?

I'm thinking of building my own alerting system if the help here is insufficient. In that case I need to know in what tables to look. Maybe someone can give me some pointers?

Thanks anyway,

Edward

Have you tried through "Configure Replication Alerts" dialog?

Launch Sqlmonitor.exe --> Expanding publisher and publications --> L-Click a publication --> Warning and Agents Page > Configure Replication Alerts Dialog

It can configure all kinds of alerts, including agent success alert etc. You can try.

Thanks

Yunjing

|||

Thanks for your answer. I know how to configure alerts. Thats not the problem.
The problem is that the 'failure' alert only gets triggered when you select 'all databases' and that the 'Success' alert not gets triggered at all.

I need to trigger the 'Failure' alert per database

I need to trigger the 'success' alert per database

It aint working.

|||

Alert will be triggered per event. It can not be configured per database.

For example, push merge agent run on distributor and pull merge agent run on subscriber. If you set aleart per database, like per subscription db, then some error happened on distribution db won't be catched. It doesn't make sense.

As for some 'Success' alert, replication agent cut some alerts for SQL 2005. LIke if you configure alert for 'Replication Agent Success', these alerts will not be triggered, since some customers complained that too many default alerts are triggered, even for success agent run, and they don't care about success but failure and critical performance etc only.

In the next version of SQL Server, we will address this issue and provide better solution.

Hope the above information will help.

Thanks

Yunjing

|||

Hi Yunjing

Thanks for your help. As you can imagine i'm not really helped. The next version of MSSQL will probably out in 4 years or something.

The strange thing is that everything works fine if the 'merge replication' is NOT in continious mode. Unfortunately this is not the desirable mode.

|||

Hi Yunjing

I was wondering (because of the SP2 update last week) if this issue is adressed?

I cross my fingers..

Thanks anyway

Ed

Agent failures and custom alerting

Hi

I need to have a reliable alerting system for my merge replications I have setup on my MSSQL 2005 server.

The problem is that the build-in alert system the 'agent failure' alert only triggers when 'all databases' for that specific alert is selected. There is no failure alert triggered when I select a database and force a failure. The 'agent succes' alert does never get triggered at all.

I need an reliable succes and failure alert per database because I need to do specific actions per database.

Can someone help me out here?

I'm thinking of building my own alerting system if the help here is insufficient. In that case I need to know in what tables to look. Maybe someone can give me some pointers?

Thanks anyway,

Edward

Have you tried through "Configure Replication Alerts" dialog?

Launch Sqlmonitor.exe --> Expanding publisher and publications --> L-Click a publication --> Warning and Agents Page > Configure Replication Alerts Dialog

It can configure all kinds of alerts, including agent success alert etc. You can try.

Thanks

Yunjing

|||

Thanks for your answer. I know how to configure alerts. Thats not the problem.
The problem is that the 'failure' alert only gets triggered when you select 'all databases' and that the 'Success' alert not gets triggered at all.

I need to trigger the 'Failure' alert per database

I need to trigger the 'success' alert per database

It aint working.

|||

Alert will be triggered per event. It can not be configured per database.

For example, push merge agent run on distributor and pull merge agent run on subscriber. If you set aleart per database, like per subscription db, then some error happened on distribution db won't be catched. It doesn't make sense.

As for some 'Success' alert, replication agent cut some alerts for SQL 2005. LIke if you configure alert for 'Replication Agent Success', these alerts will not be triggered, since some customers complained that too many default alerts are triggered, even for success agent run, and they don't care about success but failure and critical performance etc only.

In the next version of SQL Server, we will address this issue and provide better solution.

Hope the above information will help.

Thanks

Yunjing

|||

Hi Yunjing

Thanks for your help. As you can imagine i'm not really helped. The next version of MSSQL will probably out in 4 years or something.

The strange thing is that everything works fine if the 'merge replication' is NOT in continious mode. Unfortunately this is not the desirable mode.

|||

Hi Yunjing

I was wondering (because of the SP2 update last week) if this issue is adressed?

I cross my fingers..

Thanks anyway

Ed

after SP1 getting rsInvalidDataSourceCredential

I am using a custom data source. (using ADO.net dataset). I don't store the
credentials on the report server. (basically I select the 4th option:
Credentials are not required). Everything was fine till I installed RS SP1.
Now whenever I run a report I get the following error:
rsInvalidDataSourceCredentialSetting 400 The current action cannot be
completed because the user data source credentials that are required to
execute this report are not stored in the report server database.
This is absurd, since I don't require credentials and I have specifcally
checked the option "Credentials are not required" for my data source.
Now comes the funny part. I decided to select the "Credentials stored
securely in the report server" option and gave "JUNK" values for user name
and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
anyways.george tharakan wrote:
>I am using a custom data source. (using ADO.net dataset). I don't store the
> credentials on the report server. (basically I select the 4th option:
> Credentials are not required). Everything was fine till I installed RS SP1.
> Now whenever I run a report I get the following error:
> rsInvalidDataSourceCredentialSetting 400 The current action cannot be
> completed because the user data source credentials that are required to
> execute this report are not stored in the report server database.
> This is absurd, since I don't require credentials and I have specifcally
> checked the option "Credentials are not required" for my data source.
> Now comes the funny part. I decided to select the "Credentials stored
> securely in the report server" option and gave "JUNK" values for user name
> and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
> anyways.
Microsoft Service Packs are always a ticket to adventure.
You never know where you'll eventually wind up.
GeoSynch|||Please check the following RS BOL webpage - in particular scroll to the
bottom and read the section about "No Credentials" very carefully:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_datasources_v1_87e9.asp
It says that you have to configure an account for "unattended report
processing". The following BOL webpage explains how to do this:
http://msdn.microsoft.com/library/en-us/rsadmin/htm/arp_configserver_v1_8teq.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
news:OYew$g9IFHA.2936@.TK2MSFTNGP15.phx.gbl...
> george tharakan wrote:
>>I am using a custom data source. (using ADO.net dataset). I don't store
>>the
>> credentials on the report server. (basically I select the 4th option:
>> Credentials are not required). Everything was fine till I installed RS
>> SP1.
>> Now whenever I run a report I get the following error:
>> rsInvalidDataSourceCredentialSetting 400 The current action cannot be
>> completed because the user data source credentials that are required to
>> execute this report are not stored in the report server database.
>> This is absurd, since I don't require credentials and I have specifcally
>> checked the option "Credentials are not required" for my data source.
>> Now comes the funny part. I decided to select the "Credentials stored
>> securely in the report server" option and gave "JUNK" values for user
>> name
>> and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
>> anyways.
> Microsoft Service Packs are always a ticket to adventure.
> You never know where you'll eventually wind up.
>
> GeoSynch
>|||Thanks Robert.
I understand whats going on now.