From: dbuchanan on
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(a)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?

From: John Bell on
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(a)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?
>
>
From: stoney on
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(a)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?
> >
> >