Database Mail: getting bounce info from Exchange?

Database Mail only sends SMTP email messages and logs problems
connecting to the mail server. However, I also need to log bounced
emails (bad address, etc).
Are there any clean ways to do this with Database Mail and Exchange?
Or will I have to mix Database Mail with SQL Mail to read bounces out
of the inbox? Or, have a .NET windows service that talks to the
Exchange API?"Manny Klystron" <manny.klystron@.gmail.com> wrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegroups.com...
> Database Mail only sends SMTP email messages and logs problems
> connecting to the mail server. However, I also need to log bounced
> emails (bad address, etc).
> Are there any clean ways to do this with Database Mail and Exchange?
> Or will I have to mix Database Mail with SQL Mail to read bounces out
> of the inbox? Or, have a .NET windows service that talks to the
> Exchange API?
I'd have a separate API that handles this. It's not really a good duty for
SQL Server.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks
> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A
|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>
|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A
|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Serve
r.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi,
> we are in the process of upgrading SQL Server 2000 to 2005,
> Now, a lot of emails are sent within stored procedures using
> xp_smtp_sendmail.
> Now this procedure is quite nice because it lets the caller
> define diffrent From names and ReplyTo depending on certain conditions.
>
> Since I can't reuse this procedure in SQL 2005, I am testing the
> Database Mail using the sp_send_dbmail stored procedure along with
> some profiles I created. It works great, but...
>
> My problem here is that sp_send_dbmail doesn't let you dynamically change
> the FROM and REPLYTO parameters, it uses the ones on the profile
> sp_send_dbmail
> recieves in parameter. This leaves me with a dilemna because the from and
> replyTo
> adresses possibility are too great (that is why it was made dynamic in our
> SQL 2000 using xp_smtp_sendmail)
>
> How should I proceed if I do not want to create 1 profile per possibility?
> Is there another way or another SMTP send mail procedure or technique I
> could use in SQL 2005
> that will allow me to do the same thing as in XP_smtp_SendMail ?
>
> Ideas anyone ?
>
> Thanks
>
>|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>|||> Thanks, But it is a x64 bit SQL Server..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred
's post for suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLSe...=2644
98
A

database mail Vs. xp_smtp_sendmail

Hi,
we are in the process of upgrading SQL Server 2000 to 2005,
Now, a lot of emails are sent within stored procedures using
xp_smtp_sendmail.
Now this procedure is quite nice because it lets the caller
define diffrent From names and ReplyTo depending on certain conditions.
Since I can't reuse this procedure in SQL 2005, I am testing the
Database Mail using the sp_send_dbmail stored procedure along with
some profiles I created. It works great, but...
My problem here is that sp_send_dbmail doesn't let you dynamically change
the FROM and REPLYTO parameters, it uses the ones on the profile
sp_send_dbmail
recieves in parameter. This leaves me with a dilemna because the from and
replyTo
adresses possibility are too great (that is why it was made dynamic in our
SQL 2000 using xp_smtp_sendmail)
How should I proceed if I do not want to create 1 profile per possibility?
Is there another way or another SMTP send mail procedure or technique I
could use in SQL 2005
that will allow me to do the same thing as in XP_smtp_SendMail ?
Ideas anyone ?
Thanks> Since I can't reuse this procedure in SQL 2005
Why not? As long as you are on 32-bit, you can install the DLL on 2005 the
same way you did on 2000.
A|||> Since I can't reuse this procedure in SQL 2005, I am testing the
You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi,
> we are in the process of upgrading SQL Server 2000 to 2005,
> Now, a lot of emails are sent within stored procedures using
> xp_smtp_sendmail.
> Now this procedure is quite nice because it lets the caller
> define diffrent From names and ReplyTo depending on certain conditions.
>
> Since I can't reuse this procedure in SQL 2005, I am testing the
> Database Mail using the sp_send_dbmail stored procedure along with
> some profiles I created. It works great, but...
>
> My problem here is that sp_send_dbmail doesn't let you dynamically change
> the FROM and REPLYTO parameters, it uses the ones on the profile
> sp_send_dbmail
> recieves in parameter. This leaves me with a dilemna because the from and
> replyTo
> adresses possibility are too great (that is why it was made dynamic in our
> SQL 2000 using xp_smtp_sendmail)
>
> How should I proceed if I do not want to create 1 profile per possibility?
> Is there another way or another SMTP send mail procedure or technique I
> could use in SQL 2005
> that will allow me to do the same thing as in XP_smtp_SendMail ?
>
> Ideas anyone ?
>
> Thanks
>
>|||Thanks, But it is a x64 bit SQL Server..
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "msnews.microsoft.com" <spam@.spam.com> wrote in message
> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from
>> and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in
>> our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per
>> possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
Well, you can't use a 32 bit DLL in 64 bit code. See Russell's reply to Fred's post for suggestion.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <spam@.spam.com> wrote in message
news:euuEqS8aHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks, But it is a x64 bit SQL Server..
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%230invczaHHA.960@.TK2MSFTNGP03.phx.gbl...
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> You should be able to use xp_smtp in 2005, as long as it is 32 bit SQL Server.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "msnews.microsoft.com" <spam@.spam.com> wrote in message
>> news:e8h4gUzaHHA.5020@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> we are in the process of upgrading SQL Server 2000 to 2005,
>> Now, a lot of emails are sent within stored procedures using
>> xp_smtp_sendmail.
>> Now this procedure is quite nice because it lets the caller
>> define diffrent From names and ReplyTo depending on certain conditions.
>>
>> Since I can't reuse this procedure in SQL 2005, I am testing the
>> Database Mail using the sp_send_dbmail stored procedure along with
>> some profiles I created. It works great, but...
>>
>> My problem here is that sp_send_dbmail doesn't let you dynamically change
>> the FROM and REPLYTO parameters, it uses the ones on the profile
>> sp_send_dbmail
>> recieves in parameter. This leaves me with a dilemna because the from and
>> replyTo
>> adresses possibility are too great (that is why it was made dynamic in our
>> SQL 2000 using xp_smtp_sendmail)
>>
>> How should I proceed if I do not want to create 1 profile per possibility?
>> Is there another way or another SMTP send mail procedure or technique I
>> could use in SQL 2005
>> that will allow me to do the same thing as in XP_smtp_SendMail ?
>>
>> Ideas anyone ?
>>
>> Thanks
>>
>>
>|||> Thanks, But it is a x64 bit SQL Server..
In that case, you could learn the format and write a stored procedure that
creates a properly-formatted text file with a .msg extension and drops it
into the pickup folder of a local SMTP server (which doesn't necessarily
have to live on the same server, so could be 32-bit). This would allow you
to have full control over FROM/REPLYTO etc.
Another option is to set up a 32-bit edition of Express or Workgroup on
another server (even a web server), and route messages through it. As long
as the network path is reliable, not a huge difference between 'EXEC
master.dbo.xp_smtp_sendmail' and 'EXEC
linked_server_name.master.dbo.xp_smtp_sendmail'...
A|||BTW, I submitted an enhancement request about this issue. You can vote on
it if you have a passport and feel strongly enough about it to have it
addressed in a future version of SQL Server:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264498
A

database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

database mail to send mail to multiple recipient from table

I am using database mail to send emails to our Lotus Notes SMTP server using sp_send_dbmail. I want to accomplish the following.

I have maintained department-wise users email address in one table . Now I want to send mail to one particular department and there can be 1-15 users as recipient for that mail. How can I do that using sp_send_dbmail?

Well, I have found answer to it. The following way, we can accomplish. Hope that will help those, who are searching for something similar.

DECLARE @.email VARCHAR(4000)
SET @.email = ''
SELECT @.email = RTRIM(@.email) + RTRIM(email) + ';'
FROM Users
WHERE email <> '' AND DepCode = 'A'
PRINT @.email

EXECUTE msdb.dbo.sysmail_add_account_sp
@.account_name = 'custoerders',
@.description = 'Customer Address Account',
@.email_address = @.email
@.mailserver_name = 'mail.anywhere.com'

Database Mail SMTP Settings

Hi All,
The Sql server will send emails when I am connected to it on the LAN but not
when I am remote or connected via a VPN. I am using the fully qualified
hostname for the smtp server in the database mail configuration.
Here is the error message:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 3 (2007-06-08T16:36:07). Exception
Message: Cannot send mails to mail server. (The operation has timed out.).
Thanks for your help.
JN
On Jun 11, 4:11 pm, "JN" <j...@.ecconsulting.com> wrote:
> Hi All,
> The Sql server will send emails when I am connected to it on the LAN but not
> when I am remote or connected via a VPN. I am using the fully qualified
> hostname for the smtp server in the database mail configuration.
> Here is the error message:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 3 (2007-06-08T16:36:07). Exception
> Message: Cannot send mails to mail server. (The operation has timed out.).
> Thanks for your help.
> JN
Hi
Can you ping/telnet the mail server?
Has the server been set to exclude connections via VPNs?
John
|||It looks like the problem is caused by the firewall blocking port 25 on the
smtp server.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1181581014.987361.279000@.q69g2000hsb.googlegr oups.com...
> On Jun 11, 4:11 pm, "JN" <j...@.ecconsulting.com> wrote:
> Hi
> Can you ping/telnet the mail server?
> Has the server been set to exclude connections via VPNs?
> John
>

Database Mail SMTP Settings

Hi All,
The Sql server will send emails when I am connected to it on the LAN but not
when I am remote or connected via a VPN. I am using the fully qualified
hostname for the smtp server in the database mail configuration.
Here is the error message:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 3 (2007-06-08T16:36:07). Exception
Message: Cannot send mails to mail server. (The operation has timed out.).
Thanks for your help.
JNOn Jun 11, 4:11 pm, "JN" <j...@.ecconsulting.com> wrote:
> Hi All,
> The Sql server will send emails when I am connected to it on the LAN but not
> when I am remote or connected via a VPN. I am using the fully qualified
> hostname for the smtp server in the database mail configuration.
> Here is the error message:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 3 (2007-06-08T16:36:07). Exception
> Message: Cannot send mails to mail server. (The operation has timed out.).
> Thanks for your help.
> JN
Hi
Can you ping/telnet the mail server?
Has the server been set to exclude connections via VPNs?
John|||It looks like the problem is caused by the firewall blocking port 25 on the
smtp server.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1181581014.987361.279000@.q69g2000hsb.googlegroups.com...
> On Jun 11, 4:11 pm, "JN" <j...@.ecconsulting.com> wrote:
>> Hi All,
>> The Sql server will send emails when I am connected to it on the LAN but
>> not
>> when I am remote or connected via a VPN. I am using the fully qualified
>> hostname for the smtp server in the database mail configuration.
>> Here is the error message:
>> The mail could not be sent to the recipients because of the mail server
>> failure. (Sending Mail using Account 3 (2007-06-08T16:36:07). Exception
>> Message: Cannot send mails to mail server. (The operation has timed
>> out.).
>> Thanks for your help.
>> JN
> Hi
> Can you ping/telnet the mail server?
> Has the server been set to exclude connections via VPNs?
> John
>

Database Mail Formatting

I created the following, which emails out a query from a SQL 2005 database.

It works fine, but is there anyway to change the formatting to look more like a table?

Such as:

ESSSEQUENCE: 2000

MaterialType: 900

ESSPrefix: 01

PartDesc: Test

Project: 2833

PM: Jeff House

Here's the following code I'm using that sends the data as text and you can't tell what column heading goes with the data because it's kind of all over the place.

Any help would be appreciated..

declare @.q as varchar(255)

select @.q = 'select ESSSequence, MaterialType, ESSPrefix, PartDesc, Project, PM

from ESSPartNumLog

where ESSSequence = (SELECT MAX(esssequence) FROM esspartnumlog) '

exec msdb.dbo.sp_send_dbmail

@.recipients = 'tengel@.pivotint.com',

@.Profile_name = 'DatabaseEmail2',

@.subject = 'New record added in ESSPartNumLog',

@.query = @.q,

@.execute_query_database = 'Engineering'

You should be able to concat everything together into one string with formatting..something like this (if you've got any fields that are not strings you may have to cast or convert)

select 'ESSSequence:' + Esssequence + Char(13) + Char(10) + 'MaterialType:'+ MaterialType + Char(13) + Char(10)

etc....

RKS

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

Database mail attachments filling up the system drive!

Hi all,

Has anyone run into this issue before?

My company uses database mail extensively for alerting and the like, and most emails sent have attachments. We've put in place procedures for removing emails older than a month from the MSDB database, however we've now had a client server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?!

When I check the sysmail_delete_mailitems_sp sproc, it only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Does anyone know why SQL Server stores them in the temp directory of the service account user, as well as how we can remove them (apart from manually deleting the files), as we need to have an automated process for cleaning up emails, and at the moment SQL Server 2005 doesn't handle this very well.

I've checked this on our test server as well, and we're getting the same there, and this server is running the latest SP2 + hot fixes.

Thanks,

Reece.

It happens because when you attach a file to an email, the email system makes a copy of the file to attach -so that if the file is in use, or even deleted, the email attachment will send without error.

However, then temp folder is not being cleared -normally that happens when the user logs out. Since the mail account belongs to a service account, it is not logging out.

You may wish to create a SQL Agent job (or a Windows Scheduler task) to run daily and delete files older than one day.

|||

Thanks for the info. We're in the process of testing a job to remove the files, but it would be definitely nice if SQL Server cleaned up these files itself when it does the DB Mail cleanup on the database.

Cheers,

Reece.

Database mail attachments are filling the hard disk

My company started using database mail recently.Most emails sent have relatively large attachments. We've set up procedures for removing emails older than a given period of time from the MSDB database, however we've now had a server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?! sysmail_delete_mailitems_sp sproc only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.

Somebody claims that those temp files are needed in case the file being attached is deleted before it has been sent for example. I'm agree with the procedure, but... Why SQL server does not delete the temporary file after a sucessfull delivery of the message?

It is not too hard to code some job to delete these files daily, but ... Is it too hard for Microsoft Team to develop this simple functionality?

Thanks,

Philip

I think that temp directory is associated to operating system for such actions its better to code a seperate steps to drop those temp files too. Also if you have any anti-virus or anti-spyware tool installed on the server then these will be in exclusive usage hence operating system may not delete them, hence the explicit deletion is required.

I feel this is a good feature to have, you could provide such feedback on http://connect.microsoft.com site in thsi case to involve on next Service pack or in SQL 2008 release.

Database Mail and Sent Items folder

We are using SQL2005 Database Mail and sending emails using our Exchange
Server as the smtp server. There is an account set up in exchange that
Database Mail uses and it works great.
However, is there a way to have all emails that that account sends put into
the "Sent Items" folder for that account?
Any assistance would be greatly appreciated.
Sincerely,
Glen WolinskyHello Glen,
You may want to bcc to this account and configure rule for the account when
receing mail from itself, it routed it to sent item folder.
Or, you may want to use SQL mail which uses MAPI so that Exchange will save
sent items automatically.
Since the issue is related to Exchange, you may also ask in Exchange
newsgroup so that you may get more qualified answer.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Database Mail and Sent Items folder

We are using SQL2005 Database Mail and sending emails using our Exchange
Server as the smtp server. There is an account set up in exchange that
Database Mail uses and it works great.
However, is there a way to have all emails that that account sends put into
the "Sent Items" folder for that account?
Any assistance would be greatly appreciated.
Sincerely,
Glen WolinskyHello Glen,
You may want to bcc to this account and configure rule for the account when
receing mail from itself, it routed it to sent item folder.
Or, you may want to use SQL mail which uses MAPI so that Exchange will save
sent items automatically.
Since the issue is related to Exchange, you may also ask in Exchange
newsgroup so that you may get more qualified answer.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.