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.comwrote in message
news:1177542209.460490.41620@.t39g2000prd.googlegro ups.com...

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

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.googlegro ups.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: 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: 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 SMTP authentication

I am trying to configure my database mail. It gives me this error when ever I try to send an email out of my domain:

The mail could not be sent to the recipients because of the mail server error.... Mailbox unavailable: The server response was: 5.7.1 unable to relay....

Any suggestions on how to fix this issues. I have read other posts here but not a definitive solution.

Thanks,

Hi Rob - You'll probably get more and better responses from another forum or newsgroup. This one is dedicated to SQL Server Notification Services, which despite the similar name, is not the same technology as Database Mail.|||

Thanks Joe,

Do you know which forum might I try to get an answer to this issue.

|||

Hi Rob. Did you ever get an answer to this question? I'm experiencing the same issues.

Thanks!

|||Relaying is not allowed at the most SMTP servers. It will have to be allowed for either an IPAdress as a source of relaying or as destination domains. These settings can be configured on the remote SMTP server. If its an external one, rather than an internal you are probably out of luck as most providers that only host mail addresses will not allow it.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Database Mail SMTP authentication

I am trying to configure my database mail. It gives me this error when ever I try to send an email out of my domain:

The mail could not be sent to the recipients because of the mail server error.... Mailbox unavailable: The server response was: 5.7.1 unable to relay....

Any suggestions on how to fix this issues. I have read other posts here but not a definitive solution.

Thanks,

Hi Rob - You'll probably get more and better responses from another forum or newsgroup. This one is dedicated to SQL Server Notification Services, which despite the similar name, is not the same technology as Database Mail.|||

Thanks Joe,

Do you know which forum might I try to get an answer to this issue.

|||

Hi Rob. Did you ever get an answer to this question? I'm experiencing the same issues.

Thanks!

|||Relaying is not allowed at the most SMTP servers. It will have to be allowed for either an IPAdress as a source of relaying or as destination domains. These settings can be configured on the remote SMTP server. If its an external one, rather than an internal you are probably out of luck as most providers that only host mail addresses will not allow it.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Database Mail question?

Hello All!

So I learned this cool thing today called Database Mail. I have a statement that will allow me to perform a query, and send a email with the results in a table form in a HTML format. What I would like to do is place more than one table. I have several tables to send, I don't want to send 5 emails. I want to send 1 email with 5 tables in it. Here some code!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

Database Mail question?

Hello All!

So I learned this cool thing today called Database Mail. I have a statement that will allow me to perform a query, and send a email with the results in a table form in a HTML format. What I would like to do is place more than one table. I have several tables to send, I don't want to send 5 emails. I want to send 1 email with 5 tables in it. Here some code!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

database mail problem...Query execution failed: Error initializing COM

Hello -

I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.

Send simple messages with the sp_send_dbmail sproc works fine as well.

It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.

Surface config features have database mail on, and SQL Mail off.

Anyone know the solution to this?

Thanks in advance!

Actual SQL ...

use epic
go

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQLMail',
@.recipients = 'rvolters@.whaleyfoodservice.com',
@.body = 'Testing sqlmail with sproc.',
@.subject = 'Orders with invalid tech numbers',
@.query = 'SELECT order_no, order_ext, line_no from epic..STG_BNERPT_INVALIDTECHNOS' ,
@.attach_query_result_as_file = 1 ;

Message Results...

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Investigating the sproc itself shows...

