Configuration of Database Mail

Has anyone been able to configure Database Mail and successfully send an email message? I set it up this afternoon, but all my messages are sitting in the sys_mailitems table of the msdb database.

We use Novell GroupWise as our email client, but we have not installed GroupWise on this server. Can I configure the IIS SMTP server to send the email messages?

These are the steps I have taken thus far:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

GO

EXECUTE msdb.dbo.sysmail_start_sp;

GO

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'CLG_SQLMail',
@.recipients = 'username@.domain.com',
@.body = 'This is only a test. Had this been a real system problem you would already be pulling your hair out. ',
@.subject = 'SQL Server 2005 - Database Mail System Test'

GO

CREATE ENDPOINT SQLMail_Endpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 25 )
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED
)

GO

Thanks in advance,
Ken

After a few weeks of frustrating trial and error, I was able to successfully configure Database Mail. Here are the steps I followed:

Prerequisites:
1) Make sure that IIS is installed
2) Make sure that the SMTP service has been installed. (This service runs within IIS.)
3) Create a valid email account for Database Mail to use

Configuration Steps:
1) Go to the Object Browser and expand the "Management" tree
2) Right-click on "Database Mail" and select "Configure Database Mail" to start the "Database Mail Configuration Wizard"
3) Navigate through the screens of the wizard, entering the appropriate information for your environment. In our case, I use an SMTP relay server as the server for the Database Mail Account.
4) When you are finished with the wizard, go to the Management Studio and connect to the instance.
5) Open a query window and connect to the master database
6) Run the following script:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

7) Run the following script:

EXEC msdb..sysmail_start_sp;

8) Now go to the "SQL Server Configuration Manager"
9) Stop and restart both the SQL Server service and the SQL Server Agent service.

When I first tried to set up Database Mail, I did not restart these services. That was the reason my mail was stuck in the queue. The Books Online article on "sp_configure" needs to specify which of the options requires a restart of the services!

10) Go back to the Management Studio and create a test message using the sp_send_dbmail stored procedure.
11) The message should appear in your Inbox within a minute or two

NOTE: In our environment, we have an alternate email domain. I could only send messages using the primary domain, which I assume is due to the configuration of the SMTP relay server.|||One follow up in regard to restarting services. The following is the order in which you must execute the stored procedures and restart the services whenever you make a change to the Database Mail configuration:

1) Execute the following script in the query window:

EXECUTE msdb..sysmail_stop_sp;

GO

2) When the script completes, stop the following services, in the order listed below:
a. SQL Server Agent
b. SQL Server
c. SMTP Service

3) Restart the following services in the order listed below:
a. SMTP Service
b. SQL Server
c. SQL Server Agent

4) Execute the following script in the query window:

EXECUTE msdb..sysmail_start_sp;

GO

5) Send your test message again to confirm that everything is working correctly

|||Hi,

I have followed all these steps. I still can't send mail. The error message from the table: sysmail_log is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2005-10-14T10:48:48). Exception Message: Cannot send mails to mail server. (The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available ). )

My profile is set to use my Comcast account (cable modem).
Any ideas? Are there any public smtp servers I could use to test database mail?

Regards,
Asim.
|||

Hi Ken,

I was having this same issue.

I resolved my issue by putting in the correct groupwise mail server name and pointing it to use port 25 (default). Be sure that you are using the right mail host server name in your account profile.

I did not have to jump through any extra hoops, it worked right after I made change.

Let me know if that helps.

Thanks,

*T*

Configuration of Database Mail

Has anyone been able to configure Database Mail and successfully send an email message? I set it up this afternoon, but all my messages are sitting in the sys_mailitems table of the msdb database.

We use Novell GroupWise as our email client, but we have not installed GroupWise on this server. Can I configure the IIS SMTP server to send the email messages?

These are the steps I have taken thus far:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

GO

EXECUTE msdb.dbo.sysmail_start_sp;

GO

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'CLG_SQLMail',
@.recipients = 'username@.domain.com',
@.body = 'This is only a test. Had this been a real system problem you would already be pulling your hair out. ',
@.subject = 'SQL Server 2005 - Database Mail System Test'

GO

CREATE ENDPOINT SQLMail_Endpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 25 )
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED
)

GO

Thanks in advance,
Ken

After a few weeks of frustrating trial and error, I was able to successfully configure Database Mail. Here are the steps I followed:

Prerequisites:
1) Make sure that IIS is installed
2) Make sure that the SMTP service has been installed. (This service runs within IIS.)
3) Create a valid email account for Database Mail to use

Configuration Steps:
1) Go to the Object Browser and expand the "Management" tree
2) Right-click on "Database Mail" and select "Configure Database Mail" to start the "Database Mail Configuration Wizard"
3) Navigate through the screens of the wizard, entering the appropriate information for your environment. In our case, I use an SMTP relay server as the server for the Database Mail Account.
4) When you are finished with the wizard, go to the Management Studio and connect to the instance.
5) Open a query window and connect to the master database
6) Run the following script:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

7) Run the following script:

EXEC msdb..sysmail_start_sp;

8) Now go to the "SQL Server Configuration Manager"
9) Stop and restart both the SQL Server service and the SQL Server Agent service.

When I first tried to set up Database Mail, I did not restart these services. That was the reason my mail was stuck in the queue. The Books Online article on "sp_configure" needs to specify which of the options requires a restart of the services!

10) Go back to the Management Studio and create a test message using the sp_send_dbmail stored procedure.
11) The message should appear in your Inbox within a minute or two

NOTE: In our environment, we have an alternate email domain. I could only send messages using the primary domain, which I assume is due to the configuration of the SMTP relay server.|||One follow up in regard to restarting services. The following is the order in which you must execute the stored procedures and restart the services whenever you make a change to the Database Mail configuration:

1) Execute the following script in the query window:

EXECUTE msdb..sysmail_stop_sp;

GO

2) When the script completes, stop the following services, in the order listed below:
a. SQL Server Agent
b. SQL Server
c. SMTP Service

3) Restart the following services in the order listed below:
a. SMTP Service
b. SQL Server
c. SQL Server Agent

4) Execute the following script in the query window:

EXECUTE msdb..sysmail_start_sp;

GO

5) Send your test message again to confirm that everything is working correctly

|||Hi,

I have followed all these steps. I still can't send mail. The error message from the table: sysmail_log is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2005-10-14T10:48:48). Exception Message: Cannot send mails to mail server. (The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available ). )

My profile is set to use my Comcast account (cable modem).
Any ideas? Are there any public smtp servers I could use to test database mail?

Regards,
Asim.
|||

Hi Ken,

I was having this same issue.

I resolved my issue by putting in the correct groupwise mail server name and pointing it to use port 25 (default). Be sure that you are using the right mail host server name in your account profile.

I did not have to jump through any extra hoops, it worked right after I made change.

Let me know if that helps.

Thanks,

*T*

Configuration of Database Mail

Has anyone been able to configure Database Mail and successfully send an email message? I set it up this afternoon, but all my messages are sitting in the sys_mailitems table of the msdb database.

We use Novell GroupWise as our email client, but we have not installed GroupWise on this server. Can I configure the IIS SMTP server to send the email messages?

These are the steps I have taken thus far:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

GO

EXECUTE msdb.dbo.sysmail_start_sp;

GO

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'CLG_SQLMail',
@.recipients = 'username@.domain.com',
@.body = 'This is only a test. Had this been a real system problem you would already be pulling your hair out. ',
@.subject = 'SQL Server 2005 - Database Mail System Test'

GO

CREATE ENDPOINT SQLMail_Endpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 25 )
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED
)

GO

Thanks in advance,
Ken

After a few weeks of frustrating trial and error, I was able to successfully configure Database Mail. Here are the steps I followed:

Prerequisites:
1) Make sure that IIS is installed
2) Make sure that the SMTP service has been installed. (This service runs within IIS.)
3) Create a valid email account for Database Mail to use

Configuration Steps:
1) Go to the Object Browser and expand the "Management" tree
2) Right-click on "Database Mail" and select "Configure Database Mail" to start the "Database Mail Configuration Wizard"
3) Navigate through the screens of the wizard, entering the appropriate information for your environment. In our case, I use an SMTP relay server as the server for the Database Mail Account.
4) When you are finished with the wizard, go to the Management Studio and connect to the instance.
5) Open a query window and connect to the master database
6) Run the following script:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

7) Run the following script:

EXEC msdb..sysmail_start_sp;

8) Now go to the "SQL Server Configuration Manager"
9) Stop and restart both the SQL Server service and the SQL Server Agent service.

When I first tried to set up Database Mail, I did not restart these services. That was the reason my mail was stuck in the queue. The Books Online article on "sp_configure" needs to specify which of the options requires a restart of the services!

10) Go back to the Management Studio and create a test message using the sp_send_dbmail stored procedure.
11) The message should appear in your Inbox within a minute or two

NOTE: In our environment, we have an alternate email domain. I could only send messages using the primary domain, which I assume is due to the configuration of the SMTP relay server.|||One follow up in regard to restarting services. The following is the order in which you must execute the stored procedures and restart the services whenever you make a change to the Database Mail configuration:

1) Execute the following script in the query window:

EXECUTE msdb..sysmail_stop_sp;

GO

2) When the script completes, stop the following services, in the order listed below:
a. SQL Server Agent
b. SQL Server
c. SMTP Service

3) Restart the following services in the order listed below:
a. SMTP Service
b. SQL Server
c. SQL Server Agent

4) Execute the following script in the query window:

EXECUTE msdb..sysmail_start_sp;

GO

5) Send your test message again to confirm that everything is working correctly

|||Hi,

I have followed all these steps. I still can't send mail. The error message from the table: sysmail_log is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2005-10-14T10:48:48). Exception Message: Cannot send mails to mail server. (The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available ). )

My profile is set to use my Comcast account (cable modem).
Any ideas? Are there any public smtp servers I could use to test database mail?

Regards,
Asim.
|||

Hi Ken,

I was having this same issue.

I resolved my issue by putting in the correct groupwise mail server name and pointing it to use port 25 (default). Be sure that you are using the right mail host server name in your account profile.

I did not have to jump through any extra hoops, it worked right after I made change.

Let me know if that helps.

Thanks,

*T*

Configuaration of RS

I am unable to successfully configure RS. It fails at initialization and I get an error while switching panels, most likely caused by retrieving WMI properties.
Details:
Verison of the report server database is either in a format that is not valid or cannot be read. The found version in 'Unknown'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
I am thus unable to deploy a report - Error says that Report Designer is unable to connect to Report Server.................

Thanks for any helpCan you use the config tool to create a new RS database or are you seeing this error when using the config tool? If you are seeing the error in the config tool and unable to create a new DB you should try doing a repair on the Report Server via Add/Remove programs.|||I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.|||

I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.

|||I don't understand where you can not set a report directory. Do you mean a virtual directory? What error are you seeing? Have you checked in ReportServerService<timestamp>.log file to see if there are any errors?|||I was able to successfully configure the report server after deleting the old directory in IIS that conflicted with it. Now when I try to access the report server via http:/localhost/reportserver my browser states that it is unable to access the IIS Metabase. I have checked to make sure that I have read permissions for that site.|||

Hi All,

I am getting the following error in reporting services configuration. Could someone please help out.

1) The status of report server is running but initilized found to be No.

2) The report server virtual directory and report manager virtual directory is in Green.

There was an error while switching panels. The most likely cause is an error retrieving WMI properties. The Exception details are : An internal error occurred on the report server.See the error log for more details. ( rsInternalError) "

Could someone please give pointers where in the problem is occuring asap.

Thanks so much,

Kushal

Configuaration of RS

I am unable to successfully configure RS. It fails at initialization and I get an error while switching panels, most likely caused by retrieving WMI properties.
Details:
Verison of the report server database is either in a format that is not valid or cannot be read. The found version in 'Unknown'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
I am thus unable to deploy a report - Error says that Report Designer is unable to connect to Report Server.................

Thanks for any helpCan you use the config tool to create a new RS database or are you seeing this error when using the config tool? If you are seeing the error in the config tool and unable to create a new DB you should try doing a repair on the Report Server via Add/Remove programs.|||I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.|||

I am seeing the error during configuration. I tried to repair, then reinstalled. As I tried to reconfigure again - I was unable to specify a report directory as well.

|||I don't understand where you can not set a report directory. Do you mean a virtual directory? What error are you seeing? Have you checked in ReportServerService<timestamp>.log file to see if there are any errors?|||I was able to successfully configure the report server after deleting the old directory in IIS that conflicted with it. Now when I try to access the report server via http:/localhost/reportserver my browser states that it is unable to access the IIS Metabase. I have checked to make sure that I have read permissions for that site.|||

Hi All,

I am getting the following error in reporting services configuration. Could someone please help out.

1) The status of report server is running but initilized found to be No.

2) The report server virtual directory and report manager virtual directory is in Green.

There was an error while switching panels. The most likely cause is an error retrieving WMI properties. The Exception details are : An internal error occurred on the report server.See the error log for more details. ( rsInternalError) "

Could someone please give pointers where in the problem is occuring asap.

Thanks so much,

Kushal

sqlsql