December 5, 2011 at 2:54 pm
I have gone through a few resources including
http://technet.microsoft.com/en-us/library/ms187540.aspx
and
http://technet.microsoft.com/en-us/library/ms190630.aspx
and am unable to find out where I am missing setup for dbmail.
End goal is to send an email through from a development box to my email. The configuration script is the same as used for another box I was able to setup.
I use the below setup script (values scrubbed with aaa and me)
DECLARE @ProfileName VARCHAR(255)
DECLARE @ProfileDesc VARCHAR(255)
DECLARE @AccountDesc VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @AccountReplyTo VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
SET @ProfileName= 'aaa';
SET @ProfileDesc= 'aaa';
SET @AccountName= 'aaa';
SET @AccountDesc= 'aaa';
SET @AccountReplyTo = 'aaa';
SET @SMTPAddress= 'aaa';
SET @EmailAddress= 'aaa';
SET @DisplayUser= 'aaa';
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName,
@description = @ProfileDesc
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@description = @AccountDesc,
@email_address = @EmailAddress,
@replyto_address = @AccountReplyTo,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @ProfileName,
@principal_id = 0,
@is_default = 1 ;
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'me',
@body= 'aaa',
@subject = 'aaa'
SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM msdb.dbo.sysmail_account
This was done after I reconfigured database mail:
sp_configure 'Database Mail XPs', 1
go
reconfigure
go
sp_configure
I am on 2005 SP4 and went in through the surface area configuration manager and saw that database mail was enabled.
I ran the following to comare to production which sends just fine:
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
When I ran
EXECUTE dbo.sysmail_help_status_sp;
The results came back as "STARTED" though there were no entries in msdb.dbo.sysmail_event_log; so I ran the command execute dbo.sysmail_start_sp and there were then entries as well as a change in date time result stamps from running EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'.
There are no details in any log seen on windows events, security, or application as well as SQL log, or database mail log.
Executing "select top 20 * from sysmail_mailitems" returns a bunch of records with 0 for sent_status. I have made several attempts to get a message sent, as why there are multiple records. The only thing I have yet to do that I know if is restart services (I am waiting for other developers to not need the box and am hoping I am missing the resolution blindly). Service broker is enabled for the MSDB database. On production, I had no need to add a role member and on the dev box I have sysadmin credentials as well as had logged in using sa, incase it may help. I also added the member role to m user just in case it may kick something through.
I have granted access to the dbmail exe to the service account running the sql engine as well.
Anyone see where I am missing the setup issue? Anywhere else to look over?
I had the Network administrator check the open port in communication for the exchange server and everything looked good to go.
December 6, 2011 at 11:14 am
Additional information (still no resolution found in searches):
There are two instances on the box.
There was a third instance on the box for 32 bit version (others are 64 bit) that was uninstalled.
At this time, registry access was given in full to the service account at the SQL server registry level on local system
Full permission was given to service account for file system including db mail 90 exe file.
I had enabled sql agent mail profile and am finally getting an error in a log, but it leads to no solution at this time (below).
Message
[355] The mail system failed to initialize; check configuration settings
I have gone directly onto the sql test box via RDP to try setup directly on the box, to no avail.
I have restarted the machine.
I have restarted the instances I have been trying to get this to work with, several times.
I have set database mail logging to verbose.
I have turned off service broker and re-enabled for MSDB.
In sysmail_log, I am not seeing the mail queue being started comments until I try to manually use the command to start the queue. After starting the queue manually although I see the log entry, there is no progress. sysmail_help_queue_sp from one instance keeps a state of Notified. The other instance keeps the state of Inactive.
A query from sysmail_allitems continues to state sent_status of "unsent".
I have not checked to see if SMTP services were installed on the machine, but have not because I am still trying to figure out any logging I can find on why the queue just sits there.
I have not examined (http://msdn.microsoft.com/en-us/library/bb326746.aspx) as I do not have another box to test from. The production and DR servers are to remain untouched.
What actually made SOME progress is when I saw this (http://www.sqlservercentral.com/Forums/Topic581589-324-1.aspx) and double clicked the databasemail90.exe file itself. FINALLY something got logged BUT, it did not tell me why the server could not activate or use that file to begin with.
Out of the two instances, this only kicked of on the first instance I had been working on. The one that was at status Inactive for sysmail_help_queue_sp. The instance with status Notified, did nothing. I probably messed something up on that so I will back track a little for that instance. But, I am still stuck as to why the mail exe was not launching from the service.
December 6, 2011 at 11:24 am
Although I had removed and reconfigured previously from what was seen above, now after manually clicking the exe and once again deleting everything in configuration ON THE LOCAL system, everything seems like the instance just wants to behave now.
Working. I am removing a bunch of stuff to see what I can avoid having for permission setting, then resetting the box. Seems like the .exe needed a kick.
December 7, 2011 at 4:56 am
check database mail log and report the issue.
if it is SMTP server IP or server name authentic to send emails from your DB server.
Regards
Durai Nagarajan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply