April 27, 2011 at 3:54 pm
If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.)Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.)Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
--sysmail_stop_sp
--sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
April 27, 2011 at 5:23 pm
Nice compilation of scripts to use for debugging and working with dbmail. Thanks for posting it, although I think you are trying to rob us of 'fun'. That doesn't sound nice.:-)
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply