SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Sending SMTP Mail using a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 08/05/2002 :  15:36:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
One problem with sp_OACreate is that it requires the System Administrator's fixed server role in order to run. Most DBA's say, "NO WAY!"
Ummm, so does xp_cmdshell (it defaults to sysadmin, but you can grant it to anyone) If the DBA objects to sp_OACreate, they most certainly will do the same over xp_cmdshell. You can do A LOT more damage with xp_cmdshell than you can with sp_OACreate; a diligent security-minded DBA will almost certainly close it off from regular users. In any case, if this is wrapped up in an SP or a SQL job then you can use either method.

Go to Top of Page

yakoo
Constraint Violating Yak Guru

USA
312 Posts

Posted - 08/06/2002 :  14:19:41  Show Profile  Reply with Quote
I just came across this thread and figured I would add in my input on what I have done in the past to get SQL to send SMTP messages from a queue.

1. I utilize a database table that stores RecipentEmail [varchar(128)], subject [varchar(255)], body [text], ReplyTo [varchar((128)], SendRequestTime [datetime], Sent[bit], and SentTime [datetime]

2. Every 3 minutes I run a job (sp_ProcessQueue) that Processes this queue table, grabbing the email records that have yet to be sent and have a SendRequestTime less than GetDate().

3. sp_ProcessQueue then executes sp_SendMail which attempts to send the email out. If sp_SendMail returns true than my queue table is updated by setting Sent to True and SentTime to GetDate for the successful email. If sp_SendMail is false then nothing happens to the email in the queue.


I use JMail as the SMTP Component. This component has a method called AppendText that will allow you to get over the varchar limitations of sp_OAMethod (which I believe is 6000 characters).


CREATE PROCEDURE sp_ProcessQueue
as

DECLARE @ID integer, @Email varchar(128), @Subject varchar(255), @BodyLen integer, @ReplyTo varchar(128), @From varchar(128)

SELECT @From = 'yakoo@sqlteam.com'

DECLARE @ReturnValue varchar(5)

DECLARE WaitingMail CURSOR FOR
SELECT ID, RecpientEmail, Subject, DATALENGTH(Body), ReplyTo
FROM QueueMail
WHERE GetDate() > SendRequestTime and Sent = 0 and Len(RecpientEmail) > 1

OPEN WaitingMail

FETCH NEXT FROM WaitingMail INTO @ID, @Email, @Subject, @BodyLen, @ReplyTo
WHILE @@FETCH_STATUS = 0 BEGIN
Exec sp_SendMail @ID, @From, @Email, @Subject, @BodyLen, @ReplyTo, @return = @ReturnValue OUTPUT
If @ReturnValue = 'TRUE' BEGIN
UPDATE QueueMail
SET Sent = 1,
SentTime = GetDate()
WHERE ID = @ID
END
FETCH NEXT FROM WaitingMail INTO @SendMailID, @RecpEmail, @Subject, @BodyLen, @ReplyTo
END
CLOSE WaitingMail
DEALLOCATE WaitingMail
GO

CREATE Procedure sp_SendMail (
@ID integer,
@From varchar(128),
@Email varchar(128),
@Subject varchar(300),
@BodyLen integer,
@ReplyTo varchar(128),
@return varchar(255) OUTPUT
)
AS
DECLARE @object int, @hr int, @PartLen int, @property varchar(255)
DECLARE @BodyPart varchar(4000)
-- Create an object.
EXEC @hr = sp_OACreate 'JMail.Message', @object OUT
EXEC @hr = sp_OASetProperty @object, 'Subject',@subject
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'ReplyTo', @replyto
EXEC @hr = sp_OASetProperty @object, 'Silent','true'
EXEC @hr = sp_OAMethod @object, 'AddRecipient', null, @Email

DECLARE @totalBytes integer, @charCount integer
SELECT @charCount = 0, @totalBytes = 0
WHILE @totalBytes < @BodyLen BEGIN
SELECT @BodyPart = SUBSTRING(Body, @CharCount + 1, 4000) FROM QueueMail WHERE ID = @ID
EXEC @hr = sp_OAMethod @object, 'AppendText', null, @BodyPart
SELECT @CharCount = @CharCount + Len(@BodyPart)
SELECT @totalBytes = @totalBytes + DATALENGTH(@BodyPart)
END
EXEC @hr = sp_OAMethod @object, 'Send("mail.sqlteam.com")' , @return OUT
GO



hope this helps

Got SQL?
Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  06:18:35  Show Profile  Visit oddjones's Homepage  Reply with Quote
Sorry - I read the thread and got really excited by the prospect of Damain's second article... but didn't see it!

I'm trying to send the contents of a temporary table via email... I can do it via sqlmail using xp_sendmail ...

(exec master.dbo.xp_sendmail 'foo@fo0.bar',@query='select * from CDS.dbo.tbl_mailtemp')

This generates a mail with all my required text - trouble is, the formatting is really nasty and there doesn't appear to be anything I can do about it.

I want to send the contents of the table via a SP using CDO - but the contents of my table is > 8000 Chars... I can't read it all into a text variable, because I can't use text variables in a stored procedure. If I send the select statement to the @body variable in my send mail SP, then the body of my email consists of the select statement (!) HELP!!!


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/04/2002 :  07:52:24  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi there

Is this http://www.sqlteam.com/item.asp?ItemID=5908 the one you want ?

You could insert from your temp table into a queue table and use that.

Does that help ?

Damian
Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  08:01:08  Show Profile  Visit oddjones's Homepage  Reply with Quote
Many thanks for replying so promptly - I read this article earlier today and it may well be the answer to my problem - but I discounted it because of the following...

Declare @FromName VarChar(200),
@FromAddress VarChar(200),
@Subject VarChar(200),
@Body VarChar(200)

Select @FromName = 'Me',
Select @FromAddress = 'me@mydomain.com'
Select @Subject = 'Hi there'
Select @Body = 'The body of the message'

--Insert mail into queue table
INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body)
Select
@FromName, @FromAddress, UserFirstName + ' ' + UserSurname,
UserEmail, @Subject, @Body
FROM
Users


... the @body variable is of limited length (it's set to a varchar(200)) - now, if that @body can be the results of my SELECT statement (ie SELECT * FROM tbl_mailbody) - then I'm away and dancing - if not, then I'm still stuck fast...

Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  08:08:30  Show Profile  Visit oddjones's Homepage  Reply with Quote
(sorry) - not sure why my reply got truncated...

What I was trying to say was - if the @body variable can contain the results of my SELECT statement (ie the entire contents of my "mailbody" table from [SELECT * FROM tbl_mailbody] then I'm away and dancing... if not, then I'm still stuck fast with a deadline looming

"Simplicity is the ultimate sophistication."

Leonardo da Vinci
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/04/2002 :  08:22:10  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

I'm not sure what the problem is here. Can you make the mailbody a text column and insert into that ?


Damian
Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  08:39:20  Show Profile  Visit oddjones's Homepage  Reply with Quote
Merkin - thanks for sticking with me here... OK, please excuse lots of (bad) SQL coming your way...

FIRSTLY... here's my SP.

It loops through my "calls" table and concatenates the contents of
each recod into one long line, performs some rudimentary formatting
and saves it as "reportline" in my mailtemp table. - each record
works out about 3000 chrs long (nasty)

Once it's built the mailtemp table, it calls a sendMAIL SP to send an
email (I'm currently using http://www.swynk.com/downloads/sp_sendSMTPmail.sql).

(Here's that SQL)

CREATE PROCEDURE sp_send_report

@Dave [varchar](8000) ,
@callid [nvarchar](50),
@operator [nvarchar] (36),
@takentime [nvarchar] (10),
@takendate [nvarchar] (10),
@UPRN [nvarchar] (10),
@BPArea [nvarchar](10),
@callerName [nvarchar] (50),
@ContactTel [nvarchar] (50),
@Address [nvarchar] (200),
@FaultID [nvarchar] (10),
@faultType [nvarchar] (50),
@reportedFault [nvarchar] (1000),
@cct [nvarchar](10),
@contractor [nvarchar] (50),
@notes [nvarchar] (4000),
@abortive [nvarchar] (1),
@mailbody [varchar](8000)


AS

DECLARE dave CURSOR FOR
SELECT * FROM tbl_calls

OPEN dave

FETCH NEXT FROM dave into @callid,@operator,@takentime,@takendate,@UPRN,@BPArea,@callerName,@ContactTel,@Address,
@FaultID,@faultType,@reportedFault,@cct,@Contractor,@notes,@abortive

WHILE @@FETCH_STATUS = 0
BEGIN

SET @dave=@dave+ ('CALL ID: '+ @callid + CHAR(10) + '----------------------' +
CHAR(10) +'operator:'+@operator +', Taken on: '+ @takentime+' at: '+@takendate +
CHAR(10)+ '----------------------' + CHAR(10) +'CDS Property Reference: '+@UPRN+
CHAR(10)+'Caller Name: '+ISNULL(@callerName,'n/a')+' Tel: '+ISNULL(@contactTel,'n/a') +
CHAR(10)+ '----------------------' + CHAR(10) +'Address: '+ISNULL(RTRIM(@Address),'n/a')+
CHAR(10) + '----------------------' + CHAR(10) +'Fault Type: '+ISNULL(RTRIM(@faultType), 'n/a') +
CHAR(10) + '----------------------' + CHAR(10) +'Reported Fault: '+ISNULL(RTRIM(@reportedFault), 'n/a')+
CHAR(10) + '----------------------' + CHAR(10) +'Contractor Assigned: '+ ISNULL(RTRIM(@Contractor),'n/a') +
CHAR(10) + '----------------------' + CHAR(10) +'Notes: '+ISNULL(RTRIM(@Notes),'n/a')+
CHAR(10) + '----------------------'+ CHAR(10) +'Abortive Call?: '+ISNULL(@Abortive,1)+' (0=No, 1=Yes)' +
CHAR(10) + '----------------------' + CHAR(10) + CHAR(10)+ CHAR(10)+ CHAR(10))
INSERT INTO tbl_mailtemp (reportline)
VALUES (RTRIM(@dave))
FETCH NEXT FROM dave into @callid,@operator,@takentime,@takendate,@UPRN,@BPArea,@callerName,@ContactTel,@Address,
@FaultID,@faultType,@reportedFault,@cct,@Contractor,@notes,@abortive
SET @dave=''
END

exec sp_sendSMTPmail 'dominic.jones@liverpool.gov.uk', 'Testing', @dave,
@cc='',
@Importance=1,
@Attachments=''


What I want to do is send the whole contents of my mailtemp table as
the body of the email - but what happens is, if I send
@body='SELECT * FROM tbl_mailtemp' then my email body consists of the select statement - AGH!

____________________________________________
"Simplicity is the ultimate sophistication."
Leonardo da Vinci

<edit> to fix that godawful scrolling off into the sunset </edit>

Edited by - robvolk on 10/18/2002 20:32:25
Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  08:41:33  Show Profile  Visit oddjones's Homepage  Reply with Quote
NOW I Broke the forum!

Jeez - have you ever had one of those Fridays?

____________________________________________
"Simplicity is the ultimate sophistication."
Leonardo da Vinci
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/04/2002 :  09:14:18  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

It's too late here (Sydney) for me to read that code
Here is a thought, BCP the results of your table out to a temp file, then modify the mailqueue code to read the file and make that the mail body.

What do you think ?

Damian
Go to Top of Page

oddjones
Starting Member

United Kingdom
11 Posts

Posted - 10/04/2002 :  09:39:09  Show Profile  Visit oddjones's Homepage  Reply with Quote
That's a very polite way of putting it ;) - I figured you must be working late - listen, thank you so much - I've been hacking that code around since I posted it (wish there was an undo!) - Your suggestion is one possibility that I'll look into now that I can understand my own code... Go get some sleep and I'll give it my best shot. - If there are more posts from me tomorrow then you'll know I'm still battering my head on the b*st*rd.

Thanks again (and sorry for that long row of code!)

____________________________________________
"Simplicity is the ultimate sophistication."
Leonardo da Vinci
Go to Top of Page

rdilio
Starting Member

USA
1 Posts

Posted - 10/17/2002 :  12:39:22  Show Profile  Visit rdilio's Homepage  Reply with Quote
Hey Merk, I wanted to say thanks for putting article out here. It's helped us at our company send E-mails through SQL,
however we did hit a road block with the 8000 character limitation when sending large HTML formatted messages.

I've read the Mail Que article as well, but still can't figure out how to get around the body of my message being truncated when it's sent.

Can you please help, we need to get around this for a major project our team is work on.

If there is any work around you could provide, it would be greatly appreciated!



Edited by - rdilio on 10/17/2002 12:40:35
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/17/2002 :  19:13:30  Show Profile  Visit Merkin's Homepage  Reply with Quote
Do I get a royalty from your major product ?

You haven't really given enough info to help...but...
You are probably out of luck with this method. The mail queue could work for you. You could make the mail body a text field and insert into that.

Does that help ?

Damian
Go to Top of Page

SamC
White Water Yakist

USA
3462 Posts

Posted - 10/17/2002 :  22:47:22  Show Profile  Reply with Quote
I've been running a CDONTs SP for a month now. No complaints.

An improvement would be to send one email with a long list of TO, CC or BCC addresses. Has anyone researched the limits on the number of addresses that could be added using

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress2

From the post of "amolgokhale" on page 3?

I'd like to see the documentation on AddRecipient , (does it work?) and is there a method for TO, CC and BCC?

Sam

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 10/18/2002 :  19:57:40  Show Profile  Reply with Quote
quote:
You can do A LOT more damage with xp_cmdshell than you can with sp_OACreate


What more damage can you do than creating a file system object and deleting all the files, or copying a dll to machine installing it and then poof you have full control, or even better using CDONT'S and SQL SERVER TO impersonate or send emails with fake from addresses.





Edited by - ValterBorges on 10/18/2002 20:00:40
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 10/18/2002 :  20:22:25  Show Profile  Send aiken an ICQ Message  Reply with Quote
I should caution people that we saw heavily use of either CDONTS *or* AspMail from within stored procedures cause periodic SQL server crashes (the dreaded "cannot create thread process_loginread" error). We traced it to using the components from stored procedures after examining logs and noting that the server was crashing after almost identical numbers of emails sent (about 95,000), regardless of how long it took to get to that milestone.

I've redesigned the app to stuff outgoing mail into a table, and then a C# app harvests from that table and sends the actual mail, deleting it from the "outgoing" table. We're up to well over 500,000 emails with this system and everything is good again. It's cleaner than instantiating activeX from within SQL server anyways, and a bad component version wouldn't hose our SQL server.

Cheers
-b

Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 10/18/2002 :  20:27:38  Show Profile  Visit robvolk's Homepage  Reply with Quote
Formatting a hard drive comes to mind...

There are a number of other command-line functions that won't really damage anything but can screw things up on the server, like the ACL utilities and net start/stop. net send is possibly worse than email messages, because they're immediate, and net share can make server resources available to all connected clients. Stuff like that can be done using COM objects but they are much easier to do using the command line.

Go to Top of Page

Michael1047
Starting Member

USA
3 Posts

Posted - 11/25/2002 :  18:05:29  Show Profile  Visit Michael1047's Homepage  Reply with Quote
quote:

quote:
One problem with sp_OACreate is that it requires the System Administrator's fixed server role in order to run. Most DBA's say, "NO WAY!"
Ummm, so does xp_cmdshell (it defaults to sysadmin, but you can grant it to anyone) If the DBA objects to sp_OACreate, they most certainly will do the same over xp_cmdshell. You can do A LOT more damage with xp_cmdshell than you can with sp_OACreate; a diligent security-minded DBA will almost certainly close it off from regular users. In any case, if this is wrapped up in an SP or a SQL job then you can use either method.





Sorry for the long response time. Certain priv's can be assigned to xp_cmdshell. Let's say you have a web front end needing some data which is sourced from some cool .exe out on your lan. Use Windows Auth to validate the user permissions and use SQL auth for the application to use xp_cmdshell.

Makes a very nice pair if you use domain groups.



Michael
Go to Top of Page

benc007
Starting Member

6 Posts

Posted - 05/22/2004 :  03:42:07  Show Profile  Reply with Quote
I am having a similar problem, but the solution is probably much easier. I am new to using sql server stored procedures and CDONTS to send HTML email. I am using "DECLARE @body VARCHAR(8000)", and the stored procedure compiles and sends the email.

When I look at the email in my mailbox, the email gets cut off around 15,000 characters (I have over 20,000 characters in my HTML code).

I tried using "DECLARE @body TEXT" instead of "DECLARE @body VARCHAR(8000)", but I get the error:

"The text, ntext, and image data types are invalid for local variables."

Please help. Any suggestions or information would be appreciated. Thank you in advance.


Ben
benc007@sbcglobal.net



---------- MY CODE ---------------

CREATE PROCEDURE sp_SendNewsletter
AS

DECLARE @result INT
DECLARE @object INT

DECLARE @from VARCHAR(500)
DECLARE @to VARCHAR(500)
DECLARE @bcc VARCHAR(500)
DECLARE @subject VARCHAR(1000)
DECLARE @body VARCHAR(8000)
--The text, ntext, and image data types are invalid for local variables.
--DECLARE @body TEXT


BEGIN
Print 'Sending Test Newletter to 1 Email Address'


--Create the object, if @result <> 0, there is an error.
PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT


IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END

--HTML email
EXEC @result = sp_OASetProperty @object, 'MailFormat',0
EXEC @result = sp_OASetProperty @object, 'BodyFormat',0

--Send Email using Send method, if @result <> 0, there is an error.
PRINT 'Sending the message using the Send method'

set @from = 'My Newsletter <newsletter@abc.com>'
set @to = 'test@abc.com'
set @subject = 'My Newsletter'

-- ****************************** EMAIL CONTENT *************************************


--the store procedure compiles and works -> Problem is the HTML code
--in @body gets cut off (around 15,000 characters)
set @body = '<HTML><HEAD>Whole bunch of HTML here (over 20,000 characters)</HEAD><BODY>'



-- *************************** END OF EMAIL CONTENT *********************************

EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body, 0
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END

--Destroy the object, if @result <> 0, there is an error.
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END

Print 'Sending Test Newletter to 1 Email Address - DONE!'


END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 05/26/2004 :  17:50:35  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Okay, I give up. How are you putting 20,000 characters into a variable defined as only 8000 bytes in size? And don't you think that 20,000 characters (even only 8,000) is quite a lot to be sending someone. It certainly is quite a bit to try to generate out of a database. I would think this would be the perfect place to switch to an external application that can query the SQL Server for the mailing list but that the outside application builds the message itself. But still, 20,000 is a lot of stuff to plop into a bulk mailing.

--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000