Mail and sp_OASetProperty - SQL Server Forums

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

SQL Server Development (2000)

Mail and sp_OASetProperty

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2003-02-07 : 11:36:46

I have successfully been using a varient of the SQL Mail stored proc found here for ages ... but I have come across a problem that I can't seem to work around

I need to send an email with a bodyText of > 8000 chars (this lists all the current stock items that are out of stock and there are a lot of items!)

I blow the 8000 char limit of a varchar object, so I thought I'd dump the output to rows of a temp table and do something like the following

exec @returnCode = sp_OASetProperty @object, 'bodytext',
select body from @temp where [id] = 1 order by [order] desc

but I get a syntax error complaining about the select

does anyone have any ideas about how I can create an email inside of sql server that has a body of more than 8000 chars?

Points / suggestions most welcome.

skillile
Posting Yak Master

208 Posts

Posted - 2003-02-08 : 23:07:18

If you must use SQL to send mail.

We have seperated our body field into another table ie.

emailid
to varchar(x)
from varchar(x)
subject varchar(x)
....

and

emailid
body varchar(put your most common value here)
datecreated datetime

this gives us a 1 to X on the body table and we combine all with a DTS pkg and send entire email. NOTE: on the combine you combine by datecreated ASC. ie.

SET @body = ''
SELECT
@body = @body + ' ' + body

FROM dbo.tblbody
WHERE emailid = x
ORDER BY datecreated ASC

something like that

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources