Send email from T-SQL

Question

Post reply

Send email from T-SQL

mw_sql_developer

SSCoach

Points: 19445
More actions
December 28, 2015 at 3:52 pm

#310453

Can someone please pass me a short segment of code to send email from T-SQL.
(:-P I will run it and findout whether our server is configured correctly to send email)
Thanks in advance!

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Exercise your Googlefu, young Jedi!

https://msdn.microsoft.com/en-us/library/ms190307.aspx

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

mw112009 (12/28/2015)
(:-P I will run it and findout whether our server is configured correctly to send email)

If it turns out to not be setup and no one wants to set it up, you don't actually need to set it up to send email from T-SQL. There's a trick to it. Lemme know if you need that trick.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 3

You can see the current database mail configuration by using the following system functions

EXEC msdb.dbo.sysmail_help_configure_sp;

EXEC msdb.dbo.sysmail_help_account_sp;

EXEC msdb.dbo.sysmail_help_profile_sp;

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

mw_sql_developer

SSCoach

Points: 19445

December 29, 2015 at 5:23 am

#1848426

Yes I need the trick

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

I believe this must be a permissions issue right ?

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Also on another server/database I get this .....

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

No global profile is configured. Specify a profile name in the @profile_name parameter.

I beleive mail is not set up here .. right ?

simon-hulse SSC Enthusiast Points: 194 More actions · Answer 7

1. you need to give permissions to the user that is attempting to send the mail. This is in the MSDB database, role "DatabaseMailUserRole"

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole' ,@membername = '<user or role name>';

2. You need to configure Database Mail, or you don't have the name of the profile correct. SQL can now send mails under several profiles.

In SSMS go to:

Management, Database Mail. Right mouse click and "Configure Database Mail". from here you can also see configured profiles (if you have any on that server)

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 8

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

check that you didn't check db_denydatareader DB role

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

John: Where exactly do you want me to check for that "..deny'''

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 10

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.

This error occurred because none of the database mail profiles you created were set to be the "Global Profile"

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 11

mw112009 (12/29/2015)
John: Where exactly do you want me to check for that "..deny'''

In SSMS - under Databases >>mydatabase >>Security >>Users >>myusername, right click the username, select properties under database role membership “db_denydatareader”

Lowell SSC Guru Points: 323518 More actions · Answer 12

Jeff Moden (12/28/2015)
mw112009 (12/28/2015)
(:-P I will run it and findout whether our server is configured correctly to send email)
If it turns out to not be setup and no one wants to set it up, you don't actually need to set it up to send email from T-SQL. There's a trick to it. Lemme know if you need that trick.

Wait...this looks like one of those "this Simple Trick Network Admins don't want you to know" clickbaits! where's the meat and potatoes here?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

simon-hulse SSC Enthusiast Points: 194 More actions · Answer 13

db_denydatareader is a database role, you will find it in <dbname>, Security, Users, <username>, Membership.

you can actually get to it a number of ways. I think what John is suggesting is that you don't have permissions to the data - but this is not the error message you got so I think this is a red herring

simon-hulse SSC Enthusiast Points: 194 More actions · Answer 14

1. you need to give permissions to the user that is attempting to send the mail. This is in the MSDB database, role "DatabaseMailUserRole"

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole' ,@membername = '<user or role name>';

2. You need to configure Database Mail, or you don't have the name of the profile correct. SQL can now send mails under several profiles.

In SSMS go to:

Management, Database Mail. Right mouse click and "Configure Database Mail". from here you can also see configured profiles (if you have any on that server)