InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:20:37 GMT20http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspx[quote][b]Massan (3/25/2013)[/b][hr]do you have any server Database mail does work properly? if you do you may try and compare the configurations differences and if you could not find anything, you may have to reinstall the server(sql server)[/quote]I would avoid reinstalling SQL Server if at all possible.Can the OP get Database Mail working on any Server?Mon, 25 Mar 2013 10:34:16 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspx...sorry wrong postMon, 25 Mar 2013 09:06:59 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxdo you have any server Database mail does work properly? if you do you may try and compare the configurations differences and if you could not find anything, you may have to reinstall the server(sql server)Mon, 25 Mar 2013 08:39:48 GMTMassanhttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxSometime's it's not the database ;-)Tue, 19 Mar 2013 15:16:21 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxI had the same issue about a month ago and after serveral head banging hours I contacted the mail server guys, the server was not set to send mail and the prot was blocked. Please run OPC's scripts as well! :)Tue, 19 Mar 2013 11:49:54 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxDid you rule out anti-virus email blocking as I suggested? That's the only issue I've had with Database Mail in my environment (We're using McAfee AV).Tue, 19 Mar 2013 11:25:07 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxIn sysmail_faileditems I didn't see any failure.I ran DatabaseMail.exe manullly,because I didn't see any "DatabaseMail process is started" & "DatabaseMail process is shutting down" events in msdb.dbo.sysmail_log table.Even when I e3xecute the DatabaseMail,nothing happened and also I didnt get any error message?Any thoughts?Mon, 18 Mar 2013 13:45:44 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspx[quote][b]Aspet Golestanian Namagerdi (3/4/2013)[/b][hr]In sysmail_sentitems,there is not any record.In sysmail_unsentitems,In includes all the test emails I have triedIn sysmail_event_log does not show that DatabaseMail process has been started to stopped at all.The last two event in this table are(I manullay stopped and started the mail Queue): The mail queue stopped by login... The mail queue started by login...[/quote]Why don't you try seaching:SELECT * FROM msdb.dbo.sysmail_allitems Why haven't you tried opc.three queries?Whay are you trying to execute the DatabaseMail.exe?Tue, 05 Mar 2013 09:27:25 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxDoes the server have anti-virus software on it? Some anti-virus services will block any email that tries to send from a server to protect it from being used to send spam. When I set up a SQL server to use Database Mail, I routinely have to ask our engineer in charge of anti-virus to turn off this blocking on on the server.Check the System & Application logs for messages about emails being blocked that correspond to your attempts to use Database Mail, and ask whoever configures anti-virus in your organization.Tue, 05 Mar 2013 09:20:04 GMTdan-572483http://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxIt was in the first response to your post - OPC.Three gave you a script to runMon, 04 Mar 2013 19:36:02 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxSorry ,I guess I have missed something.What is OPC three script?Mon, 04 Mar 2013 17:26:13 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxWhen I tried to run the DatabaseMail.exe manually in Windows event log I face with the following error message:Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseExceptionMessage: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), connection parameters: Server Name: ., Database Name: msdbMon, 04 Mar 2013 10:32:46 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxI created Account and Profile from the scratch and when I run the following query:SELECT items.subject, items.last_mod_date ,l.description FROM dbo.sysmail_faileditems as itemsINNER JOIN dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_idI didn't get any result.Mon, 04 Mar 2013 10:11:29 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxDid you try running opc.three's Scripts?Mon, 04 Mar 2013 10:08:40 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxFor some reason it seems External program (DatabaseMail.exe) does not been run at all.And when I tried to manually run DatabaseMail.exe, nothing happened.Mon, 04 Mar 2013 09:59:19 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxIn sysmail_sentitems,there is not any record.In sysmail_unsentitems,In includes all the test emails I have triedIn sysmail_event_log does not show that DatabaseMail process has been started to stopped at all.The last two event in this table are(I manullay stopped and started the mail Queue): The mail queue stopped by login... The mail queue started by login...Mon, 04 Mar 2013 09:57:37 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxWe use database mail extensivly to report server information back to a monitoring environment. Typical issues we see are things like the mail server name is wrong or the SQL Service account can't use the mail server, or the mail server hasn't been configured to forward e-mail from the SQL Server.The issue is almost always on the mail server side, and I recommend you get the mail server log checked for errors related to incomming mail from your SQL server.CheersLeoSun, 03 Mar 2013 19:12:12 GMTLeo.Millerhttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxTry executing these queries:[code="sql"]SELECT *FROM sysmail_mailitemsGOGOSELECT *FROM sysmail_logGOSELECT *FROM sysmail_sentitemsSELECT *FROM sysmail_allitemsSELECT * FROM sysmail_event_log [/code]Sun, 03 Mar 2013 17:14:04 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxThanks for the reply.I have already removed all profile and accounts under the profile and recreate it ,but still have the same issue.Sun, 03 Mar 2013 14:59:17 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxI just configured Database Mail on 3 Servers on Friday. Make sure the login you use for the account does not expire.To clean up I would do the following:[code="sql"]USE msdbGODECLARE @ProfileName VARCHAR(35)DECLARE @AccountName VARCHAR(35)SET @ProfileName = 'Database_Mail_Profile'SET @AccountName = 'CYP_DW_Mail_Account'IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_idWHERE p.name = @ProfileName AND a.name = @AccountName)BEGIN PRINT 'Deleting Profile Account' EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountNameEND IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile pWHERE p.name = @ProfileName)BEGIN PRINT 'Deleting Profile.' EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileNameEND IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account aWHERE a.name = @AccountName)BEGIN PRINT 'Deleting Account.' EXECUTE sysmail_delete_account_sp @account_name = @AccountNameEND[/code]I use something like I found in Dan Guzmans Log (names are generic).[code="sql"]-- Enable Database Mail for this instanceEXECUTE sp_configure 'show advanced', 1;RECONFIGURE;EXECUTE sp_configure 'Database Mail XPs',1;RECONFIGURE;GO-- Create a Database Mail accountEXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Primary Account', @description = 'Account used by all mail profiles.', @email_address = 'myaddress@mydomain.com', @replyto_address = 'myaddress@mydomain.com', @display_name = 'Database Mail', @mailserver_name = 'mail.mydomain.com'; -- Create a Database Mail profileEXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Default Public Profile', @description = 'Default public profile for all users'; -- Add the account to the profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Default Public Profile', @account_name = 'Primary Account', @sequence_number = 1; -- Grant access to the profile to all msdb database usersEXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Default Public Profile', @principal_name = 'public', @is_default = 1;GO --send a test emailEXECUTE msdb.dbo.sp_send_dbmail @subject = 'Test Database Mail Message', @recipients = 'testaddress@mydomain.com', @query = 'SELECT @@SERVERNAME';GO[/code]Then I run a number of queries to verify that the e-mail has been sent.The following is one of the queries that I run:[code="sql"]SELECT items.subject, items.last_mod_date ,l.description FROM dbo.sysmail_faileditems as itemsINNER JOIN dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id[/code]I hope this helps!Sun, 03 Mar 2013 10:21:11 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxAlso when I checked msdb.dbo.sysmail_event_log tables,I could not find the following events. DatabaseMail process is startedDatabasemail is shuttingdown It seems databasemail doesnot restart automatically ,when there is message in Message Queue.I checked the Servive Broker one more time and it was enabled.Sun, 03 Mar 2013 09:55:22 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxHere is what I use to monitor Database Mail. Run it in your environment and see if it turns up any useful information.[code="sql"]DECLARE @days_ago_start INT---------------------------------------------------------------------------------------SET @days_ago_start = 7---------------------------------------------------------------------------------------SELECT fi.send_request_date AS send_date, fi.send_request_user AS send_user, fi.recipients, fi.copy_recipients, fi.blind_copy_recipients, fi.[subject], fi.body, fi.sent_status, p.name AS profile_name, fi.body_format, fi.importance, fi.sensitivity, fi.file_attachments, fi.attachment_encoding, fi.query, fi.execute_query_database, fi.attach_query_result_as_file, fi.query_result_header, fi.query_result_width, fi.query_result_separator, fi.exclude_query_output, fi.append_query_error, fi.sent_account_id, fi.sent_date, fi.last_mod_date, fi.last_mod_user, fi.mailitem_idFROM msdb.dbo.sysmail_faileditems fi JOIN msdb.dbo.sysmail_profile p ON fi.profile_id = p.profile_idWHERE fi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE())ORDER BY fi.send_request_date ;--SELECT * FROM msdb.dbo.sysmail_log WHERE mailitem_id IN (9265, 9303)-- failed message logSELECT l.[description] AS log_description, mi.recipients, mi.copy_recipients, mi.blind_copy_recipients, mi.[subject], mi.body, mi.body_format, mi.importance, mi.sensitivity, mi.append_query_error, mi.send_request_date, mi.send_request_user, mi.sent_account_id, CASE mi.sent_status WHEN 0 THEN 'unsent' WHEN 1 THEN 'sent' WHEN 3 THEN 'retrying' ELSE 'failed' END AS sent_status, mi.sent_date, mi.last_mod_date, mi.last_mod_user, N'EXEC msdb.dbo.sp_send_dbmail @profile_name=''' + p.name + N''',@recipients=''' + mi.recipients + N''',@subject=''' + mi.[subject] + N''',@body_format=''' + mi.body_format + N''',@body=''' + mi.body + N'''' AS resend_execFROM msdb.dbo.sysmail_mailitems mi JOIN msdb.dbo.sysmail_log l ON mi.mailitem_id = l.mailitem_id JOIN msdb.dbo.sysmail_profile p ON mi.profile_id = p.profile_idWHERE mi.send_request_date > DATEADD(day, -@days_ago_start, GETDATE()) AND mi.sent_status NOT IN (1, 3)ORDER BY mi.send_request_date DESC ;--SELECT TOP 10-- *--FROM msdb.dbo.sysmail_log--WHERE log_date > DATEADD(day, -1, GETDATE())--ORDER BY log_date DESC[/code]Sun, 03 Mar 2013 07:16:36 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1425902-1550-1.aspxWe have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it has been queued but it does not being delivered.Here are some point about about Database mail configuration1- Database mail is enabled.3- database mail is started4- the status of mail queue is "InActive"5- The size of mail queue is 9025 and status is Inactive and the status queue is empty(length is 0) and it has been actived a long time ago.6- service Broker is enabled.7- When I ran the databasemail.exe manually nothing happened.8- In mail log shows only The mail queue was started or stopped and it does not show any failure or problem.Note : I have one Profile and one account in that profile.I wil be grateful,if someone help me on this.ThanksAspetSat, 02 Mar 2013 14:24:10 GMTAspet Golestanian Namagerdi