Line 476 in the sproc is the beginning of a 'trap'
--Raise an error it the query execution fails
-- This will only be the case when @.append_query_error is set to 0 (false)
IF( (@.RetErrorMsg IS NOT NULL) AND (@.exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @.RetErrorMsg)
END

RETURN (@.rc)
this is the last section of code in the sproc

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

|||

We have same problem for some time. it has been fixed for us after I made some changes to the security setting and rebooted the server..

We are running SQL 2005 on Windows 2003 SP1. Check the following link

http://support.microsoft.com/default.aspx/kb/926642/en-us

Faeed

database mail problem...Query execution failed: Error initializing COM

Hello -

I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.

Send simple messages with the sp_send_dbmail sproc works fine as well.

It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.

Surface config features have database mail on, and SQL Mail off.

Anyone know the solution to this?

Thanks in advance!

Actual SQL ...

use epic
go

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQLMail',
@.recipients = 'rvolters@.whaleyfoodservice.com',
@.body = 'Testing sqlmail with sproc.',
@.subject = 'Orders with invalid tech numbers',
@.query = 'SELECT order_no, order_ext, line_no from epic..STG_BNERPT_INVALIDTECHNOS' ,
@.attach_query_result_as_file = 1 ;

Message Results...

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Investigating the sproc itself shows...

Line 476 in the sproc is the beginning of a 'trap'
--Raise an error it the query execution fails
-- This will only be the case when @.append_query_error is set to 0 (false)
IF( (@.RetErrorMsg IS NOT NULL) AND (@.exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @.RetErrorMsg)
END

RETURN (@.rc)
this is the last section of code in the sproc

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

|||

We have same problem for some time. it has been fixed for us after I made some changes to the security setting and rebooted the server..

We are running SQL 2005 on Windows 2003 SP1. Check the following link

http://support.microsoft.com/default.aspx/kb/926642/en-us

Faeed

database mail problem...Query execution failed: Error initializing COM

Hello -

I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.

Send simple messages with the sp_send_dbmail sproc works fine as well.

It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.

Surface config features have database mail on, and SQL Mail off.

Anyone know the solution to this?

Thanks in advance!

Actual SQL ...

use epic
go

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQLMail',
@.recipients = 'rvolters@.whaleyfoodservice.com',
@.body = 'Testing sqlmail with sproc.',
@.subject = 'Orders with invalid tech numbers',
@.query = 'SELECT order_no, order_ext, line_no from epic..STG_BNERPT_INVALIDTECHNOS' ,
@.attach_query_result_as_file = 1 ;

Message Results...

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Investigating the sproc itself shows...

Line 476 in the sproc is the beginning of a 'trap'
--Raise an error it the query execution fails
-- This will only be the case when @.append_query_error is set to 0 (false)
IF( (@.RetErrorMsg IS NOT NULL) AND (@.exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @.RetErrorMsg)
END

RETURN (@.rc)
this is the last section of code in the sproc

I have a very similar issue.

When i execute the following everything work 100%.

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'DBA Mail',

@.recipients = me@.whatever.co.za',

@.body = 'Test email from server using dbmail',

@.subject = 'Test email from server using dbmail',

@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;

As soon as i put this exact code in a trigger i get the following error:

Msg 22050, Level 16, State 1, Line 0

Error formatting query, probably invalid parameters

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478

Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

Anyone ?

Database mail in SQL Server 2005 - Email Query Results in Excel format?

Using database mail in SQL Server 2005, I can email the query results in form of a .txt attachment.

EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'userName@.domain.com'
,@.body='Message Body'
,@.subject ='Message Subject'
,@.profile_name ='Mailer Profile'
,@.query ='SELECT * FROM SalesDb.dbo.SalesHistory'
,@.attach_query_result_as_file = 1
,@.query_attachment_filename ='Results.txt'

I want the query results in a CSV or preferably in Excel format. Can I achive that using database mail?

You have several options.

(a) You could use bcp to create the file, and the email the file as an attachment.

(b) You could revise the existing query to create csv formated data like this:

SELECT ''' + ',' + column1 + ',' + Col2 + ',' + {etc} + ''' FROM SalesDb.dbo.SalesHistory

(c) You could use xml to create a xml file that would be easily handled by Excel.

For the xml option, check in Books Online, Topics: OpenXML, FOR XML, Writing XML.

Database Mail fails to email Operator

Sqlserver 2005 SP2
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JN
Try to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:

> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>

Database Mail fails to email Operator

Sqlserver 2005 SP2
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JNTry to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:
> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>

Database Mail does not work from SQL Agent Job

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

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

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

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

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

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

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

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail 101

Question 1: From what I understand, Database Mail can only be used to Send, not Receive, email. Is this correct?

Question 2: I can hear our developers saying "way too complicated. When an error conditon is sensed by our app we can simply have the app send an email to our email server. Done." With that in mind what does Database Mail have to offer our developers that they can't easily do in their app?

TIA,

Barkingdog

Hello,

1: Yes. DBMail can only be used to send.

2: For a start, Dbmail can be used to issue e-mail alerts in response to database-engine related events (job completion, errors etc) as well as including query results in the message. I believe you'd have a hard (time consuming) time incorporating this functionality into an application to the same degree of reliability that DBmail provides out-of-the-box.

Cheers,

Rob

Database Mail - Status sent but email never received

From SQL Management Studion I go to Management > Database Mail and I am trying to send a test email but I never receive anything. I checked my SMTP Mail Server Logs and I saw no entry of my test email.

I also I checked my SQL Database Mail Logs and everything seems fine, no errors are reported.

The msdb.dbo.sysmail_allitems shows my email status as "sent".

So what am I missing? What steps would you recommend for troubleshooting my problem?

Thank you,

Ric

Quick question if you are using Express if yes then your mail was not sent because the MSDN is home to the Agent and the Agent is not included with Express. Hope this helps.|||

No I am not using express, I am using the SQL Server 2005 standard version. Did I post at the wrong place?

Ric

|||

No you did not post in the wrong place but your problem is related to configuration and permissions. I have found two tutorials by SQL Server users check them to see what is missing in your configuration. Hope this helps.

http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp

http://www.databasejournal.com/features/mssql/article.php/3626056

|||

Thanks for your reply Gift. I did exactly what is mentionned in your articles however I get the exact same results. My test emails seems to be sent, no error message, status is mark as sent but no I see no trace of such email on the smtp server.

Issp_send_dbmail waiting for a reply from the SMTP server before to mark the email status as sent? Also if I had a problem of permission I guest I would have some kind of error? Same thing if the port 25 was block I guess?

Ric

|||

(msdb.dbo.sysmail_allitems)

The above from your original post tells me either you and the Agent or just one need permissions in the Server and MSDB database because I don't know why these things are not documented but most auxiliary automation tasks like mail runs out of the Agent database the MSDB database. So try that and post again if you still need help.

|||

Sorry Gift but you are loosing me here, I don't understand what you mean. If you think that I have a permission problem where should I look? Which permission and on what? How to give the proper rights if that is what's missing?

Thanks again,
Ric

|||

(Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.

The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email.)

I got the above from one of the links you need to create account both in your SQL Server and MSDB database and grant it DatabaseMailUserRole public role before your configuration will work. So go to the Security section under management in Management Studio and the new security section within MSDB to create the account with the right permissions. Hope this helps.