October 13, 2003 at 4:38 am
This query is mainly to do with DTS but hopefully someone might be able to suggest a better way of working....?
I've got a system that sends out emails on SQL via CDONTS. The emails are all html and the code is either stored in a table or hard coded into a stored procedure.
The emails are built on the fly and substitutions made depending on the required content.
The problem I have is that if I need to change the html of the email I need to either change the stored procedure or change the records in the table. What I would really like to do is have SQL import html files held on the server and insert into local tables. This way I can easily amend the format of the emails without mucking about in SQL tables etc. I've tried to get SQL to import the html files but I'm not having much luck. I can see DTS has a HTML Files (Source) connection but I can't get it to work.
Does anyone know if this is possible...or perhaps even a better way of doing it?
Cheers....
October 13, 2003 at 10:33 am
Try this..
CREATE TABLE #file (line varchar(255) null)
INSERT #file EXEC master..xp_cmdshell 'Type D:\temp.html'
SELECT * FROM #file
DROP TABLE #file
October 14, 2003 at 2:54 am
hi!
other suggestion: why do you want to mess around with generating/modifying your html code in your dbms? its just not built for it, and t-sql is somehow a little "stoneage" don't you think?
why not exporting the relevant data you need for generating your emails as XML and use XSL/XSLT for generating the actual HTML code ...
best regards,
chris.
October 14, 2003 at 6:34 am
I agree with Chris. I had a project that required a daily report to be sent out in HTML format via email. I just sucked out XML and did a transform with XSL sending the resulting HTML in the email.
I used VB6 at that time rather than DTS.
VB6 calls sproc.
sproc returns xml
using msxml DOM object, load xsl, load xml.
do a transform on the xml.
send resulting html as body of email.
October 14, 2003 at 10:05 am
Have a look at the following link
http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx
about using vbscript within DTS to generate and email HTML reports
Edited by - maudapa on 10/14/2003 10:06:27 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply