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:
[vbcol=seagreen]
> Hi
> I think this is to do with your exchange server's SMTP settings rather tha
n
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
>
Showing posts with label email. Show all posts
Showing posts with label email. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
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:
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?
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:
[vbcol=seagreen]
> 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:
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?
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:
[vbcol=seagreen]
> 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:
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
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
Monday, March 19, 2012
How to set up a regular job which will put the quesry result into file and email
Dear forum people,
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
I am appreciate every answer.
Thank you.
Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.
If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
I am appreciate every answer.
Thank you.
Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.
If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.
Sunday, February 19, 2012
How to send the SQL Reports via email.
Hi Everybody
can somebody please help in finding how to mail the sql server reports via email.
Thanks alot
Nee123
Copy this to help you:
Delivered by
Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.
Report Server E-Mail Settings
Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
Report Server File Share
Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.
The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||Thanks alot guys for your timely help
Best
Nee123
|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.Any clues to how i can get the e-mail delivery option to show up?
Thanks
|||
Please refer to the following link for the problem:
http://msdn2.microsoft.com/en-us/library/ms159155.aspx
How to send the SQL Reports via email.
Hi Everybody
can somebody please help in finding how to mail the sql server reports via email.
Thanks alot
Nee123
Copy this to help you:
Delivered by
Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.
Report Server E-Mail Settings
Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
Report Server File Share
Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.
The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||Thanks alot guys for your timely help
Best
Nee123
|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.Any clues to how i can get the e-mail delivery option to show up?
Thanks
|||
Please refer to the following link for the problem:
http://msdn2.microsoft.com/en-us/library/ms159155.aspx
How to send the SQL Reports via email
Hi Everybody
can somebody please help in finding how to mail the sql server reports via email.
Thanks alot
Nee123
Copy this to help you:
Delivered by
Select the delivery extension to use to distribute the report. Selecting a delivery extension causes other settings to appear. The settings vary depending on the delivery extension you choose. Reporting Services provides Report Server File Share and Report Server E-mail delivery.
Report Server E-Mail Settings
Choosing Report Server E-mail shows delivery options that specify how to deliver a report to a mailbox. To e-mail a report, your report server must be configured for e-mail delivery. E-mail subscriptions provide fields that are familiar to e-mail users (for example, To, Subject, and Priority fields). Specify Include Report to embed or attach the report, and Include Link to include a URL to the report. Render Format to choose a presentation format for the attached or embedded report. For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
Report Server File Share
Choosing Report Server File Share shows delivery options that specify how to deliver a report to a location on the file system. You can deliver any report to a file share. However, reports that support interactive features (including matrix reports that support drill-down to supporting rows and columns) are rendered as static files. You cannot view drill-down rows and columns in a file that is based on a matrix report.
The file share must be specified in Uniform Naming Convention (UNC) format (for example, \\myserver\myreportfiles). Do not include a trailing backslash in the path name. The file name is based on the report name, which you can override by setting the File Name option. The report file will be delivered in a file format that is based on the render format (for example, if you choose Excel, the report is delivered as an .xls file). For more information about how to specify these options, search SQL Server Books Online for the topic How to subscribe to a report.
|||If you use Report Manager, the email extension should be the default option when you select New Subscription. If the email extension is not there check the RSReportServer.config file and see if the configuration info for the email extension contains information for the smtpserver and from element. If not, add the correct information and the email extension will show up in Report Manager.|||Thanks alot guys for your timely help
Best
Nee123
|||Hi I'm having problems getting the Report Server E-mail delivery option to show up I only have the File share option to chose from in my subscriptions. I edited the RSReportServer.config file and when i look at the Reporting Services Configuration Manager it looks like everything is set up correctly. It's using the desired sender address, delivery method = use SMTP server and the desired smtp server is listed.Any clues to how i can get the e-mail delivery option to show up?
Thanks
|||
Please refer to the following link for the problem:
http://msdn2.microsoft.com/en-us/library/ms159155.aspx
How to send report through email one time i.e. without subscription for recurrent emailing?
I want to send a deployed report (report manager), through email, by calling reportingServices webservice. But the requirement is that, time is not fixed for sending this email, so I want to trigger the sending of report as a one time event, no subscription to a daily time...
How should I go about it?
Hello,
As I see it you have two choices, you could either create a one time subscription on the fly (http://technet.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx) or a create a Delivery Extension (http://technet.microsoft.com/en-us/library/ms154050.aspx) that has email as the delivery method. The first option is the easiest and most straight forward. Create a new subscription with a schedule to run the report one time at a time 5 minutes from then.
Good luck,
Larry
How to send notification to multiple email accounts?
I am in process of setting up job failure notification and one of the requirements that I have is to send notification to multiple users. I would appreciate any suggestion. One of the possible ways would be to create some sort of group account on exchange server with all users added as members. Is there anyway though where I could use users' individual accounts and based on that forward notification to them?
Thanks
Which version of sql server is this?
In SQL Server 2005 you can use Database Mail - http://www.sqlserverclub.com/articles/article05.aspx
In SQL Server 2000 try SQL Mail - http://support.microsoft.com/kb/263556
and also you can send mail using SP_OACreate SP
Refer : http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure
Madhu
|||Madhu,
I am sorry for not mentioning in advance that I am using Database Mail in SQL Server 2005 and that is why I am wondering how I could send notification to multiple users from job failure. As I understand using Profile would send email to only one of the accounts listed in that profile. I am quite familiar with Database Mail and I have been using it successfully for almost a year now.
|||Without creating distribution list or group recipient from Exchange Server, I couldn't find any other way to send job failure notification to multiple people. My solution was to create a procedure that collects history of failed job and add it as a last step to my jobs so that whenever job failed it would execute that last step. Within that procedure I used sp_send_dbmail with a list of users email addresses. Solution seem to work brilliant.
Subscribe to:
Posts (Atom)