April 2, 2012 at 7:52 am
I can't get Database Mail to work on a New Server that I configured.
I have tried everything.
The message stays in the Queue and is never sent.
I have a trace running to monitor an unrelated issue and I notice that the Database Mail Application is kicking when I'm not even trying to run it.
Any ideas?
When I try running the following on that server I get the error listed below:
DECLARE @From varchar(100)
DECLARE @To varchar(100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar(4000)
DECLARE @cc varchar(100)
DECLARE @BCC varchar(100)
SET @cc = Null
SET @BCC = Null
DECLARE @MailID int
DECLARE @hr int
SET @From = 'jblow@.com'
SET @To = 'jblow@.com'
SET @Subject = 'Testing'
SET @Body = 'Testing'
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OASetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
How do I turn on sys.sp_OAMethod?
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/
April 2, 2012 at 7:56 am
this will do the trick
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Ole Automation Procedures',1
go
reconfigure
go
April 2, 2012 at 7:59 am
I just turned it on but no e-mail?
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/
April 2, 2012 at 8:05 am
is it the same error or a different one?
also is that email address right in the snippet or was it modified?
is there anything in the database mail log?
av blocking mass mail worm?
smtp server not configured for your server to connect to?
authentication issues?
firewall issues/blocking port 25?
April 2, 2012 at 8:18 am
anthony.green (4/2/2012)
is it the same error or a different one?also is that email address right in the snippet or was it modified?
is there anything in the database mail log?
av blocking mass mail worm?
smtp server not configured for your server to connect to?
authentication issues?
firewall issues/blocking port 25?
I tried the code on a Server that I configured Database mail on a few ago.
When I run the Code I get command completed sucessfully.
When you say database mail log are you refering to 'sysmail_log'?
There are start and stop completed sucessfully in sysmail_log.
I can send mail from this server using telnet.
I don't know about av.
There should not be any authenication issues, local admin, sysadmin.
I need to verify that port 25 is not blocked.
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/
April 2, 2012 at 8:59 am
I sent email via telnet using port 25.
If it was an AV Issues then it should block an email sent via telnet.
I don't know what else to test or check for?
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/
April 2, 2012 at 9:01 am
only other thing I can think of now is permissions to the procedures
what level of access is the user which is running the mail task?
April 2, 2012 at 9:11 am
anthony.green (4/2/2012)
only other thing I can think of now is permissions to the procedureswhat level of access is the user which is running the mail task?
sysadmin.
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/
April 2, 2012 at 10:59 am
I starting to believe that it is a Windows Problem since CDONTS Fails?
I'm using SQL Server 2008 R2 x64 on Windows Server 2008 R2 x64.
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/
April 2, 2012 at 12:02 pm
Forgive me for asking a basic question but did you ever enable Database Mail? sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
April 2, 2012 at 12:07 pm
dan-572483 (4/2/2012)
Forgive me for asking a basic question but did you ever enable Database Mail?sp_configure 'show advanced options',1go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
Yes, I have tried every troubleshooting step listed when you test Database mail and it does not send.
Database Mail is enabled and started.
Message go into the queue but they are never sent.
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/
April 2, 2012 at 12:10 pm
anthony.green (4/2/2012)
is it the same error or a different one?
No error.
anthony.green (4/2/2012)
also is that email address right in the snippet or was it modified?
It was modified.
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/
April 2, 2012 at 12:58 pm
Have you changed any of the service logins outside of SQL Server Configuration Manager? If you have, they may not have the required permissions...
Jared
CE - Microsoft
April 2, 2012 at 1:10 pm
SQLKnowItAll (4/2/2012)
Have you changed any of the service logins outside of SQL Server Configuration Manager? If you have, they may not have the required permissions...
No but that makes think that perhaps a permission issue.
What services do I need to be concerned with?
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/
April 2, 2012 at 1:16 pm
I changed the Account for the SQL Server and SQL Server Agent Account to an account that it should work with and tried to send a message but all of the message are still queued.
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 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply