August 23, 2017 at 7:58 am
I'm not entirely sure if I'm looking for an answer or an opinion here, sorry. The topic started off an a long rant, which I decided wasn't actually beneficially to anyone, so I started again. 🙂
SQL server has opinion for sending email, which is great. For DBAs, and related teams, you can get it to send crucial and important information just when you need it. You can also use it to send certain reports that are required by business processes, also very useful. But, is it really designed to be used as a very expensive Mail Merge Service? I'd suggest not.
Recently, at the office, the requests for having emails sent to our clients has been on the rise, and SQL Server has been the candidate to do the grunt work (because our core system can't). This, I can see, is going to start taking a toll of the server, as these queries aren't cheap and are very frequent, so I'm considering alternatives.
Does anyone else have this problem? Does anyone use an alternative product to SQL server to handle their real time emails, specifically data driven ones? How are you managing to keep impact to a minimum if you're having to regularly check for new clients or updates?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 23, 2017 at 8:53 am
Ran into something similar awhile ago but SQL Server was never really pursued as the "mailer"...it doesn't make much sense - because just as you stated, it's not really what a DBMS is designed for.
The path they ended up going down that worked for that situation was using an external app that was always connected to the database, it used Query Notifications and SQLDependency so that the app was notified when there were changes to the two or three tables that had the data which would trigger sending certain emails. After the application received notification of a table change that was being monitored, it would execute some queries, check whatever logic and the application would send the appropriate emails. So the only "hits" to the database were just with the service broker pieces for the notifications (instead of doing the polling for new and/or updated data) and the subsequent queries the application would do when notified of the changes. So minimal impact. It won't work for all scenarios but can be a decent way to manage some things like this.
There are probably some other ways using some of those things with service broker so something with that may be an option.
Sue
August 23, 2017 at 12:33 pm
Pretty sure you already know the answer.
The email service on SQL Server was only ever really intended to act as an alerting mechanism for issues on the server. That's a large part of why the functionality is very limited. While on a certain level I agree with Heinlein that specialization is for insects, he wasn't talking about software. There, specialization has a real purpose. I'd strongly suggest letting SQL Server manage relational data storage, which it's pretty darned good at, and finding another specialty tool for managing email.
That opinion and a $3.50 will get you a cup of coffee.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2017 at 1:26 pm
I suggest trying to steer them towards using Reporting Services, a much better tool for sending scheduled reports.
August 23, 2017 at 4:31 pm
My humble opinion is that, as with all else, "It Depends".
I agree that SQL Server is an expensive product and it's a shame to turn it into an email generation system with some reporting to boot. The problem is when people try to work around that fact.
For example... no matter what tool someone uses (SSRS, special tool, 3rd party software, yada-yada, etc, etc), WHERE does the data for it all live? Typically, all the necessary data still lives in the database on the SQL Server.
So the way I see it, unless you can very quickly and efficiently assemble the necessary data and then push that data to a dedicated email system, you can spend a pot wad o' money on some other solution or learn how to do it in SSRS, or whatever but the server is still going to be the source of the data and your resources might still be getting hammered. Since that's usually true and sending an email from SQL Server is nearly a trivial task compared to assembling the data for the email, you might be wasting time, money, effort, and, possibly, additional hardware by "going purist". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2017 at 7:58 am
My apologies for the delay.
Here's the proc I use to send email from SQL Server without using sp_Send_DBMail. This is my "presentation" version that I give to others and include in my "Tool Time #1" presentation. There are some "todo's" that need to be done if you decide to use it but nothing frightening or complex.
Notice the "sp_OASetProperty" entries. If you look at similar VB entries, you can see how to execute what would normally require a trip to VB in T-SQL, instead.
/*
DEPLOYMENT NOTES:
1. Before you deploy, search for "TODO" and make the appropriate changes for your company.
2. This could be easily modified to be an "sp_" stored procedure in the Master database.
3. Make sure the the current database is where you want to install this. I keep it in my "DBA" database.
*/
GO
--=====================================================================================================================
-- Enable OLE Automation
-- You could add this to the proc to turn it on, use it, and then turn it off to make some DBAs happy.
--=====================================================================================================================
EXEC sp_configure 'show advanced options' , 1; RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE; -- 1 is ON, 0 is OFF
EXEC sp_configure 'show advanced options' , 0; RECONFIGURE;
GO
CREATE 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:
--===== Send the email.
EXEC dbo.SendCdoMail
@pFrom = @From, --Optional. Defaults to @@ServerName@NoReply.somecompanyname.com
@pTo = @To,
@pSubject = @Subject,
@pBody = @Body,
@pFormat = 'HTML', --Optional. Defaults to plain 'Text'
@pAttachments = 'some list of file names' --Optional
@pDebug = 0 --Optional. Defaults to '0' (Debug is OFF). Non-zero is ON.
;
Programmer Notes:
1. Having "Debug" ON does NOT prevent the sending of an email!
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.somecompany.com address will be created automatically.
@pTo, @pCC, @pBcc are optional but at least one must be correctly 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
**********************************************************************************************************************/
--===== 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 = 'youremail@somecompany,com', --Note: Change these names to your test email address
-- @pCC = 'youremail@somecompany,com',
-- @pBCC = 'youremail@somecompany,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.somecompany.com' --TODO: Change "somecompany" to what you need.
;
--===== 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 mail gateway. (Rev 02)
-- TODO: Change the "XXXX" to either the IP address of the smtp gateway or to the alias for the gateway.
-- An alias may be in the form of something like 'smtp.email.somecompany.com'
EXEC dbo.sp_OASetProperty @objEmailID,
N'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','XXXX';
-- 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 - Preferred method. Server login should be a Windows Login.
-- '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
--=====================================================================================================================
-- Error Handling
--=====================================================================================================================
BEGIN CATCH
SELECT ErrorDescription = @ErrorMessage,
ErrorNumber = ERROR_NUMBER(),
ErrorSeverity = ERROR_SEVERITY(),
ErrorState = ERROR_STATE(),
ErrorProcedure = ERROR_PROCEDURE(),
ErrorLine = ERROR_LINE(),
ErrorMessage = ERROR_MESSAGE()
;
--===== Show inputs and related token(s)
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 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 success
--=====================================================================================================================
--===== If we made it to here, all went well. Return with success.
RETURN 0 --Success
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2017 at 8:11 am
Jeff Moden - Wednesday, August 23, 2017 4:31 PMMy humble opinion is that, as with all else, "It Depends".I agree that SQL Server is an expensive product and it's a shame to turn it into an email generation system with some reporting to boot. The problem is when people try to work around that fact.
For example... no matter what tool someone uses (SSRS, special tool, 3rd party software, yada-yada, etc, etc), WHERE does the data for it all live? Typically, all the necessary data still lives in the database on the SQL Server.
So the way I see it, unless you can very quickly and efficiently assemble the necessary data and then push that data to a dedicated email system, you can spend a pot wad o' money on some other solution or learn how to do it in SSRS, or whatever but the server is still going to be the source of the data and your resources might still be getting hammered. Since that's usually true and sending an email from SQL Server is nearly a trivial task compared to assembling the data for the email, you might be wasting time, money, effort, and, possibly, additional hardware by "going purist". 😉
You're latter part is really the crux of my concerns Jeff, and you hit the nail on the head. In an ideal world, the emails I'm being asked to create recently would be event driven, then there's no need to almost continually checking the tables for new entries. Right now, it's not too bad, but I can see that the business here is going towards wanting more, which is a concern. Although the queries aren't too expensive, lots of them are all going to add up. I kind of think there's not real solution to the "problem", it's more a case of managing it well.
Jeff Moden - Thursday, August 24, 2017 7:58 AMMy apologies for the delay.
Here's the proc I use to send email from SQL Server without using sp_Send_DBMail. This is my "presentation" version that I give to others and include in my "Tool Time #1" presentation. There are some "todo's" that need to be done if you decide to use it but nothing frightening or complex.
Notice the "sp_OASetProperty" entries. If you look at similar VB entries, you can see how to execute what would normally require a trip to VB in T-SQL, instead.
No worries, I really appreciate the help in the first place. I won't get a chance to look at this for a little while, as I'm moving tomorrow (so out of the office for awhile) and the internet isn't getting switched on at the new gaff until late next week. If I have any questions once I've had a look and given it a go I'll post back. If you have the time to get back to me on them, I'd be really grateful.
Cheers!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply