October 27, 2016 at 7:00 am
Hello,
I hope you can help. I am using Microsoft SQL Server 2014 - 12.0.2000.8 (X64). I managed to get emailing from SQL working from home (SQL standard) but Unfortunately in my work I am limited to using SQL Express so I couldn't use the built in database mail program so I followed the guide from this link on this website --> Link To Article[/url] <--. It wouldn't let me comment under this link so I thought i'd make a new post on this subject.
All worked expect i am not receiving any emails. When I checked the logs SELECT * FROM msdb.dbo.sysmail_log I noticed this message:
" Message: SQL Server blocked access to procedure 'sys.sp_PostAgentInfo' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online."
Attempted fix
I ran these commands which are supposed to fix this issue but no luck. I have to use WITH OVERRIDE - Perhaps this is the issue? But i wouldn't think so.
--Configure
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Any help/suggestions would be greatly appreciated.
Thanks
Jamie
Full Error Message Below
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Mail configuration information could not be read from the database.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object obj)
2) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 16
LineNumber: 1
Number: 15281
Procedure: sp_PostAgentInfo
Server: ECL-APP-04\SQLEXPRESS
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: SQL Server blocked access to procedure 'sys.sp_PostAgentInfo' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
October 27, 2016 at 7:08 am
database mail requires the sql server agent, which express does not have
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2016 at 7:26 am
I tried turning off the SQL server Agent on my home machine and sent an email which worked.
EXEC msdb.dbo.sp_send_dbmail @profile_name='database email',
@recipients='GaffneyJ@Eclipseblinds.co.uk',
@subject= 'TEST',
@body=' TEST'
The link below says this is possible. I am not planning to use the SQL server agent only the above query to send the email. If it's 100% not possible i'll ask my manager again if we can get SQL Server Standard.
http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/02/sending-mail-using-sql-server-express-edition/[/url]
October 27, 2016 at 8:06 am
Microsoft doesn't allow DB Mail for express edition:
https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
The article you linked to was from 2013 so maybe that workaround was broken in 2014 version.
There are other alternatives possible though, like using a CLR assembly:
PowerShell scripts can also handle querying and e-mailing.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply