How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
=B7 I have verified that yes; 'Database Mail' is started in surface
area configuration.
=B7 I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
=B7 I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id =3D 2
principal_name =3D guest
profile_id =3D 2
profile_name =3D TestMailProfile
is_default =3D 1
Why does;
principal_name =3D guest
instead of my user name?
=B7 I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type =3D 'mail' ;
Which showed the state =3D INACTIVE
Why? Shouldn't it be active?
=B7 I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
Which showed 'sent_status' column as "FAILED"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which also shows the same error as above...
=B7 I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
=B7 I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name =3D 'msdb' ;
Which shows that the broker is enabled.
=B7 I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
--
There are no operators with email addresses defined on this server.
--
BUTTONS: OK
--
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:
> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
> Hi
> I think this is to do with your exchange server's SMTP settings rather than
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
> > Hello,
> >
> > I have attempted to send test email from SQL Server Management Studio -
> > Management - Database Mail, without success.
> >
> > Current setup information...
> >
> > System information:
> > The OS is Windows XP SP2.
> > SQL Server 2005 Developer Edition (9.0.2047)
> > IIS is installed and IIS admin service is started
> > SMTP service is installed and SMTP service is started
> > I am logged in as the administrator.
> >
> > The environment:
> > This computer is on a network that has exchange running. This computer
> > also has MS Outlook installed. I know that Database Mail does not need
> > either, but those are the facts.
> >
> > This is my personal network. I am trying to learn how to setup Database
> > Mail. I am trying to set up mail to send email feedback for processes
> > such as database maintenance plans and SSIS processes.
> >
> > These are the steps I have taken to troubleshoot my test email problem:
> >
> > · I have verified that yes; 'Database Mail' is started in surface
> > area configuration.
> >
> > · I ran:
> >
> > USE msdb
> > GO
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > which showed the 'sent_status' column showing "Failed" and the
> > 'last_mod_user' column showing "This is a test e-mail sent from
> > Database Mail on KEPLER"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which showed the column 'description' showing "The mail could not
> > be sent to the recipients because of the mail server failure. (Sending
> > Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> > send mails to mail server. Mailbox unavailable. The server response
> > was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> >
> > · I ran:
> >
> > EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> >
> > Which showed that yes I am a member of DatabaseMailUserRole.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> >
> > Which showed the following values for each column in the one profile;
> >
> > principal_id = 2
> > principal_name = guest
> > profile_id = 2
> > profile_name = TestMailProfile
> > is_default = 1
> >
> > Why does;
> > principal_name = guest
> > instead of my user name?
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_start_sp
> >
> > Which showed that mail is started.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> >
> > Which showed the state = INACTIVE
> > Why? Shouldn't it be active?
> >
> > · I ran:
> >
> > SELECT sent_account_id, sent_date
> > FROM msdb.dbo.sysmail_sentitems ;
> >
> > Which returned no records.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log ;
> >
> > Which showed many errors for the many times I tried to send a test
> > message.
> >
> > The 'description' colums for each of these errors has the same
> > error as shown above.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > Which showed 'sent_status' column as "FAILED"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which also shows the same error as above...
> >
> > · I verified that I am a user in the msdb database, tha I am a member
> > of the DatabaseMailUserRole database role in the msdb database, and
> > that I am a member of sysadmin in the Security Server Roles.
> >
> > · I ran:
> >
> > SELECT is_broker_enabled
> > FROM sys.databases
> > WHERE name = 'msdb' ;
> >
> > Which shows that the broker is enabled.
> >
> > · I ran:
> >
> > USE msdb
> > go
> > EXECUTE dbo.sysmail_help_status_sp ;
> >
> > Which shows that the status is started.
> >
> > When I tried to setup logging for a Maintenance Plan I got the
> > following error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> > There are no operators with email addresses defined on this server.
> > --
> > BUTTONS: OK
> > --
> >
> > What does this mean?
> >
> > I also set up a SSIS project where I tried to have mail inform of
> > success or failure. I got the same error message as above.
> >
> > I trying to trouble shoot this problem I read and follow all I could
> > find on troubleshooting this problem.
> >
> > What am I missing? What should I do next?
> >
> >sql