Send email from T-SQL

Question

Post reply

Send email from T-SQL

Viewing 11 posts - 16 through 25 (of 25 total)

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

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

mw112009 (12/29/2015)
Yes I need the trick

Are you the DBA for the company?

--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)

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

simon-hulse (12/29/2015)
1. you need to give permissions to the user that is attempting to send the mail. This is in the MSDB database, role "DatabaseMailUserRole"

Gosh no. Unless you're talking about a service account for stored procedures to use, never give individuals the privs to send emails. Do it all in a stored procedure and ensure the stored procedure has the privs.

--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)

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

The guy is just trying to send a test email under his credentials...

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

mw_sql_developer

SSCoach

Points: 19445

December 29, 2015 at 8:42 am

#1848516

i am not a dba

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

Lowell (12/29/2015)
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?

Heh... my Network Admins actually encouraged me to build this "trick" because, when I started at the company, they were the ones that administered all that stuff. Since they were (and still are... we're in the process of a migration during Q1 2016) on SQL Server 2005, it was a real pain for them (they don't know T-SQL very well) to setup a profile for each "FROM" that we needed on each server that needed to send email. They were also just getting started with Clustered Servers and (for reasons unknown to me) they said it was very difficult to setup clustered servers with so many email profiles (yeah... they original clustered both Dev and Staging as well as Prod).

Since it DOES go around what might be authorized, that why I just asked if the person was a DBA for the company. Ostensibly, DBAs obey the rules or they're not really DBAs.

So, I set this up on all of my servers, even the prod ones. They later finally gave me the privs I needed to setup real email but I still keep the "trick" method on all servers because, for me as a DBA, it just works too well.

On the Dev and Staging servers, I made it so that code does NOT actually need to be changed when it finally gets to prod because of two simple synonyms.

I'll post the code tonight. It'll likely cause nightmares for many purists. 😛

--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)

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

mw112009 (12/29/2015)
i am not a dba

I'll still post the code but, do yourself a real favor to keep your butt out of the sling... ask your DBA if it's alright to do this "bypass". If (s)he says, "No", then work with them instead of going around them. Being assigned as the DBA for my company, I have to tell you that you don't want to get on the wrong side of the DBA... ever.

--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)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

Hi, Jeff, we're all still waiting for this super-duper magical emailing code you allege to have concocted!

:rolleyes:

Rich

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

Rich Mechaber (1/7/2016)
Hi, Jeff, we're all still waiting for this super-duper magical emailing code you allege to have concocted!
:rolleyes:
Rich

First, thanks for responding. I had delete a whole bunch of email and lost track of this post.

Shifting gears, I hoping that I'm taking your response above very wrong.

--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)

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

Again, sorry for the delay. I'd lost track of this particular thread.

The code I spoke of is below. Look for the word "TODO" for changes that you'll need to make get it to work on your system. It does not require any email setup on SQL Server but it does require that your domain have email setup. Once you have it setup for your domain, you can copy it to any SQL Server in the domain that's 2005 or better and it'll work. Note that I haven't built in the capability to pass it a query like the real thing. I didn't need or want that (better ways of doing things than that) and didn't code for it. You might also want to search for the literal "XXXX" just to make sure I didn't miss anything with a "TODO".

"Usage" and other details are where they belong... comments in the code. 😉 This is the "sanitized" version of what I use in production.

Here's the code.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SendCdoMail]

/**********************************************************************************************************************

Purpose:

Send email via SMTP server using CDO (SMTP Server). Note that the body of the email has virtually no limit

(1 billion NVARCHAR characters) as to length and may be in HTML format or plain text format.

***** NOTE THAT THE EMAIL SERVER IS HARDCODED FOR THE GIVEN INSTALLATION! *****

Usage:

EXEC dbo.SendCdoMail @pFrom, @pTo, @pCC, @pBCC, @pSubject, @pBody, @pFormat, @pAttachments, @pDebug

Parameter Info:

The datatypes of each parameter have been defined to match similar parameters in sp_Send_Mail.

@pFrom is optional but may be virtually anything so long as it's in the form of something@something.com with the

following exceptions. If the word "Server" is passed or @pFrom is passed as a NULL, the a

@@SERVERNAME + N'@NoReply.XXXX.com address will be created automatically.

@pTo, @pCC, @pBcc are optional but at least one must be correclty populated.

@pBody is optional and may contain HTML when @pFormat='HTML'.

@pFormat is optional and defaults to 'TEXT' (May be 'TEXT' or 'HTML').

@pAttachments is optional.

@pDebug is optional and defaults to 0 (0 is "Debug Off", non-zero is "Debug On").

Revision History:

Rev 01 - 23 Nov 2011 - Jeff Moden - Initial creation and unit test

Rev 02 - 03 Sep 2014 - Jeff Moden - Change SMTP target from old IP address to new server name alias with the "smtp"

- hint for anonymous logins verified by machine address entered on the smtp server.

**********************************************************************************************************************/

--===== Declare the I/O parameters (named as expected).

@pFrom VARCHAR(255) = NULL, --Virtually anything may precede an @ symbol on this line.

@pTo VARCHAR(MAX) = NULL,

@pCC VARCHAR(MAX) = NULL,

@pBCC VARCHAR(MAX) = NULL,

@pSubject NVARCHAR(255),

@pBody NVARCHAR(MAX) = NULL,

@pFormat VARCHAR(20) = 'TEXT', --May be 'TEXT' or 'HTML'

@pAttachments NVARCHAR(MAX) = NULL,

@pDebug TINYINT = 0 --0 is "Debug Off", non-zero is "Debug On"

WITH EXECUTE AS OWNER

AS

----===== Uncomment this section for testing only

--DECLARE @pFrom VARCHAR(255),

-- @pTo VARCHAR(MAX),

-- @pCC VARCHAR(MAX),

-- @pBCC VARCHAR(MAX),

-- @pSubject NVARCHAR(255),

-- @pBody NVARCHAR(MAX),

-- @pFormat VARCHAR(20),

-- @pAttachments NVARCHAR(MAX),

-- @pDebug TINYINT,

-- SELECT @pFrom = 'Server',

-- @pTo = 'jmoden@XXXX.com', --Note: Change these names to your test email address

-- @pCC = 'jmoden@XXXX.com',

-- @pBCC = 'jmoden@XXXX.com',

-- @pSubject = N'TestMessage(' + CONVERT(NVARCHAR(30),GETDATE(),121) + N') - Did you get this?',

-- @pBody = N'Test Message: ' + CONVERT(NVARCHAR(30),GETDATE(),121) + N' Did you get this?',

-- @pFormat = 'HTML';

--=====================================================================================================================

-- Debug to show inputs

--=====================================================================================================================

IF @pDebug <> 0

SELECT [From] = @pFrom,

[To] = @pTo,

[CC] = @pCC,

[BCC] = @pBCC,

[Subject] = @pSubject,

[Body] = @pBody,

[Format] = @pFormat

;

--=====================================================================================================================

-- Presets

--=====================================================================================================================

--===== Suppress the auto-display of rowcounts to prevent false error message returns

SET NOCOUNT ON;

--===== Declare local variables

DECLARE @objEMailID INT, --OLE automation object identification for email

@BodyFormat NCHAR(1),

@MailFormat NCHAR(1),

@Result INT,

@ErrorMessage SYSNAME

;

--===== If the From field is Null or contains the word "server" in any form,

-- make it so the server name (THIS server name) is used.

IF ISNULL(@pFrom,N'Server') LIKE N'%Server%'

SELECT @pFrom = @@SERVERNAME + N'@NoReply.XXXX.com' --Name changed by Rev 06 --TODO CHANGE XXXX to whatever you want...

;

--===== Convert the format requirement to inputs required by CDOSYS.

SELECT @BodyFormat = CASE WHEN @pFormat = 'HTML' THEN '0' ELSE '1' END, --0=HTML/Plain Text, 1=Plain Text Only

@Mailformat = CASE WHEN @pFormat = 'HTML' THEN '0' ELSE '1' END --0=MIME, 1=Plain Text Only

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

BEGIN TRY

--=====================================================================================================================

-- Create an email object and configure it

--=====================================================================================================================

--===== Set the error message for this sub-section

SELECT @ErrorMessage = N'dbo.SendCdoMail errored during message configuration.';

--===== Setup the configuration to create a CDOSYS.dll/SMTP email message.

-- Trust me... none of this is optional. If you want to know what all of this does,

-- you need to Google the HTTP addresses which aren't really addresses at all.

-- Create the mail object

EXEC dbo.sp_OACreate 'cdo.message', @objEmailID OUT;

-- This is a "by rote" required property that needs to be set.

EXEC dbo.sp_OASetProperty @objEmailID,

N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2';

-- Set the SMTP address for the XXXX mail gateway. (Rev 02)

EXEC dbo.sp_OASetProperty @objEmailID,

N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','smtp.email.XXXX.com'; --TODO CHANGE THE XXXX to what you need it to be. Could be just a TCP/IP address.

-- This is a "by rote" required property that needs to be set.

EXEC dbo.sp_OASetProperty @objEmailID,

N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25';

-- Set the authentication mode.

-- '0' = Windows Authentication

-- '1' = Hard coded user name and password required.

EXEC dbo.sp_OASetProperty @objEmailID,

N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0';

-- If '1' is selected for 'smtpauthenticate' above, uncomment the following line and enter hard coded user name.

--EXEC dbo.sp_OASetProperty @objEmailID,

-- N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','TODO User Name Here';

-- If '1' is selected for 'smtpauthenticate' above, uncomment the following line and enter hard coded password.

--EXEC dbo.sp_OASetProperty @objEmailID,

-- N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','TODO Password HERE';

-- Update the mail object with all the field information we created above.

EXEC dbo.sp_OAMethod @objEmailID,

N'Configuration.Fields.Update', NULL;

--=====================================================================================================================

-- Populate the mail object with the parameters we passed in and the settings we calculated from some of those

-- parameters.

--=====================================================================================================================

--===== Set the error message for this sub-section

SELECT @ErrorMessage = N'dbo.SendCdoMail errored during field population.';

--===== Populate the fields of the email message

EXEC dbo.sp_OASetProperty @objEmailID, N'To' , @pTo;

EXEC dbo.sp_OASetProperty @objEmailID, N'From' , @pFrom;

EXEC dbo.sp_OASetProperty @objEmailID, N'Subject' , @pSubject;

EXEC dbo.sp_OASetProperty @objEmailID, N'HTMLBody' , @pBody;

EXEC dbo.sp_OASetProperty @objEmailID, N'BodyFormat' , @BodyFormat; --0=HTML/Plain Text, 1=Plain Text Only

EXEC dbo.sp_OASetProperty @objEmailID, N'MailFormat' , @Mailformat; --0=MIME, 1=Plain Text Only

EXEC dbo.sp_OASetProperty @objEmailID, N'CC' , @pCC;

EXEC dbo.sp_OASetProperty @objEmailID, N'BCC' , @pBCC;

EXEC dbo.sp_OASetProperty @objEmailID, N'Attachments', @pAttachments;

--=====================================================================================================================

-- Send the mail

--=====================================================================================================================

--===== Set the error message for this sub-section

SELECT @ErrorMessage = N'dbo.SendCdoMail errored while sending the email.';

--===== Send the email and capture the result

EXEC @result = dbo.sp_OAMethod @objEmailID, N'Send', NULL;

--===== Close the email object (helps prevent memory leaks)

EXEC dbo.sp_OADestroy @objEmailID;

-- Make sure we no error occurred while sending the email

IF @result <> 0 RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;

END TRY

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

-- CATCH code

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

BEGIN CATCH

SELECT ErrorDescription = @ErrorMessage,

ErrorNumber = ERROR_NUMBER(),

ErrorSeverity = ERROR_SEVERITY(),

ErrorState = ERROR_STATE(),

ErrorProcedure = ERROR_PROCEDURE(),

ErrorLine = ERROR_LINE(),

ErrorMessage = ERROR_MESSAGE()

;

--===== Debug to show inputs and related token(s)

IF @pDebug <> 0

SELECT [From] = @pFrom,

[To] = @pTo,

[CC] = @pCC,

[BCC] = @pBCC,

[Subject] = @pSubject,

[Body] = @pBody,

[Format] = @pFormat,

[BodyFormat] = @BodyFormat,

[MailFormat] = @Mailformat

;

--===== Exit with error.

RAISERROR (@ErrorMessage,16,3)

RETURN -1 --Failed

;

END CATCH

;

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

--=====================================================================================================================

-- Debug to show inputs

--=====================================================================================================================

IF @pDebug <> 0

SELECT [From] = @pFrom,

[To] = @pTo,

[CC] = @pCC,

[BCC] = @pBCC,

[Subject] = @pSubject,

[Body] = @pBody,

[Format] = @pFormat,

[BodyFormat] = @BodyFormat,

[MailFormat] = @Mailformat

;

--=====================================================================================================================

-- Exit with success

--=====================================================================================================================

--===== If we made it to here, all went well. Return with success.

RETURN 0 --Success

;

--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)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

Jeff Moden (1/7/2016)
Rich Mechaber (1/7/2016)
Hi, Jeff, we're all still waiting for this super-duper magical emailing code you allege to have concocted!
:rolleyes:
Rich
First, thanks for responding. I had delete a whole bunch of email and lost track of this post.
Shifting gears, I hoping that I'm taking your response above very wrong.

Thanks for posting this, Jeff. I doubt I'll have occasion or need to use it, but I'm always interested in unusual ways of cat skinning. This looks an awful lot like VB scripts I wrote (non-SQL) years ago!

Rich

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

Rich Mechaber (1/8/2016)
Thanks for posting this, Jeff. I doubt I'll have occasion or need to use it, but I'm always interested in unusual ways of cat skinning. This looks an awful lot like VB scripts I wrote (non-SQL) years ago!
Rich

'Zactly. It's the "ancient" CDOSYS method (the upgrade from CDONTS). It's no longer well advertised but it is supported. Again, the only time someone would need this is if they really didn't feel like setting up database mail or had an old enough version of SQL Server that didn't allow you to specify the FROM without having to setup a separate profile for every little thing you might need to.

--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)