December 29, 2015 at 8:30 am
mw112009 (12/29/2015)
Yes I need the trick
Are you the DBA for the company?
--Jeff Moden
December 29, 2015 at 8:32 am
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
December 29, 2015 at 8:36 am
The guy is just trying to send a test email under his credentials...
December 29, 2015 at 8:42 am
i am not a dba
December 29, 2015 at 8:43 am
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
December 29, 2015 at 9:21 am
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
January 7, 2016 at 12:45 pm
Hi, Jeff, we're all still waiting for this super-duper magical emailing code you allege to have concocted!
:rolleyes:
Rich
January 7, 2016 at 10:42 pm
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
January 7, 2016 at 11:01 pm
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
January 8, 2016 at 6:25 am
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
January 8, 2016 at 6:40 am
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply