How to setup Database Mail for SQLS 2005
in [Server Configuration]
|
From: dbuchanan on 6 Nov 2006 11:06 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 6 Nov 2006 12:34 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 6 Nov 2006 12:40 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? > > > >
|
Pages: 1 Prev: TDS_RET_STATUS error Next: Information came from sql error log. |