Database name not found in the Edit job step in SQL Server Enterprise Manager

Hi,

I need help. I transferred my database from one server to another by
using attach detach process.I created all the jobs which existed in the

old server in the new server. Everything looked fine but a couple of
jobs is a giving me some problems. When I try to edit the job step I
could see that the database dropdown list doesn't contain the database
name(which I transferred from the old server)

Does anyone have any idea about it.

Thanks
Arun(arunkumar_m2001@.yahoo.com) writes:
> I need help. I transferred my database from one server to another by
> using attach detach process.I created all the jobs which existed in the
> old server in the new server. Everything looked fine but a couple of
> jobs is a giving me some problems. When I try to edit the job step I
> could see that the database dropdown list doesn't contain the database
> name(which I transferred from the old server)
>
> Does anyone have any idea about it.

Have you tried simply closing down Enterprise Manager and starting it
again? I did a quick test, and it appears that the job-step dialogue
caches the database list, so it may not have seen the new database yet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Database Migration

Hello All,
I need to migrate our databases from one server to another. Now since these
databases have DTS packages/scheduled jobs associated with them, should i
just restore the MSDB database on the target server to recover all settings?
Or is there any alternate way that will be best for my scenario?
Thanks
MannyI would script the job (right-click in EM). And Save DTS packages as files and from those files open
and save them in the new instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:70B7B8A8-DC90-4C7E-B484-B88EABD23F92@.microsoft.com...
> Hello All,
> I need to migrate our databases from one server to another. Now since these
> databases have DTS packages/scheduled jobs associated with them, should i
> just restore the MSDB database on the target server to recover all settings?
> Or is there any alternate way that will be best for my scenario?
> Thanks
> Manny

database maintenance plan

I am having an interesting problem. I have created 14 different jobs with 3
different databases. They are daily, weekly, and monthly. Along with
backing up the transaction logs. I have created them 3 times, and they are
fine, until the next day I go to check on them and they are gone.
I have installed the client tools for SQL, and I am using it at my desktop,
I am not sure what is going on, but it keeps dissapering. Not all of them,
just some, and it is not the same plans that I have created.
Has anyone every seen this before, as this is new to me.
Thanks,
Stacy
Check permissions to the msdb database for
user you have used when you din't see the jobs.
Regards.
"Stacy" wrote:

> I am having an interesting problem. I have created 14 different jobs with 3
> different databases. They are daily, weekly, and monthly. Along with
> backing up the transaction logs. I have created them 3 times, and they are
> fine, until the next day I go to check on them and they are gone.
> I have installed the client tools for SQL, and I am using it at my desktop,
> I am not sure what is going on, but it keeps dissapering. Not all of them,
> just some, and it is not the same plans that I have created.
> Has anyone every seen this before, as this is new to me.
> Thanks,
> Stacy

database maintenance plan

I am having an interesting problem. I have created 14 different jobs with 3
different databases. They are daily, weekly, and monthly. Along with
backing up the transaction logs. I have created them 3 times, and they are
fine, until the next day I go to check on them and they are gone.
I have installed the client tools for SQL, and I am using it at my desktop,
I am not sure what is going on, but it keeps dissapering. Not all of them,
just some, and it is not the same plans that I have created.
Has anyone every seen this before, as this is new to me.
Thanks,
StacyCheck permissions to the msdb database for
user you have used when you din't see the jobs.
Regards.
"Stacy" wrote:
> I am having an interesting problem. I have created 14 different jobs with 3
> different databases. They are daily, weekly, and monthly. Along with
> backing up the transaction logs. I have created them 3 times, and they are
> fine, until the next day I go to check on them and they are gone.
> I have installed the client tools for SQL, and I am using it at my desktop,
> I am not sure what is going on, but it keeps dissapering. Not all of them,
> just some, and it is not the same plans that I have created.
> Has anyone every seen this before, as this is new to me.
> Thanks,
> Stacy

database maintenance plan

I am having an interesting problem. I have created 14 different jobs with 3
different databases. They are daily, weekly, and monthly. Along with
backing up the transaction logs. I have created them 3 times, and they are
fine, until the next day I go to check on them and they are gone.
I have installed the client tools for SQL, and I am using it at my desktop,
I am not sure what is going on, but it keeps dissapering. Not all of them,
just some, and it is not the same plans that I have created.
Has anyone every seen this before, as this is new to me.
Thanks,
StacyCheck permissions to the msdb database for
user you have used when you din't see the jobs.
Regards.
"Stacy" wrote:

> I am having an interesting problem. I have created 14 different jobs with
3
> different databases. They are daily, weekly, and monthly. Along with
> backing up the transaction logs. I have created them 3 times, and they ar
e
> fine, until the next day I go to check on them and they are gone.
> I have installed the client tools for SQL, and I am using it at my desktop
,
> I am not sure what is going on, but it keeps dissapering. Not all of them
,
> just some, and it is not the same plans that I have created.
> Has anyone every seen this before, as this is new to me.
> Thanks,
> Stacy

Database Maintenance

I've set up a database maintenance plan in Enterprise
manager, but it seems none of the jobs are running. What
am I missing?
ThanksIn the jobs screen in EM.
Is the Job Enabled ?
Is the Schedule Enabled ?
Is it working but simply failed ?
How do you know/think it isn't running?
Is SQL Server Agent started ?
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike D" <anonymous@.discussions.microsoft.com> wrote in message
news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
> I've set up a database maintenance plan in Enterprise
> manager, but it seems none of the jobs are running. What
> am I missing?
> Thanks|||Is SQL Server Agent started?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike D" <anonymous@.discussions.microsoft.com> wrote in message
news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
> I've set up a database maintenance plan in Enterprise
> manager, but it seems none of the jobs are running. What
> am I missing?
> Thanks|||Yes
Yes
No
I'm not finding updated database backups in the backups
directory
NO
>--Original Message--
>In the jobs screen in EM.
>Is the Job Enabled ?
>Is the Schedule Enabled ?
>Is it working but simply failed ?
>How do you know/think it isn't running?
>Is SQL Server Agent started ?
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Mike D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
>> I've set up a database maintenance plan in Enterprise
>> manager, but it seems none of the jobs are running. What
>> am I missing?
>> Thanks
>
>.
>|||No, it wasn't running and when I try to start it I get the
message:
Could not start the sqlserveragent service on local
computer. The service did not return an error. This could
be an internal windows error or an internal service error.
>--Original Message--
>Is SQL Server Agent started?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mike D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
>> I've set up a database maintenance plan in Enterprise
>> manager, but it seems none of the jobs are running. What
>> am I missing?
>> Thanks
>
>.
>|||Check to ensure the login and password that the agent runs under is correct.
--
Andrew J. Kelly SQL MVP
"Mike D" <anonymous@.discussions.microsoft.com> wrote in message
news:000a01c3c4af$f3a9ac90$a101280a@.phx.gbl...
> No, it wasn't running and when I try to start it I get the
> message:
> Could not start the sqlserveragent service on local
> computer. The service did not return an error. This could
> be an internal windows error or an internal service error.
>
> >--Original Message--
> >Is SQL Server Agent started?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mike D" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
> >> I've set up a database maintenance plan in Enterprise
> >> manager, but it seems none of the jobs are running. What
> >> am I missing?
> >>
> >> Thanks
> >
> >
> >.
> >|||It was under the Local System account. I switched it to an
admin account, but I got the same error when trying to
start it.
>--Original Message--
>Check to ensure the login and password that the agent
runs under is correct.
>--
>Andrew J. Kelly SQL MVP
>
>"Mike D" <anonymous@.discussions.microsoft.com> wrote in
message
>news:000a01c3c4af$f3a9ac90$a101280a@.phx.gbl...
>> No, it wasn't running and when I try to start it I get
the
>> message:
>> Could not start the sqlserveragent service on local
>> computer. The service did not return an error. This
could
>> be an internal windows error or an internal service
error.
>>
>> >--Original Message--
>> >Is SQL Server Agent started?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mike D" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
>> >> I've set up a database maintenance plan in Enterprise
>> >> manager, but it seems none of the jobs are running.
What
>> >> am I missing?
>> >>
>> >> Thanks
>> >
>> >
>> >.
>> >
>
>.
>|||You might want to give MS PSS a call so they can work directly with you.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly SQL MVP
"Mike D" <anonymous@.discussions.microsoft.com> wrote in message
news:063801c3c4b9$4cd9ec90$a001280a@.phx.gbl...
> It was under the Local System account. I switched it to an
> admin account, but I got the same error when trying to
> start it.
> >--Original Message--
> >Check to ensure the login and password that the agent
> runs under is correct.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Mike D" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:000a01c3c4af$f3a9ac90$a101280a@.phx.gbl...
> >> No, it wasn't running and when I try to start it I get
> the
> >> message:
> >> Could not start the sqlserveragent service on local
> >> computer. The service did not return an error. This
> could
> >> be an internal windows error or an internal service
> error.
> >>
> >>
> >> >--Original Message--
> >> >Is SQL Server Agent started?
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Mike D" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:08f701c3c4a9$89412190$a501280a@.phx.gbl...
> >> >> I've set up a database maintenance plan in Enterprise
> >> >> manager, but it seems none of the jobs are running.
> What
> >> >> am I missing?
> >> >>
> >> >> Thanks
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?No, DBCC DBREINDEX includes a full update statistics.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?
No, DBCC DBREINDEX includes a full update statistics.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?
|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?No, DBCC DBREINDEX includes a full update statistics.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx
.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx
.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?

Database Mail Operator Question

I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||Yes, I was able to send an e-mail using T-SQL when logged in with the account the SQL Server service / agent was using. I was able to fix the problem, although I am not sure if it's a permanent solution.
The job operator e-mail address was a distribution list on Exchange. I changed the e-mail address to include my own, ran the job, and one alert came through fine. Then I sent it only to my account, again, worked fine. I then retyped the operator e-mail address back in of the distribution list and it succeeded. The one thing I am positive of is that the e-mail address was correct right from the start because I use the same e-mail address in the Pagers section. This was and is identical to what was in the E-mail name of the operator.
Does this sound like a bug? I guess it's a little concerning considering one of the benefits of database mail is it being SMTP based and supposed to be a lot more reliable.
Thanks,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:e1A60tPmIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens
|||I've just noticed another issue. I've created all my maintenance plans using the wizard. When I go into the "Reporting and Logging" section for the maintenance plan, I've selected the "Send report to an email recipient" check box, the recipient being the same one that the job alert sends to. However, I am not receiving the e-mail report. Instead, I am only getting an e-mail from the job saying it has completed. I don't get a report from the maintenance plan. I have also confirmed that the logging file is created in a folder on the server.
Any idea why I am not getting this report in an e-mail?
Thank-you,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:eC7QYMXmIHA.5684@.TK2MSFTNGP03.phx.gbl...
Yes, I was able to send an e-mail using T-SQL when logged in with the account the SQL Server service / agent was using. I was able to fix the problem, although I am not sure if it's a permanent solution.
The job operator e-mail address was a distribution list on Exchange. I changed the e-mail address to include my own, ran the job, and one alert came through fine. Then I sent it only to my account, again, worked fine. I then retyped the operator e-mail address back in of the distribution list and it succeeded. The one thing I am positive of is that the e-mail address was correct right from the start because I use the same e-mail address in the Pagers section. This was and is identical to what was in the E-mail name of the operator.
Does this sound like a bug? I guess it's a little concerning considering one of the benefits of database mail is it being SMTP based and supposed to be a lot more reliable.
Thanks,
Rubens
"Rubens" <rubensrose@.hotmail.com> wrote in message news:e1A60tPmIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hmmm, come to think of it, no I wasn't. I will have to test that when back in the office tomorrow. I will let you know.
Thanks John.
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:erH9oGOmIHA.1184@.TK2MSFTNGP04.phx.gbl...
Hi
And were you logged in as this account when you tested sp_send_dbmail?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23A5C6DOmIHA.5084@.TK2MSFTNGP04.phx.gbl...
It is running under a domain account that is an administrator on the server, which is the same account the SQL Server service is running under (I haven't split them up and restricted it as is recommended).
Thank-you,
Rubens
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:O6%231lsNmIHA.1208@.TK2MSFTNGP05.phx.gbl...
Hi
What account is SQL Agent running under?
John
"Rubens" <rubensrose@.hotmail.com> wrote in message news:%23Tec6mLmIHA.3780@.TK2MSFTNGP06.phx.gbl...
I have two jobs configured to e-mail myself on completion, but I am not currently receiving these notifications.
The SQL Agent is configured to use database mail, with a specific private profile. Of note is that the button to "Test" the mail profile is grayed out on the Alert System tab. When I execute sp_send_dbmail using the e-mail address from the operator and use the profile name defined in the SQL Agent properties, I receive an e-mail. However the jobs themselves are not sending me any e-mail.
I've also confirmed the correct name of the profile in the sysmail_profile table. The e-mail does not appear in sysmail_mailitems and sysmail_send_retries is empty. So the job notification is not getting queued.
Any ideas?
Thank-you,
Rubens

database mail isn't working in the SQL Agent job notification

Hi,

Starting a few weeks ago I stopped receiving notifications when SQL Server Agent Jobs failed. The only error I see from the Error logs is the following: "[264] An attempt was made to send an email when no email session has been established"

I've followed all of the postings about stoping and restarting the Agent service, confirming that the database mail can send out test emails and making sure that database mail account name matches the actual SMTP account.

From my testing, I can successfully send out a test email by right-clicking on "Database Mail" - "Send Test E-Mail."

Here's my setup:
Active/Passive SQL Server 2005 Cluster.
32 bit
SS2005 SP1 installed

Database Mail worked for around 6 months and then stopped. Not sure if it's a permissions issue, patch issue or a configuration issue.

Any help resolving this would be great.

Sincerely,

Steve

Ditto for me. Standard SQL 2005 setup. Engine, AS, Integration, etc. with SP1. I've done all the troubleshooting steps detailed here because this was the closest related issue I found on the Web.
The stored proc sp_send_dbmail works. So I can craft a workaround.

I found the same issue posted under Technet here. Any ideas?

|||

Well this is a slap in the face.

My manager suggested I do a restart of the SQL Server Agent. I had tried that before but since he is the boss I did it again expecting the same results. Well as you might have guessed - its working. And not just on the development server but the production too. Can't figure it.

|||

Restarting has become my habit in debugging now thanks to this

"Restart" has been mentioned in couple threads though if you searched by your error message here (or Google)

It's really weird, SQL Mail stopped working randomly IMO, thank god this doesn't happen to SQL Server

|||

Steve,

I've seen this from time-to-time on our servers, as well. After a restart of SQL Server Agent, database mail will usually run fine for a month and then the error will come back again. It is frustrating especially when it happens on production boxes with replication, but at least it doesn't require a server reboot like SQL Mail always did when it locked up.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Fails For SQL Server Agent Jobs

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile and been able to successfully send test emails. The problem occurs with notifications for sql server agent jobs. I've set up email notifications for when certain jobs fail, but the emails aren't being sent. The following errors show up in the sql server agent log:

"Message
[264] An attempt was made to send an email when no email session has been established"

I tried re-starting the sql server agent and the following message appeared in the log:

"Message
[355] The mail system failed to initialize; check configuration settings"

Both the SQL Server Database and SQL Server Agent are running with the same domain account. I've set SQL Server Agent to send emails through the default Database Mail profile (set under the "Alert System" tab). Any help would be greatly appreciated. Thank you.

If you see this on 64 bit environment, we have a tracking bug for this.

This is planned to get addressed for our next public release.

The workaround would be to use SQLAgent mail on 64 bit environment.

Please refer to KB article # 908360 for more information.

Thanks,

Gops Dwarak

|||I am experiencing the same problem you are having, so I would like to know if you resolved it and what you did to resolve it. I would appreciate your feedback. Thank you.|||

Please refer to KB article # 908360 for work around.

SP1 address this bug

Till then using SQLAgent profile for sending mails for notifications is the workaround

If you want to use DBMail profile to send notifications for job runs, the workaround would be to add a TSQL job step as the last step to the job that will call sp_notify_operator.

Hope this helps!

Thanks,

908360

|||

I have the same error no matter which email method I use. Help me get SP1 please.

Thanks

Sam

|||

Hi,

I have come across the same problem and got resolved by enabling mail session in SQL Server Agent and it is working fine after restarting the SQL Agent.

Pramod

|||

Hello,

I'm getting the same problem. The only issue is we have 3 cluster servers running 64-bit and, for some reason, the one that is running most of the jobs and is the distributor for our replication, cannot send an email via job notification. The other two clusters work fine.

We have dropped and added everything a couple of times and restarted SQL Server Agent. I checked and the SQL Server Agent is running under the domain account. But we still get "[264] An attempt was made to send an email when no email session has been established" in the SQL Server Agent log. Also have a "[355] The mail system failed to initialize; check configuration settings" error, but I was playing with switching it to SQLMail, so this might be erroneous.

Any help would be appreciated.

|||Never mind. I just noticed the version on the cluster that is failing is 1399, not 2047.|||I am getting the same error on one of two identical servers, one works one does not.
I get "[355] The mail system failed to initialize; check configuration settings" when I restart the SQL Server Agent and
"[264] An attempt was made to send an email when no email session has been established" when I have a job run that is set to notify the operator on completion.

I am running the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I setup Database Mail and my operator exactly the same on both servers, I enabled the Mail profile under the "Alert System" tab in the SQL Server Agent properties window. I can successfully send a test message from the Database Mail object under Management.

I'm running out of ideas.

Database Mail Error Object reference not set to an instance of an object

We have SQL Server 2005 setup to send out emails on job failure... This am no emails went out for failed jobs and there were several (identical) log entries as shown below

---

Date 2/27/2007 4:30:04 AM
Log Database Mail (Database Mail Log)

Log ID 48
Process ID 2180

Mail Item ID 29
Last Modified 2/27/2007 4:30:04 AM
Last Modified By sa

Message
Object reference not set to an instance of an object.

---

How can I diagnose this? I searched the forums and google groups and couldnt find this error message in the context of the database mail component of SQLServer. Thanks in advance

PS: This has been moved from the SSIS forum ... The moderator there can delete that thread

got these yesterday am and today am ... any insights?
|||deleted the mail profile and all associated accounts and re-created them and that fixed the issue