Varchar 8000 (AGAIN... sort of)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Varchar 8000 (AGAIN... sort of)

Author

Topic

syoung68
Starting Member

6 Posts

Posted - 2006-01-10 : 16:29:41

I am trying to dynamically build an html email that is stored in MSSQL until approved for sending. I have created a "queue" table with a mailSubject varchar(100) and mailBody text fields, among others. I am attempting to write a store procedure that pulls from 3 separate tables to build the email. The problem is that the mailBody will eventually be more than 8000 characters and I am not sure how to get around this.

First the SP gets the subject and body from one table and inserts into the appropriate fields in the queue table... no sweat.

Then I want to perform 2 Replaces. In the mailBody template I have 2 tags (%%greeting%%, and %%content%%) I want to replace these tags with data pulled from the 2 other tables. The greeting is a varchar (1000) and the content is a weekly generated link list that could be 8000 characters by itself.

This is what I have so far and the replace is giving me an error

------------------------------------------------------------------------------------------
DECLARE @QueueID int
DECLARE @templateID int
DECLARE @templateSubject varchar (100)
DECLARE @templateBody varchar (8000)
DECLARE @Greeting varchar (1000)

SELECT @templateID = ID FROM mailTemplates WHERE (ID = 2)
SELECT @templateSubject = emailSubject FROM mailTemplates WHERE WHERE (ID = 2)
SELECT @templateBody = emailBody FROM mailTemplates WHERE WHERE (ID = 2)

INSERT INTO mailQueue (listID, Subject, Body, sendDate) VALUES (2, @templateSubject, @templateBody, GETDATE() + 1)

SET @QueueID = @@IDENTITY

SELECT @Greeting = addOnText FROM mailTemplateAddOns WHERE (templateID = @templateID) AND (typeID = 1)

REPLACE((SELECT Body FROM mailQueue WHERE ID = @QueueID), '%%greeting%%', @Greeting)

------------------------------------------------------------------------------------------

I am not sure if I can even do a SELECT from within a REPLACE

Any help would be appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-10 : 16:34:58

replace returns a string - it doesn't do anything to update data - maybe you want

update mailQueue
set Body = replace(Body, '%%greeting%%', @Greeting)
WHERE ID = @QueueID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-10 : 16:50:31

If you know the body of the email will be more than 8000 characters then you could have multiple rows for those that break the 8000 barrier, and return them both as the result of a query instead of trying to create the "body string".

syoung68
Starting Member

6 Posts

Posted - 2006-01-10 : 17:45:06

Ok, first nr thank you. I have been staring at this for too long. druer, the problem with dividing it into multiple strings is that our email engine can only pull from one feild.

The update was my obvios error, but there is more to it than a simple update. Since it is a text feild UPDATE will not work. After some more digging, i found this...

NOTE: I changed the tag in the template to ||greeting||
--------------------------------------------------------------------------------------------
DECLARE @queueBody binary(16)
DECLARE @GreetingPos int
SELECT @GreetingPos = PATINDEX('%||greeting||%', Body) -1 FROM mailQueue WHERE ID = @QueueID

SELECT @queueBody = TEXTPTR(Body) FROM mailQueue WHERE ID = @QueueID
UPDATETEXT mailQueue.Body @queueBody @GreetingPos 12 @Greeting

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources