hi 2 everybody,first of all - excuse my very bad english - i hope u are able to even recognize my problem ;-)Got a problem on sending emails via sqlserver 7(I´m sure, it´s quite easy to solve for all the experts out here *smile*)what I want to do:we are developing a service-plattform for distributing news-messages to subscribers.the user should be able to - select a group of users(emails)- enter a mail-message- define date and time for sending the mailI´m using ms-sql-server 7 on win2k and aspmail4 from www.serverobjects.comThe way I tried was the following concept/idea (hopefully nt too stupid..)- generate a table "mail-prepare" with mail-message (ok.. only a reference via uniqeidentifier - but thats not the problem) and sending time- generate a stred-procedure & sql-server-job (which runs on low cpu-usage or once an hour)- generate a table "mail-send"- use an update-trigger for "mail-send" to generate the mailso I prepared everything and it works fine - until I try to use a "text"-variable for the message-body.(aaaahhhh.. I can see you smile because it´s the old "truncate-to-255-problem" and the "can´t-use-text-as-a-local-variable-problem").a varchar(8000) won´t solve my problem, because it could be a limitation for complex html-mailsyes - I don´t like html-mails too - but our customer has to integrate this feature to be competitive....and - ok, it didn´t even work properly with th varchar(8000) - but i think there were just problems because of the time I was working on it *g*it´s a little bit annoying, that i can use a stored-proc like CREATE PROCEDURE sp_AspMail @RecptName varchar(50),@RecptAddress varchar(50),@Subject varchar(50),@BodyText text AS declare @pObj int, @AspMail int declare @source varchar(30), @desc varchar(200) declare @property varchar(30), @result int [...cut...] exec @AspMail=sp_OASetProperty @pObj, "BodyText",@BodyText if @AspMail <> 0 goto Err [...cut...]
which works perfect with a "text"-var, but not be able to use a "text"-variable IN the procedure itself... :-(do you know any workaround?and of course, im not sure if this is all to oversized/complicated..hope, you´ve got some ideas.thanks in advancejuergen 
"text" in stored procedure/scheduled mail-send
Posted - 2002-03-27 : 19:38:34
You have the mail in a table, I would suggest sending using a script task instead of from a procedure. That will get you around the 255 character limit.Have a look at this for some ideas