SQL2005-sql agent error

On Dec 6, 4:08 pm, Adalberto Andrade
wrote:
> Hi,
>
> I ran a script file in Sql Server Management Studio (query window) to
> load a job (inside of that file). For my surprise I got the message below :
>
> "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> The specified '@notify_email_operator_name' is invalid (valid values are
> returned by sp_help_operator)."
>
> In Error Logs (under SQL Server Agent) there is the warning below :
>
> "[260] Unable to start mail session (reason: No mail profile defined)"
>
> And the following error message :
>
> "[298] SQLServer Error: 2812, Could not find stored procedure
> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
>
> My SQL SERVER 2005 is an evalution version and the server is a Windows
> Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
>
> Any idea ?
> Thanks in advance,
> Adalberto Andrade

It looks like you didn't configure database mail on your server. If
it isn't configured, then you need to enable it and configure database
mail on your server. Unlike SQL Server 2000, in SQL Server 2005
configuring database mail is very simple. From SSMS expend the server
tree, then expend the management brunch. Right click on "database
mail" and from the menu select "Configure database mail". From this
point you'll work with the wizard.

Adi

Adi,

I am not sure because I have an other machine with the same OS, with
the same version on SQL and without any configuration of database mail and
these is no problem with SQL AGENT. So I have doubt about this.

The message "Unable to start mail session (reason: No mail profile
defined)" is a warning.

And the main problem is "Could not find store procedure
msdb.dbo.xp._sqlagent_notify (DisableAgentXPS)" ===> It appear in SQLSERVER
as a ERROR. So I don´t have doubt about that, I only don´t the reason and
solve this problem, but the question is that.


Thanks a lot
Best Regards,
Adalberto

"Adi" wrote:

> On Dec 6, 4:08 pm, Adalberto Andrade
> wrote:
> > Hi,
> >
> > I ran a script file in Sql Server Management Studio (query window) to
> > load a job (inside of that file). For my surprise I got the message below :
> >
> > "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> > The specified '@notify_email_operator_name' is invalid (valid values are
> > returned by sp_help_operator)."
> >
> > In Error Logs (under SQL Server Agent) there is the warning below :
> >
> > "[260] Unable to start mail session (reason: No mail profile defined)"
> >
> > And the following error message :
> >
> > "[298] SQLServer Error: 2812, Could not find stored procedure
> > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
> >
> > My SQL SERVER 2005 is an evalution version and the server is a Windows
> > Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> > Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
> >
> > Any idea ?
> > Thanks in advance,
> > Adalberto Andrade
>
> It looks like you didn't configure database mail on your server. If
> it isn't configured, then you need to enable it and configure database
> mail on your server. Unlike SQL Server 2000, in SQL Server 2005
> configuring database mail is very simple. From SSMS expend the server
> tree, then expend the management brunch. Right click on "database
> mail" and from the menu select "Configure database mail". From this
> point you'll work with the wizard.
>
> Adi
>

On Dec 7, 2:20 pm, Adalberto Andrade
wrote:
> Adi,
>
> I am not sure because I have an other machine with the same OS, with
> the same version on SQL and without any configuration of database mail and
> these is no problem with SQL AGENT. So I have doubt about this.
>
> The message "Unable to start mail session (reason: No mail profile
> defined)" is a warning.
>
> And the main problem is "Could not find store procedure
> msdb.dbo.xp._sqlagent_notify (DisableAgentXPS)" ===> It appear in SQLSERVER
> as a ERROR. So I don´t have doubt about that, I only don´t the reason and
> solve this problem, but the question is that.
>
> Thanks a lot
> Best Regards,
> Adalberto
>
>
>
> "Adi" wrote:
> > On Dec 6, 4:08 pm, Adalberto Andrade
> > wrote:
> > > Hi,
>
> > > I ran a script file in Sql Server Management Studio (query window)to
> > > load a job (inside of that file). For my surprise I got the message below :
>
> > > "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> > > The specified '@notify_email_operator_name' is invalid (valid values are
> > > returned by sp_help_operator)."
>
> > > In Error Logs (under SQL Server Agent) there is the warning below :
>
> > > "[260] Unable to start mail session (reason: No mail profile defined)"
>
> > > And the following error message :
>
> > > "[298] SQLServer Error: 2812, Could not find stored procedure
> > > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
>
> > > My SQL SERVER 2005 is an evalution version and the server is a Windows
> > > Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> > > Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
>
> > > Any idea ?
> > > Thanks in advance,
> > > Adalberto Andrade
>
> > It looks like you didn't configure database mail on your server. If
> > it isn't configured, then you need to enable it and configure database
> > mail on your server. Unlike SQL Server 2000, in SQL Server 2005
> > configuring database mail is very simple. From SSMS expend the server
> > tree, then expend the management brunch. Right click on "database
> > mail" and from the menu select "Configure database mail". From this
> > point you'll work with the wizard.
>
> > Adi- Hide quoted text -
>
> - Show quoted text -

