Setting up dbmail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting up dbmail

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-03-01 : 03:32:28

Hi all

I'm trying to set up dbmail on a local server so that we can notify various groups of people when certian jobs are done.
I've gone through and set up the mail account (it's a group account that we use to send emails from anyway so I copied the details from that).

When I run the script, it queues the mail but I get the following error fromm the sysmail_log table:-

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 6 (2012-03-01T08:28:18). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 62.130.254.20:25). )

I'm attempting to use the SMTP server we use for emails normally but I don't know if that's correct (the IP address and port number are for the email SMTP server).

Does anyone have any idiot-proof instructions (I've used the details I found via Google).?

Does our SQL server need it's own SMTP server or can I do it the way I'm trying to?

All help gratefully received.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-03-16 : 09:51:01

Right, got this set up and working (it was actually a setting in McAfee that was blocking everything but that's now sorted).

What I want to do now (if possible) is pass the query parameters for:-
recipients - this will a semi-colon delimited list
subject - a short string
body - a slightly longer string

This is my query as it stands:-


exec msdb.dbo.sp_send_dbmail
	@profile_name='Profile'
	,@recipients = 'my email address'
	,@subject = 'test from sql'
	,@body =  'if you can see this SQL mail is working !!!!!'

I've edited the profile name and my email address for obvious reasons.

Anyone any ideas how this can be done, or even if it's possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:03:51

are recipients details stored in some table? or is it in a delimited file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-03-16 : 11:40:14

The recipients will be stored as a field in a table in the correct format (semi-colon delimited) as will the subject and body.
I just need a way of passing to the SP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:51:08

see this

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources