April 2, 2012 at 3:17 pm
SQLKnowItAll (4/2/2012)
Yup! Stepping away and observing because this is clearly not what I thought... 🙂
I think digging at the setup for db_mail is fine; we've got to get Welsh from getting distracted with other error messages and stay on track with db_mail for now.
It seems to me the issue's more related to the service broker not processing the emails in the queue; He's said that he's stopped and started the service, which is how he'd fixed a very similar issue in the past in another thread.
I'd like to see the results of some of the broker related DMV's for more information;
Service Broker Related Dynamic Management Views (Transact-SQL)
How to troubleshoot Service Broker problems
Lowell
April 2, 2012 at 3:22 pm
I would be curious the value for is_broker_enabled in sys.databases for this database.
April 2, 2012 at 3:34 pm
Lowell,
Sorry that I created a new post. I could not find the original. So I'm wrong for that but I'm not lying.
Yes, it is the same Server and it is the only server that I'm having an issue.
I never posted anything on the other server because they worked.
The reason for the delay is that it is just a Dev/QA Server and only one other person other than I are using it and I had
I have three instance and it does not work on either one.
I used the same script to to create the Account and the profile.
This is the 1st Server using 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 3:35 pm
It was not my intent to use CDONTS as an alternative.
I just wanted to see if it worked.
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/
April 2, 2012 at 3:37 pm
SQLKnowItAll (4/2/2012)
Hmm... Can you post the results of sp_configure?
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
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 3:40 pm
Lynn Pettis (4/2/2012)
Lowell (4/2/2012)
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
There's a bigger picture here, it's hard to track;
Wesh Corgi added a new server, and database mail's not working on the new server, but is fine on many of his other servers; he had a couple of other threads where he was wrestling with trying to get it to work.
this thread is an attempt to get any kind of email working, clearly out of frustration, as he moved off of db_mail and is trying cdonts as an alternative.
Any chance of getting these consolidated so that we aren't flailing around trying to figure out what is going on?
http://www.sqlservercentral.com/Forums/Topic1263714-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1271710-1550-1.aspx
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 3:43 pm
Lynn Pettis (4/2/2012)
I would be curious the value for is_broker_enabled in sys.databases for this database.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
Returns:
is_broker_enabled
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/
April 2, 2012 at 4:00 pm
Lowell (4/2/2012)
SQLKnowItAll (4/2/2012)
Yup! Stepping away and observing because this is clearly not what I thought... 🙂IIt seems to me the issue's more related to the service broker not processing the emails in the queue; He's said that he's stopped and started the service, which is how he'd fixed a very similar issue in the past in another thread.
I'd like to see the results of some of the broker related DMV's for more information;
Service Broker Related Dynamic Management Views (Transact-SQL)
Well I'm sorry that I got everyone mad at me. What do I need to do to get out of the Dog House?
SELECT t1.name AS [Service_Name], t3.name AS [Schema_Name], t2.name AS [Queue_Name],
CASE WHEN t4.state IS NULL THEN 'Not available'
ELSE t4.state
END AS [Queue_State],
CASE WHEN t4.tasks_waiting IS NULL THEN '--'
ELSE CONVERT(VARCHAR, t4.tasks_waiting)
END AS tasks_waiting,
CASE WHEN t4.last_activated_time IS NULL THEN '--'
ELSE CONVERT(varchar, t4.last_activated_time)
END AS last_activated_time ,
CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--'
ELSE CONVERT(varchar,t4.last_empty_rowset_time)
END AS last_empty_rowset_time,
(
SELECT COUNT(*)
FROM sys.transmission_queue t6
WHERE (t6.from_service_name = t1.name) ) AS [Tran_Message_Count]
FROM sys.services t1 INNER JOIN sys.service_queues t2
ON ( t1.service_queue_id = t2.object_id )
INNER JOIN sys.schemas t3 ON ( t2.schema_id = t3.schema_id )
LEFT OUTER JOIN sys.dm_broker_queue_monitors t4
ON ( t2.object_id = t4.queue_id AND t4.database_id = DB_ID() )
INNER JOIN sys.databases t5 ON ( t5.database_id = DB_ID() )
It does not post very well.
Partial Results:
ExternalMailServicedboExternalMailQueueINACTIVE
InternalMailServicedboInternalMailQueueINACTIVE
http://schemas.microsoft.com/SQL/Notifications/EventNotificationServicedboEventNotificationErrorsQueueNot available
http://schemas.microsoft.com/SQL/Notifications/QueryNotificationServicedboQueryNotificationErrorsQueueNot available
http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerdboServiceBrokerQueueNot available
syspolicy_event_listenerdbosyspolicy_event_queueINACTIVE
This Server is not that new but the latest that I have received. I installed in late Feburary and created the first Database on 2012-02-29.
I will soon have 2 new Servers, I will use the same script to setup the Account & Pofile and it will be interesting to see what happens.
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 4:13 pm
Welsh Corgi (4/2/2012)
SELECT *
FROM sysmail_event_log
Nothing but start & stop DB Mail
SELECT *
FROM sysmail_allitems
I found the items were listed as failed.
So I check the Windows Event Log and I have the following:
1) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlConnection
Message: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .ctor(System.String)
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Microsoft.SqlServer.Management.Common.SqlConnectionInfo.CreateConnectionObject()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)
2) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlConnectionFactory
Message: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .cctor()
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection..cctor()
3) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlPerformanceCounters
Message: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .ctor()
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnectionFactory..ctor()
at System.Data.SqlClient.SqlConnectionFactory..cctor()
4) Exception Information
===================
Exception Type: System.Configuration.ConfigurationErrorsException
Message: Configuration system failed to initialize
BareMessage: Configuration system failed to initialize
Filename: NULL
Line: 0
Errors: System.Configuration.ConfigurationException[]
Data: System.Collections.ListDictionaryInternal
TargetSite: Void EnsureInit(System.String)
HelpLink: NULL
Source: System.Configuration
StackTrace Information
===================
at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName)
at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
at System.Configuration.ConfigurationManager.GetSection(String sectionName)
at System.Configuration.PrivilegedConfigurationManager.GetSection(String sectionName)
at System.Diagnostics.DiagnosticsConfiguration.Initialize()
at System.Diagnostics.Switch.InitializeConfigSettings()
at System.Diagnostics.Switch.InitializeWithStatus()
at System.Diagnostics.Switch.get_SwitchSetting()
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
5) Exception Information
===================
Exception Type: System.Configuration.ConfigurationErrorsException
Message: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config)
BareMessage: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied.
Filename: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config
Line: 0
Errors: System.Configuration.ConfigurationException[]
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ThrowIfErrors(Boolean)
HelpLink: NULL
Source: System.Configuration
StackTrace Information
===================
at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors()
at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
6) Exception Information
===================
Exception Type: System.UnauthorizedAccessException
Message: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void WinIOError(Int32, System.String)
HelpLink: NULL
Source: mscorlib
StackTrace Information
===================
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
at System.Configuration.Internal.InternalConfigHost.StaticOpenStreamForRead(String streamName)
at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName, Boolean assertPermissions)
at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName)
at System.Configuration.ClientConfigurationHost.OpenStreamForRead(String streamName)
1) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlConnection Message: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .ctor(System.String) HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at Microsoft.SqlServer.Management.Common.SqlConnectionInfo.CreateConnectionObject() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel) 2) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlConnectionFactory Message: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .cctor() HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnection..cctor() 3) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlPerformanceCounters Message: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .ctor() HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnectionFactory..ctor() at System.Data.SqlClient.SqlConnectionFactory..cctor() 4) Exception Information =================== Exception Type: System.Configuration.ConfigurationErrorsException Message: Configuration system failed to initialize BareMessage: Configuration system failed to initialize Filename: NULL Line: 0 Errors: System.Configuration.ConfigurationException[] Data: System.Collections.ListDictionaryInternal TargetSite: Void EnsureInit(System.String) HelpLink: NULL Source: System.Configuration StackTrace Information =================== at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey) at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName) at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName) at System.Configuration.ConfigurationManager.GetSection(String sectionName) at System.Configuration.PrivilegedConfigurationManager.GetSection(String sectionName) at System.Diagnostics.DiagnosticsConfiguration.Initialize() at System.Diagnostics.Switch.InitializeConfigSettings() at System.Diagnostics.Switch.InitializeWithStatus() at System.Diagnostics.Switch.get_SwitchSetting() at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp) at System.Data.SqlClient.SqlPerformanceCounters..ctor() at System.Data.SqlClient.SqlPerformanceCounters..cctor() 5) Exception Information =================== Exception Type: System.Configuration.ConfigurationErrorsException Message: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config) BareMessage: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. Filename: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config Line: 0 Errors: System.Configuration.ConfigurationException[] Data: System.Collections.ListDictionaryInternal TargetSite: Void ThrowIfErrors(Boolean) HelpLink: NULL Source: System.Configuration StackTrace Information =================== at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal) at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors() at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey) 6) Exception Information =================== Exception Type: System.UnauthorizedAccessException Message: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. Data: System.Collections.ListDictionaryInternal TargetSite: Void WinIOError(Int32, System.String) HelpLink: NULL Source: mscorlib StackTrace Information =================== at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.Configuration.Internal.InternalConfigHost.StaticOpenStreamForRead(String streamName) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName, Boolean assertPermissions) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName) at System.Configuration.ClientConfigurationHost.OpenStreamForRead(String streamName) at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()
OMG, it looks like permissions.:crying:
IMHO, the source of the Database mail problem is embeded in the Windows Application Event Log error that I posted above.
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 4:17 pm
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
I only mentioned SSIS because I suspect something weird is going on with that Server.
I just want to test CDONTS to see if it worked not as an alternative to DB 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 4:26 pm
SQLKnowItAll (4/2/2012)
In my neverending search for knowledge... I found this article. Welsh, does this help at all?
I had found another article similar to this.
I got an error when I tried to register the DLL.
The error is related to permissions. Even though I'm an Admin I get the error.
I was trying to catch a Domain Admin to get back to his desk and run as Administrator.
Could we please forget about CDONTS? I regret that I ever mentioned it.
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 4:31 pm
Welsh Corgi (4/2/2012)
SQLKnowItAll (4/2/2012)
In my neverending search for knowledge... I found this article. Welsh, does this help at all?I had found another article similar to this.
I got an error when I tried to register the DLL.
The error is related to permissions. Even though I'm an Admin I get the error.
I was trying to catch a Domain Admin to get back to his desk and run as Administrator.
Could we please forget about CDONTS? I regret that I ever mentioned it.
More than happy to forget CDONTS. It just confused me anyway.
April 3, 2012 at 4:42 am
Lowell (4/2/2012)
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
There's a bigger picture here, it's hard to track;
Wesh Corgi added a new server, and database mail's not working on the new server, but is fine on many of his other servers; he had a couple of other threads where he was wrestling with trying to get it to work.
this thread is an attempt to get any kind of email working, clearly out of frustration, as he moved off of db_mail and is trying cdonts as an alternative.
You implying that I do not know how to install Database Mail as if I have never done it. Thats is just not true and I'm offended.
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 3, 2012 at 4:56 am
I absolutely did not mean to imply that you did not have any experience, and I definitely did not mean to offend you.
If you read my post that way, I'm sorry, and apologize, as it was not my intent.
I would love to do nothing more that help you get your Database mail up and working; every installation is unique, and can have it own issues. I'm sure that's what we have here, something unique that is keeping the service broker from doing it's job.
I am more than aware that you've installed multiple servers previously, as well as having a skillset and experience in SSIS that is greater than my own.
Whatever the issue is with this installation, I'd like to help with diagnosing the issue.
Welsh Corgi (4/3/2012)
Lowell (4/2/2012)
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
There's a bigger picture here, it's hard to track;
Wesh Corgi added a new server, and database mail's not working on the new server, but is fine on many of his other servers; he had a couple of other threads where he was wrestling with trying to get it to work.
this thread is an attempt to get any kind of email working, clearly out of frustration, as he moved off of db_mail and is trying cdonts as an alternative.
You implying that I do not know how to install Database Mail as if I have never done it. Thats is just not true and I'm offended.
Lowell
April 3, 2012 at 5:21 am
Listed below is the script that I have used to configure 5 servers in the past 6 months. Excluding the one that it is not working on.
I also listed some of the statements that I executed to attempt to determine the problem. They are not necessarily in the correct order.
--use master
--go
--exec sp_configure 'show advanced options', 1
--reconfigure
--exec sp_configure 'Database Mail XPs', 1
--reconfigure
USE msdb
GO
DECLARE @ProfileName VARCHAR(35)
DECLARE @AccountName VARCHAR(35)
SET @ProfileName = 'Joe_Blow'
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 = 'XYZ_BI_DW_Mail_Account',
@description = 'Mail account used by XYZ_BI_DW SQL Server Agent.',
@email_address = 'JBlow@cypressig.com',
@replyto_address = 'JBlow@cypressig.com',
@display_name = ' XYZ_BI_DW Server Agent',
@mailserver_name = 'relay.mydomain.com',
@port = 25,
@username = 'JBlow@mydomain.com',
@password = 'XXX#$1$5',
@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 = 'XYZ_BI_DW_Mail_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'JBlow@mydomain.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = 'Database_Mail_Profile'
SE msdb
GO
exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp
EXEC sysmail_help_queue_sp @queue_type = 'Mail' ;
SELECT *
FROM sysmail_faileditems
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO
SELECT *
FROM sysmail_sentitems
SELECT *
FROM sysmail_allitems
SELECT *
FROM sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_profile p
SELECT * FROM msdb.dbo.sysmail_account
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 - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply