December 6, 2016 at 8:35 am
I used a script that I was able to successfully install on multiple servers but I can't get it to work on my pc.
I get the following error:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2016-12-06T09:33:54). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available).
)
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 8:43 am
what script did you use......?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
December 6, 2016 at 8:44 am
Wait do you mean this is an instance of SQL Server installed on your personal PC that you are trying to run this against?
My guess would be that the network is not configured to allow your personal PC to connect to the mail server.
December 6, 2016 at 9:25 am
the error message you pasted was
The server response was: 4.3.2 Service not available,
which would be an SMTP service exists, but is not available.
so the issue is not database mail, but whatever settings you are entering in are not for a valid, working SMTP server.
do you have the right smtp server? are you pointing to an external FQDN mx record, or some internal server on your network?
Lowell
December 6, 2016 at 9:57 am
J Livingston SQL (12/6/2016)
what script did you use......?
--work file
--USE msdb
--GO
--exec dbo.sysmail_start_sp
--exec dbo.sysmail_stop_sp
--use master
--go
-- exec sp_configure 'show advanced options', 1
-- reconfigure
-- exec sp_configure 'Database Mail XPs', 1
-- reconfigure
SELECT * FROM msdb.dbo.sysmail_profile p
SELECT * FROM msdb.dbo.sysmail_account
USE msdb
GO
DECLARE @ProfileName VARCHAR(35)
DECLARE @AccountName VARCHAR(35)
SET @ProfileName = 'Database_Mail_Profile'
SET @AccountName = '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_id
WHERE
p.name = @ProfileName AND
a.name = @AccountName)
BEGIN
PRINT 'Deleting Profile Account'
EXECUTE sysmail_delete_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName
END
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
BEGIN
PRINT 'Deleting Profile.'
EXECUTE sysmail_delete_profile_sp
@profile_name = @ProfileName
END
IF EXISTS(
SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
BEGIN
PRINT 'Deleting Account.'
EXECUTE sysmail_delete_account_sp
@account_name = @AccountName
END
--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Account',
@description = 'Mail account used by my local PC.',
@email_address = 'XYZ.com',
@replyto_address = 'XYZ.com',
@display_name = 'Asset2426\SQL2014',
@mailserver_name = 'relay-1.msexch.XXX.com',
@port = 25,
@username = 'XXX.com',
@password = 'XXX',
@enable_ssl = 1
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database_Mail_Profile',
@description = 'Profile used for Database Mail'
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Database_Mail_Profile',
@account_name = 'Account',
@sequence_number =1
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Database_Mail_Profile',
@principal_name = 'public',
@is_default = 1
--Send mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'XXX.com',
@body= 'Test Email Body After Start',
@subject = 'Test Email Subject',
@profile_name = 'Database_Mail_Profile'
exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
USE msdb
GO
EXEC sp_who2
EXEC sp_send_dbmail @profile_name='Database_Mail_Profile',
@recipients='XXX.com',
@subject='Test message Unsecure',
@body='This is the body of the test message.'
USE msdb
GO
EXEC sysmail_help_queue_sp @queue_type = 'Mail' ;
SELECT *
FROM sysmail_faileditems
WHERE profile_id = 7
SELECT *
FROM sysmail_mailitems
WHERE profile_id = 7
SELECT items.subject,
items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2013-02-13T12:05:55). Exception Message: Cannot send mails to mail server. (Server does not support secure connections.). )
GO
SELECT *
FROM sysmail_log
GO
SELECT *
FROM sysmail_sentitems
SELECT *
FROM sysmail_allitems
SELECT *
FROM sysmail_event_log
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
EXEC sysmail_help_queue_sp @queue_type = 'Mail' ;
SELECT *
FROM sysmail_event_log
SELECT *
FROM sysmail_allitems
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
SELECT *
FROM msdb.dbo.sysmail_allitems
where sent_status NOT IN('failed','sent')
--declare @mailid int
--EXECUTE [msdb].[dbo].[sp_send_dbmail]
-- @profile_name = 'Database_Mail_Profile'
-- ,@recipients = 'XXX.com'
-- ,@body = 'This is a test e-mail sent from Database Mail on XXX\DEV.'
-- ,@subject = 'Database Mail Test'
-- ,@mailitem_id = @mailid OUTPUT
--select @mailid
--Send mail with attachment
--EXEC msdb.dbo.sp_send_dbmail
-- @profile_name = 'DBMail'
-- ,@recipients = 'GroupSQLDBA@MyCo.com'
-- ,@from_address = 'DBMail@MyCo.com'
-- ,@query = 'SELECT resource_type, resource_database_id,
-- request_mode, request_session_id
-- FROM sys.dm_tran_locks
-- WHERE request_mode IN (''IX'', ''X'')'
-- ,@subject = 'Exclusive Locks'
-- ,@attach_query_result_as_file = 1 ;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 10:03 am
ZZartin (12/6/2016)
Wait do you mean this is an instance of SQL Server installed on your personal PC that you are trying to run this against?My guess would be that the network is not configured to allow your personal PC to connect to the mail server.
Agreed. How do I test?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 10:05 am
Lowell (12/6/2016)
the error message you pasted wasThe server response was: 4.3.2 Service not available,
which would be an SMTP service exists, but is not available.
so the issue is not database mail, but whatever settings you are entering in are not for a valid, working SMTP server.
do you have the right smtp server? are you pointing to an external FQDN mx record, or some internal server on your network?
Gotch ya. My guess is that my PC is not authorized to connect to the SMTP Server. Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 10:08 am
just read and work through your errors; you seem to be changing stuff pretty quick to try and get it to work.
your latest error is just because you checked the enable_ssl bit int he script:
Cannot send mails to mail server. (Server does not support secure connections.).
@mailserver_name = 'relay-1.msexch.XXX.com',
@port = 25,
@username = 'XXX.com',
@password = 'XXX',
[highlight="#ffff11"]@enable_ssl = 1[/highlight]
Lowell
December 6, 2016 at 10:40 am
Lowell (12/6/2016)
just read and work through your errors; you seem to be changing stuff pretty quick to try and get it to work.your latest error is just because you checked the enable_ssl bit int he script:
Cannot send mails to mail server. (Server does not support secure connections.).
@mailserver_name = 'relay-1.msexch.XXX.com',
@port = 25,
@username = 'XXX.com',
@password = 'XXX',
[highlight="#ffff11"]@enable_ssl = 1[/highlight]
I have changed that but I get a status 03 3 retry.
🙁
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply