Where to store a letter

Question

Where to store a letter

david.gray

SSC Eights!

Points: 968
More actions
September 5, 2008 at 6:43 am

#192472

Hi,
Replicating a procedure that exists on our legacy system which takes a letter template,
performs some substitution (mail merge) and creates an email. This is not on a Windows platform.
I need to store this letter in a text field of some description, read it using SQL Server 2000 & 2005
perform the substitution and then send out an email.
e.g.
"Dear , Thanks for your subscription... "
This all needs to run from a sQL agent job.
I know how to do the substitution etc but am not sure of the best repository for this data item.
Any thoughts?
I will only have access to T-SQL, not any .net tools
Regards
Dave

Viewing 7 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You can store it in a VARCHAR(MAX) or NVARCHAR(MAX) field and it will be relatively easy to manage the substitution with REPLACE.

I understand you have some project constraints, but Reporting Services is probably a better way to handle this. "Merging" and emailing a report to someone is what it is made for.

sgmunson SSC Guru Points: 110651 More actions · Answer 2

I agree with Michael. Investigate "Data Driven Subscriptions", as I think that might be exactly what you need. Then you can create your letter as a report, include a company logo, etc., and even provide a T-SQL query that will identify the recipients. Given an appropriate data table, each recipient could receive the report in a different format, e.g. Adobe PDF, etc.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 3

This is complicated by having to make it SQL 2K compliant. If you were only using 2K5 you could use [n]varchar(MAX) and be set.

The only other option IMHO is to use a varchar(8000) or nvarchar(4000) column and a sequence column. Then concatenate them together after doing your string replacement somehow when calling your mail procedure.

Gary Johnson
Sr Database Engineer

John Beggs SSCommitted Points: 1921 More actions · Answer 4

If you go the Reporting Services route, keep in mind that Data Driven Subscriptions are not supported in Standard or Express versions.

It is pretty easy to create your own "Data Driver" in .Net, not so sure how easy it would be with just SSIS or straight T-SQL, as I haven't tried it.

GSquared SSC Guru Points: 260824 More actions · Answer 5

SSIS and DBMail could definitely do this. Not so sure about how to do it in SQL 2000. Been too long since I used that, and I never did use DTS for anything like this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

david.gray SSC Eights! Points: 968 More actions · Answer 6

Hi,

Thanks for all the responses, plenty of food for thought there.

We will be ditching SQL2000 before the end of the year but this project needs to be live my end

of September so I have to cater for both SQL versions.

Never dabbled with Reporting Services, I guess we will look into that after

waving goodbye to SQL2K.

Cheers

Dave