August 22, 2017 at 4:07 am
To start this off on "the right foot", I am pretty sure that this can't be achieved by T-SQL/SQL Server. It is, however, the task that I have been set. /sigh
To give a little bit of an idea, certain directors in the company only understand that SQL Server as a Mailing System. It sends them emails with reports, so it can sends customers emails when they purchase something. What they don't seem to understand is that SQL Server is a data engine, and isn't really a mail merge tool. Of course, that argument falls on deaf ears.
So, at the moment, our SQL server sends a few emails to customers based on certain conditions (such as them buying a product, a renewal being due, etc). This, is achieve by a lovely thing called a CURSOR (that we all know and love), and does a nice RBAR process as it cycles through the customers to email (no triggers here, as the data is only a replication of the system's flat file system). It's "Beautiful" . :sick:
Anyway, a certain someone has now decided that in addition to the email being sent to the customer, an email needs to be saved to the customer's file. This needs to be done as a .msg file, so that they can view the file in Outlook and "know" it's an email.
I've had a little bit of a Google, but my Search-fu isn't even giving me results related to what I'm trying to do. This tells me, as I suspect, that the task at hand isn't possible with SQL Server, or at least not on its own. Does anyone know of a way to save the email that is sent via sp_send_dbmail? Specifically, as I said, it's needs to be in a .msg format.
I did consider taking that details out of msdb.dbo.sysmail_allitems, and then saving them off that way. The problem, however, is I don't see a way to export the data as a .msg file. I could save as (m)html, however, I doubt that's going to get the thumbs up; especially as header (to address, subject, sent date/time, etc) information would need to be displayed other ways which would normally be shown in the email file.
If this really can't be achieve, please, do not be afraid to shoot it down. In all honestly, I expect it to be and it gives me more reasons to go back to the Directors and tell them "SQL Server is not the right tool for this, we need a different client/service." Hopefully, if I get that through, they might actually open their wallets (as I doubt such tools are free, but not expensive).
Thanks for any help.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 4:43 am
Thom, we used to save email details in a table in one of our databases. Not as an .msg format, but in table format that could be used to regenerate email details. The question, I guess, is how the customers need to access this information. If they access it through a website, why can't the website do all the conversion work and SQL just keep the text information?
Let me dig through our stuff here, see if I can find the details of how we built this table. I think we wrapped sp_send_dbmail (after SQL 2000) with the original proc (2000 and lower) that we used for this tracking. I'll let you know shortly what I find.
August 22, 2017 at 4:46 am
As a wild stab ...
You could create a CLR that takes the data and creates a mail object. Then save that to a file ....
August 22, 2017 at 4:58 am
Create table script...
USE <MyDB>;
GO
IF (SELECT OBJECT_ID('MyDB..tbl_smtp_sendmail')) IS NOT NULL
DROP TABLE [dbo].[tbl_smtp_sendmail];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_smtp_sendmail](
[MailTS] [datetime] NULL CONSTRAINT [DF_tbl_smtp_sendmail_MailTS] DEFAULT (getdate()),
[SentFrom] [varchar](200) NULL,
[FromName] [varchar](200) NULL,
[SentTo] [varchar](1000) NULL,
[ReplyTo] [varchar](200) NULL,
[CopyTo] [varchar](200) NULL,
[BlindCopyTo] [varchar](100) NULL,
[Subject] [varchar](1000) NULL,
[Message] [varchar](3000) NULL,
[Attachment] [varchar](1000) NULL,
[Attachments] [varchar](1000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Proc wrapper script... We grant impersonate in this script because at a certain point, we needed extra permissions for attachments or something like that. We also have a CASE statement for setting up environment specific DB Mail profiles so we don't have to keep changing the script when we restore a database down from Production into non-Prod. You'll want to update that or remove it and replace @profile with your actual profile if you're only using this in one environment.
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[sp_smtp_sendmail]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_smtp_sendmail]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_smtp_sendmail]
-----------------------------------------------------------------
-- Date Written: 4/12/05
-- Author: MA
-- Description: wrapper proc for xp_smtp_sendmail; for use with
-- DTS packages; created to accept parameter values;
-- original procedure courtesy of Gert E.R. Drapers
-- (GertD@SQLDev.Net)
-----------------------------------------------------------------
@FROM NVARCHAR(4000) = NULL,
@FROM_NAME NVARCHAR(4000) = NULL,
@TO NVARCHAR(4000) = NULL,
@replyto NVARCHAR(4000) = NULL,
@cc NVARCHAR(4000) = NULL,
@BCC NVARCHAR(4000) = NULL,
@priority NVARCHAR(10) = N'NORMAL',
@subject NVARCHAR(4000) = NULL,
@message NVARCHAR(4000) = NULL,
@messagefile NVARCHAR(4000) = NULL,
@type NVARCHAR(100) = N'TEXT',
@attachment NVARCHAR(4000) = NULL,
@attachments NVARCHAR(4000) = NULL,
@server NVARCHAR(4000) = N'smarthost',
@codepage INT = 0,
@timeout INT = 10000,
@profile varchar(50) = NULL
as
declare @rc int,
@inituser varchar(50),
@sqlcmd varchar(8000),
@servernm varchar(50),
@chkinituser varchar(50)
SELECT @profile=
CASE
WHEN @@SERVERNAME IN ('1','2')
THEN 'DevProfile'
WHEN @@SERVERNAME IN ('3','4')
THEN 'TestProfile'
WHEN @@SERVERNAME IN ('5','6')
THEN 'ProdProfile'
END
IF (@type='text\plain') or (@type='')
BEGIN
SET @type='text'
END
SELECT @chkinituser=SUSER_NAME()
SELECT @inituser=USER_NAME()
IF (@attachment is not NULL) and (@chkinituser not like '%domain%')
BEGIN
SET @sqlcmd='USE MSDB' + char(10) + 'GRANT IMPERSONATE ON USER:: [domain\user] TO [' + @inituser + ']' + char(10)
SET @sqlcmd=@sqlcmd + 'EXECUTE msdb.dbo.sp_send_dbmail
@PROFILE_Name=''' + @profile + ''',@recipients=''' + isnull(@TO,'') + ''',@copy_recipients=''' + isnull(@CC,'') + ''',
@blind_copy_recipients=''' + isnull(@BCC,'') + ''',
@importance=''' + isnull(@priority,'') + ''',
@subject=''' + isnull(@subject,'') + ''',
@body=''' + isnull(@message,'') + ''',
@body_format=''' + isnull(@type,'') + ''',
@file_attachments=''' + isnull(@attachment,'') + '''' + char(10) + 'REVERT'
EXEC (@sqlcmd)
-- select @sqlcmd
if (@@error <> 0 )--or @rc <> 0)
raiserror(N'Sending message using sp_send_dbmail failed', 16, 1)
else
begin
insert into dbo.tbl_smtp_sendmail
(SentFrom, FromName, SentTo, ReplyTo, CopyTo, BlindCopyTo,
Subject, Message, Attachments)
values
(left(@profile, 200), left(@profile,200), left(@TO, 1000), left(@profile, 200),
left(@CC, 200), left(@BCC, 100), left(@subject, 1000),
left(@message,3000), left(@attachment,1000))
end
END
ELSE
BEGIN
exec @rc = msdb.dbo.sp_send_dbmail
@PROFILE_Name =@profile,
@recipients = @TO,
@copy_recipients = @cc,
@blind_copy_recipients = @BCC,
@importance = @priority,
@subject = @subject,
@body = @message,
@body_format = @type,
@file_attachments = @attachment
if (@@error <> 0 or @rc <> 0)
raiserror(N'Sending message using sp_send_dbmail failed', 16, 1)
else
begin
insert into dbo.tbl_smtp_sendmail
(SentFrom, FromName, SentTo, ReplyTo, CopyTo, BlindCopyTo,
Subject, Message, Attachments)
values
(left(@profile, 200), left(@profile,200), left(@TO, 1000), left(@profile, 200),
left(@CC, 200), left(@BCC, 100), left(@subject, 1000),
left(@message,3000), left(@attachment,1000))
end
END
GO
I hope this helps get you started. The .msg thing, as far as I know, can't actually be done in the database. You could potentially save the email message as an image in SQL Server, but that would make it harder to forward or respond to it later. But doing something along these lines, though, at least someone can build a CLR or application code on top of this data to reconstitute the message as an .msg for whatever interface the customer is using to access it. It wouldn't be Outlook, though.
On the other hand... Exchange (the base platform behind Outlook) is supposed to be SQL Server based now. So if you have an Exchange server, maybe you already have what you need and can leverage whatever publically available .dlls, services, schemas, etc. are on that server to save these messages as the .msg. Maybe import some of that code into regular SQL Server?
August 22, 2017 at 5:01 am
Brandie Tarvin - Tuesday, August 22, 2017 4:43 AMThom, we used to save email details in a table in one of our databases. Not as an .msg format, but in table format that could be used to regenerate email details. The question, I guess, is how the customers need to access this information. If they access it through a website, why can't the website do all the conversion work and SQL just keep the text information?Let me dig through our stuff here, see if I can find the details of how we built this table. I think we wrapped sp_send_dbmail (after SQL 2000) with the original proc (2000 and lower) that we used for this tracking. I'll let you know shortly what I find.
The client files are for the staff UI, not the customers. The staff can then see what documents have been sent to the customer, which should include said emails. As this is a 3rd party application the location of the files needs to be in a specific location per client/policy, and the capacity to query SQL server to see what emails have been sent isn't an option. Working out the location for each customer isn't an issue (it can be derived from their details), which is located on our file server.
Hence the need for not only exporting the data, but in the same format that the customer received it in.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 5:06 am
Thom A - Tuesday, August 22, 2017 5:01 AMBrandie Tarvin - Tuesday, August 22, 2017 4:43 AMThom, we used to save email details in a table in one of our databases. Not as an .msg format, but in table format that could be used to regenerate email details. The question, I guess, is how the customers need to access this information. If they access it through a website, why can't the website do all the conversion work and SQL just keep the text information?Let me dig through our stuff here, see if I can find the details of how we built this table. I think we wrapped sp_send_dbmail (after SQL 2000) with the original proc (2000 and lower) that we used for this tracking. I'll let you know shortly what I find.
The client files are for the staff UI, not the customers. The staff can then see what documents have been sent to the customer, which should include said emails. As this is a 3rd party application the location of the files needs to be in a specific location per client/policy, and the capacity to query SQL server to see what emails have been sent isn't an option. Working out the location for each customer isn't an issue (it can be derived from their details), which is located on our file server.
Hence the need for not only exporting the data, but in the same format that the customer received it in.
Wow. That sounds like a scanning system (like Hyland OnBase). Is there an Exchange Server onsite? Cribbing from that would be your best bet.
August 22, 2017 at 5:06 am
DesNorton - Tuesday, August 22, 2017 4:46 AMAs a wild stab ...
You could create a CLR that takes the data and creates a mail object. Then save that to a file ....
Not used, and certainly not created, a CLR function before, in all honestly. It's certainly something I'll entertain, if we can't get a solution and they won't budget for a solution. My concern, however, is that this means that likely the only person able to support the CLR would be myself (if it's in VB.Net). or the web developer (if in C#). Our 3rd party client runs on Linux, and has a custom scripting language, so most of our devs (only) work in that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 5:16 am
Thom A - Tuesday, August 22, 2017 5:06 AMDesNorton - Tuesday, August 22, 2017 4:46 AMAs a wild stab ...
You could create a CLR that takes the data and creates a mail object. Then save that to a file ....Not used, and certainly not created, a CLR function before, in all honestly. It's certainly something I'll entertain, if we can't get a solution and they won't budget for a solution. My concern, however, is that this means that likely the only person able to support the CLR would be myself (if it's in VB.Net). or the web developer (if in C#). Our 3rd party client runs on Linux, and has a custom scripting language, so most of our devs (only) work in that.
In VB.Net, it seems pretty straight forward. NOTE: I have not touched VB.Net or C# in more than 10 years.
August 22, 2017 at 5:20 am
If you really want to go crazy on this, you could see if there is an API for the exchange server that you can use to query emails sent and retrieve them in .msg format. Or you could try if you can somehow directly access the Exchange database. But neither sounds like a very appealing option to me.
When you use the HTML option of sp_send_dbmail, then storing the HTML message content and producing that in a browser should result in a almost exact copy of the original email. (One of my customers in the past had a similar system, using SQL Server to send emails - I saved the content of the emails in a table and made them available to backoffice staff in case they needed to review the email history)
August 22, 2017 at 5:21 am
Flat out, they're asking for crazy stuff. Even if you can find some type of solution to deliver what's being asked for, it's going to be a Rube Goldberg style solution that isn't going to scale and will certainly be a maintenance nightmare. Better to spend your time getting them on board with the idea that it's all about data. Sure, store that an email was sent and store the parameter values that can recreate the email. Storing the message, and as a .msg file, which will have to be done as a second step because SQL Server on it's own is NOT creating that format, so it has to come from outside SQL Server and cannot possibly be a part of the fundamental service... they're digging a hole.
A pretty healthy chunk of all the issues around SQL Server, or any other DBMS for that matter, is because people fight tooth & nail to make it do stuff that it just wasn't designed for. I had a fight with a developer once when they presented me with code that wouldn't scale or perform. When I said it violated best practices the developers response, and they considered it absolutely definitive, was "But nothing in SQL Server prevented me from doing this, so it must be how it was supposed to function." Yes, I know how stupid that sounds. Yes, nothing prevents you from hammering your toes flat and yet that's not the intended function of a hammer. Yes, I lost the fight, initially. The code went into the server. It failed, badly. We had to completely redesign the system.
You're in that same fight. Good luck.
"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 22, 2017 at 5:47 am
Grant posts a great wake-up call. I must admit that I felt for the technical challenge of your question, but Grant is right in taking a step back and wondering whether the question is valid at all.
One of the things I try to remind myself of when reading user requests is that should specify WHAT they want, not HOW they want it. If the request contains the words "save as .msg file", then they have filled in HOW it should be done, which is not their job. Take a step back, and ask them (or infer from their specs) WHAT they want. The answer, in this case, is probably going to be "see the mails we have sent". Grant has presented some good thoughts on how you can give them WHAT they want - just in a different (better!) way.
August 22, 2017 at 6:08 am
I totally agree that SQL Server is not the right place for it. My main aim right now is 2 parts:
Like has been mentioned, a CLR is an option, however, I doubt it fulfils options 1.1 (maintainability and low impact), so I'd rather not entertain the option unless the foot really comes down (and i won't be taking any stick for fallout if it occurs).
Everyone's thoughts here are, regardless of if you're shooting the idea down or not, really helpful. Any ideas on solutions are great, as I can at least see how (un)viable they are. Personally, I was against the idea of using SQL as a mailer to start with, but it could be achieved. Now that the goal posts have been moved, it gives me more reason to say "SQL Server isn't the right choice" and build a better case for alternatives. Those saying the same helps me cement that fact and I can, to a degree, include your thoughts in my proposal.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 8:34 am
Grant Fritchey - Tuesday, August 22, 2017 5:21 AMFlat out, they're asking for crazy stuff.
Heh... to coin a phrase... "If you prick me, do I not perform"?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2017 at 8:37 am
Thom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2017 at 8:49 am
Jeff Moden - Tuesday, August 22, 2017 8:37 AMThom,
I'm not sure where you'd set it up but couldn't you simply build a special email address for Outlook, setup a folder for each customer within Outlook that would actually point to the customer folders you were talking about for each customer, setup an "on arrival" rule, and have SQL Server include that in the Blind Copy address?
Honestly, my knowledge of Outlook/Exchange isn't great, so not sure. If the folder needs to be set up manually, that'll be a problem as we have over 400,000 distinct customer references (not including their individual policies references, which they can have many of). Yes, not all of those clients/policies will be current, but it's still the large portion will be.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply