Archive

Archive for the ‘Mail Profile’ Category

Send email using SQL Mail without specifying a profile

December 31, 2013 Leave a comment

Issue: User is experimenting with sending email via sp_send_dbmail. If he set the @profile_name to NULL I get the following error:
No global profile is configured. Specify a profile name in the @profile_name parameter.
So, he would like to send email using SQL Mail without specifying a profile while at the same time passing in the @from_address?
Workaround:
As per the problem posted two thoughts
Frist Thought:
When @profile is not specified, sp_send_dbmail uses a default profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile. If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

sp_send_dbmail does not support e-mail messages with no content. To send an e-mail message, you must specify at least one of @body, @query, @file_attachments, or @subject. Otherwise, sp_send_dbmail returns an error.
Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.
If both @query and @file_attachments are specified and the file cannot be found, the query is still executed but the e-mail is not sent.
When a query is specified, the result set is formatted as inline text. Binary data in the result is sent in hexadecimal format.
The parameters @recipients, @copy_recipients, and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. At least one of these parameters must be provided, or sp_send_dbmail returns an error.
Reference Link:http://technet.microsoft.com/en-us/library/ms190307.aspx

Second thought:
How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account–http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx

Advertisements
Categories: Mail Profile

Database Mail Configuration script

February 5, 2013 1 comment

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I’m sharing here.

Setup Script

Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and

— Enable Database Mail for this instance

EXECUTE sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXECUTE sp_configure ‘Database Mail XPs’,1;

RECONFIGURE;

GO

— Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = ‘Primary Account’,

    @description = ‘Account used by all mail profiles.’,

    @email_address = ‘myaddress@mydomain.com’,

    @replyto_address = ‘myaddress@mydomain.com’,

    @display_name = ‘Database Mail’,

    @mailserver_name = ‘mail.mydomain.com’;

— Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = ‘Default Public Profile’,

    @description = ‘Default public profile for all users’;

— Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = ‘Default Public Profile’,

    @account_name = ‘Primary Account’,

    @sequence_number = 1;

— Grant access to the profile to all msdb database users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = ‘Default Public Profile’,

    @principal_name = ‘public’,

    @is_default = 1;

GO

–send a test email

EXECUTE msdb.dbo.sp_send_dbmail

    @subject = ‘Test Database Mail Message’,

    @recipients = ‘testaddress@mydomain.com’,

    @query = ‘SELECT @@SERVERNAME’;

GO

Database Mail Configuration script

December 12, 2011 Leave a comment

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I’m sharing here.  

Setup Script

Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and

— Enable Database Mail for this instance

EXECUTE sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXECUTE sp_configure ‘Database Mail XPs’,1;

RECONFIGURE;

GO

 

— Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = ‘Primary Account’,

    @description = ‘Account used by all mail profiles.’,

    @email_address = ‘myaddress@mydomain.com’,

    @replyto_address = ‘myaddress@mydomain.com’,

    @display_name = ‘Database Mail’,

    @mailserver_name = ‘mail.mydomain.com’;

 

— Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = ‘Default Public Profile’,

    @description = ‘Default public profile for all users’;

 

— Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = ‘Default Public Profile’,

    @account_name = ‘Primary Account’,

    @sequence_number = 1;

 

— Grant access to the profile to all msdb database users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = ‘Default Public Profile’,

    @principal_name = ‘public’,

    @is_default = 1;

GO

 

–send a test email

EXECUTE msdb.dbo.sp_send_dbmail

    @subject = ‘Test Database Mail Message’,

    @recipients = ‘testaddress@mydomain.com’,

    @query = ‘SELECT @@SERVERNAME’;

GO

Categories: Mail Profile