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)
 Trigger that sends an email....

Author  Topic 

eflooks
Starting Member

5 Posts

Posted - 2007-06-19 : 10:54:55
The problem i am having is trying to send and image with that email. Basically, i want to be able to have an email sent to a user that enters there email info and name in then it will send a reply with the image (coupon) .. This is what i have for the trigger:

CREATE TRIGGER EileenTest ON dbo.OrgCouponTestMain
FOR Insert
AS
declare @emailaddress varchar(50)
declare @body varchar(300)
declare @fname varchar(50)
declare @coupon varbinary(8000)


if update(emailaddress)
begin

Select
@emailaddress=EmailAddress,
@fname=FirstName
FROM OrgCouponTestMain


Select
@Coupon= OrgCoupon1
FROM OrgCouponTest2
where CouponID=1

SET @body = 'Thank you ' + @fname + ', Here is the coupon you requested ' + CAST(@coupon AS VARCHAR(8000))

exec master.dbo.xp_sendmail

@recipients = @emailaddress,
@subject ='Organix Coupon',
@message = @body
END

The email will send out, but only with the text no image??? Just some symbols. Any help would be greatly appreciated!






spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 10:56:44
so you want to cast varbinary to varchar? why?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

eflooks
Starting Member

5 Posts

Posted - 2007-06-19 : 11:00:03
Just an idea, cant use image and when i don't use cast i get an error : Error 403 Invaild operator for data type. operator equals add, type equals varchar. Any suggestions?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 11:11:29
you have to add the image as an attachment
and in your html you have to put a link to it.

oh and don't send emails from a trigger
create a service that polls the sql server and sends the emails.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

eflooks
Starting Member

5 Posts

Posted - 2007-06-19 : 11:15:57
hmmm just wanted to see if it could be done from a trigger.. so how would i add the image as an attachment in a trigger?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 12:00:32
as far as i know you can only attach a file to xp_sendmail

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

eflooks
Starting Member

5 Posts

Posted - 2007-06-19 : 12:04:57
hmmm would using a stored procedure work instead? would i be able to send an image through email this way?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-19 : 12:16:55
trigger is a stored procedure of sorts.

the problem is in the xp_sendmail only supporting attaching files and not streams.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 05:26:13
We do this with a scheduled application that sends a Query to SQL Server to find out what emails need sending (and then marks them as "Sent" in the database so they don't get sent a second time!), and the application worries about building HTML and Plain text versions, embedding images, or attaching them; handling SMTP errors, bounces, etc etc etc.

The application is scheduled to run every 10 minutes.

Alternatively pass a data feed to an Email tool - there are some excellent on-line ones, and they are built to get Emails through to Hotmail, AOL, etc. and handle whatever quirks are necessary to stay on a Whitelist ...

Kristen
Go to Top of Page
   

- Advertisement -