I've seen few posts on the net that this is a problem that was fixed
with SQL Server's service pack 2. Could it be that some of your
servers have service pack 2 installed on them but the problematic
server doesn't? You can check it with this statement:

select serverproperty ('ProductLevel')

Adi

Adi,

In production Server the result is SP1 (where the problem appeared).
In development server the result of this select is RTM.
What exactly it means ?
By the way, for don´t spend more time and solve my problem fastly I
gave up to load that job and decided to create and edit it again (step by
step) in development server. Of course it ran OK with success. Yesterday I
had needed to transfer an other job (from development server to production
server) and for my surprise the original problem don´t repeat and the job
was loaded with success. I didn´t make nothing for it works OK now (load
without any problem). Where is the mystery ?

Best Regards,
Adalberto Andrade


"Adi" wrote:

> On Dec 7, 2:20 pm, Adalberto Andrade
> wrote:
> > Adi,
> >
> > I am not sure because I have an other machine with the same OS, with
> > the same version on SQL and without any configuration of database mail and
> > these is no problem with SQL AGENT. So I have doubt about this.
> >
> > The message "Unable to start mail session (reason: No mail profile
> > defined)" is a warning.
> >
> > And the main problem is "Could not find store procedure
> > msdb.dbo.xp._sqlagent_notify (DisableAgentXPS)" ===> It appear in SQLSERVER
> > as a ERROR. So I don´t have doubt about that, I only don´t the reason and
> > solve this problem, but the question is that.
> >
> > Thanks a lot
> > Best Regards,
> > Adalberto
> >
> >
> >
> > "Adi" wrote:
> > > On Dec 6, 4:08 pm, Adalberto Andrade
> > > wrote:
> > > > Hi,
> >
> > > > I ran a script file in Sql Server Management Studio (query window) to
> > > > load a job (inside of that file). For my surprise I got the message below :
> >
> > > > "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> > > > The specified '@notify_email_operator_name' is invalid (valid values are
> > > > returned by sp_help_operator)."
> >
> > > > In Error Logs (under SQL Server Agent) there is the warning below :
> >
> > > > "[260] Unable to start mail session (reason: No mail profile defined)"
> >
> > > > And the following error message :
> >
> > > > "[298] SQLServer Error: 2812, Could not find stored procedure
> > > > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
> >
> > > > My SQL SERVER 2005 is an evalution version and the server is a Windows
> > > > Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> > > > Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
> >
> > > > Any idea ?
> > > > Thanks in advance,
> > > > Adalberto Andrade
> >
> > > It looks like you didn't configure database mail on your server. If
> > > it isn't configured, then you need to enable it and configure database
> > > mail on your server. Unlike SQL Server 2000, in SQL Server 2005
> > > configuring database mail is very simple. From SSMS expend the server
> > > tree, then expend the management brunch. Right click on "database
> > > mail" and from the menu select "Configure database mail". From this
> > > point you'll work with the wizard.
> >
> > > Adi- Hide quoted text -
> >
> > - Show quoted text -
>
> I've seen few posts on the net that this is a problem that was fixed
> with SQL Server's service pack 2. Could it be that some of your
> servers have service pack 2 installed on them but the problematic
> server doesn't? You can check it with this statement:
>
> select serverproperty ('ProductLevel')
>
> Adi
>

On Dec 12, 3:33 pm, Adalberto Andrade
wrote:
> Adi,
>
> In production Server the result is SP1 (where the problem appeared).
> In development server the result of this select is RTM.
> What exactly it means ?
> By the way, for don´t spend more time and solve my problem fastly I
> gave up to load that job and decided to create and edit it again (step by
> step) in development server. Of course it ran OK with success. Yesterday I
> had needed to transfer an other job (from development server to production
> server) and for my surprise the original problem don´t repeat and the job
> was loaded with success. I didn´t make nothing for it works OK now (load
> without any problem). Where is the mystery ?
>
> Best Regards,
> Adalberto Andrade
>
>

Hi Adalberto

The query select serverproperty('ProductLevel) shows you the last
service pack for SQL Server that was installed on a specific
instance. SP1 means that service pack 1 was installed on your
server. RTM means that no service pack was installed.

I can't tell you for sure how come when you created the job using the
GUI, you didn't encounter any problem. The only guess that I can
make, is that the GUI on the production server created a different
script from the GUI on the development server. If you want you can
check it by creating the script from both servers and compare the
scripts. As for the other job that you were able to create with no
problems at all - Maybe it just didn't use the problematic stored
procedure.

Adi

>
> "Adi" wrote:
> > On Dec 7, 2:20 pm, Adalberto Andrade
> > wrote:
> > > Adi,
>
> > > I am not sure because I have an other machine with the same OS, with
> > > the same version on SQL and without any configuration of database mailand
> > > these is no problem with SQL AGENT. So I have doubt about this.
>
> > > The message "Unable to start mail session (reason: No mail profile
> > > defined)" is a warning.
>
> > > And the main problem is "Could not find store procedure
> > > msdb.dbo.xp._sqlagent_notify (DisableAgentXPS)" ===> It appear in SQLSERVER
> > > as a ERROR. So I don´t have doubt about that, I only don´t the reason and
> > > solve this problem, but the question is that.
>
> > > Thanks a lot
> > > Best Regards,
> > > Adalberto
>
> > > "Adi" wrote:
> > > > On Dec 6, 4:08 pm, Adalberto Andrade
> > > > wrote:
> > > > > Hi,
>
> > > > > I ran a script file in Sql Server Management Studio (query window) to
> > > > > load a job (inside of that file). For my surprise I got the message below :
>
> > > > > "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> > > > > The specified '@notify_email_operator_name' is invalid (valid values are
> > > > > returned by sp_help_operator)."
>
> > > > > In Error Logs (under SQL Server Agent) there is the warning below :
>
> > > > > "[260] Unable to start mail session (reason: No mail profile defined)"
>
> > > > > And the following error message :
>
> > > > > "[298] SQLServer Error: 2812, Could not find stored procedure
> > > > > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
>
> > > > > My SQL SERVER 2005 is an evalution version and the server is a Windows
> > > > > Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> > > > > Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
>
> > > > > Any idea ?
> > > > > Thanks inadvance,
> > > > > AdalbertoAndrade
>
> > > > It looks like you didn't configure database mail on your server. If
> > > > it isn't configured, then you need to enable it and configure database
> > > > mail on your server. Unlike SQL Server 2000, in SQL Server 2005
> > > > configuring database mail is very simple. From SSMS expend the server
> > > > tree, then expend the management brunch. Right click on "database
> > > > mail" and from the menu select "Configure database mail". From this
> > > > point you'll work with the wizard.
>
> > > > Adi- Hide quoted text -
>
> > > - Show quoted text -
>
> > I've seen few posts on the net that this is a problem that was fixed
> > with SQL Server's service pack 2. Could it be that some of your
> > servers have service pack 2 installed on them but the problematic
> > server doesn't? You can check it with this statement:
>
> > select serverproperty ('ProductLevel')
>
> > Adi- Hide quoted text -
>
> - Show quoted text -

Hi Adi,

I agree with you. Thanks for the help.


Best Regards,
Adalberto Andrade

"Adi" wrote:

> On Dec 12, 3:33 pm, Adalberto Andrade
> wrote:
> > Adi,
> >
> > In production Server the result is SP1 (where the problem appeared).
> > In development server the result of this select is RTM.
> > What exactly it means ?
> > By the way, for don´t spend more time and solve my problem fastly I
> > gave up to load that job and decided to create and edit it again (step by
> > step) in development server. Of course it ran OK with success. Yesterday I
> > had needed to transfer an other job (from development server to production
> > server) and for my surprise the original problem don´t repeat and the job
> > was loaded with success. I didn´t make nothing for it works OK now (load
> > without any problem). Where is the mystery ?
> >
> > Best Regards,
> > Adalberto Andrade
> >
> >
>
> Hi Adalberto
>
> The query select serverproperty('ProductLevel) shows you the last
> service pack for SQL Server that was installed on a specific
> instance. SP1 means that service pack 1 was installed on your
> server. RTM means that no service pack was installed.
>
> I can't tell you for sure how come when you created the job using the
> GUI, you didn't encounter any problem. The only guess that I can
> make, is that the GUI on the production server created a different
> script from the GUI on the development server. If you want you can
> check it by creating the script from both servers and compare the
> scripts. As for the other job that you were able to create with no
> problems at all - Maybe it just didn't use the problematic stored
> procedure.
>
> Adi
>
> >
> > "Adi" wrote:
> > > On Dec 7, 2:20 pm, Adalberto Andrade
> > > wrote:
> > > > Adi,
> >
> > > > I am not sure because I have an other machine with the same OS, with
> > > > the same version on SQL and without any configuration of database mail and
> > > > these is no problem with SQL AGENT. So I have doubt about this.
> >
> > > > The message "Unable to start mail session (reason: No mail profile
> > > > defined)" is a warning.
> >
> > > > And the main problem is "Could not find store procedure
> > > > msdb.dbo.xp._sqlagent_notify (DisableAgentXPS)" ===> It appear in SQLSERVER
> > > > as a ERROR. So I don´t have doubt about that, I only don´t the reason and
> > > > solve this problem, but the question is that.
> >
> > > > Thanks a lot
> > > > Best Regards,
> > > > Adalberto
> >
> > > > "Adi" wrote:
> > > > > On Dec 6, 4:08 pm, Adalberto Andrade
> > > > > wrote:
> > > > > > Hi,
> >
> > > > > > I ran a script file in Sql Server Management Studio (query window) to
> > > > > > load a job (inside of that file). For my surprise I got the message below :
> >
> > > > > > "Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243
> > > > > > The specified '@notify_email_operator_name' is invalid (valid values are
> > > > > > returned by sp_help_operator)."
> >
> > > > > > In Error Logs (under SQL Server Agent) there is the warning below :
> >
> > > > > > "[260] Unable to start mail session (reason: No mail profile defined)"
> >
> > > > > > And the following error message :
> >
> > > > > > "[298] SQLServer Error: 2812, Could not find stored procedure
> > > > > > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)"
> >
> > > > > > My SQL SERVER 2005 is an evalution version and the server is a Windows
> > > > > > Server 2003. The SQL SERVER 2005 is installed in the drive "D:\Program
> > > > > > Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\DATA"
> >
> > > > > > Any idea ?
> > > > > > Thanks in advance,
> > > > > > Adalberto Andrade
> >
> > > > > It looks like you didn't configure database mail on your server. If
> > > > > it isn't configured, then you need to enable it and configure database
> > > > > mail on your server. Unlike SQL Server 2000, in SQL Server 2005
> > > > > configuring database mail is very simple. From SSMS expend the server
> > > > > tree, then expend the management brunch. Right click on "database
> > > > > mail" and from the menu select "Configure database mail". From this
> > > > > point you'll work with the wizard.
> >
> > > > > Adi- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > I've seen few posts on the net that this is a problem that was fixed
> > > with SQL Server's service pack 2. Could it be that some of your
> > > servers have service pack 2 installed on them but the problematic
> > > server doesn't? You can check it with this statement:
> >
> > > select serverproperty ('ProductLevel')
> >
> > > Adi- Hide quoted text -
> >
> > - Show quoted text -
>
>