Help! Trying to create a trigger in SQL 2000...

Hello All!

I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.

Looks basically like this(This is my test tables):

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

if update(emailaddress)
begin

Select
@.emailaddress=(select EmailAddress from OrgCouponTestMain as str),
@.fname=(select EmailAddress from OrgCouponTestMain as str)
@.Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)

SET @.body= 'Thank you' +' '+ @.fname +' '+ ',Here is the coupon you requested' +' ' + @.coupon
exec master.dbo.xp_sendmail
@.recipients = @.emailaddress,
@.subject = 'Coupon',
@.message = @.body
END

Hello my friend,

Try the following: -

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

Kind regards

Scotty

|||

Hi there,

I've recently developed a functionality at my work place that is very similar to the one you need.

My suggestion is that you make things other way (very much simpler i think):

Instead of coding in T-SQL, you can develop your application in any .NET language. I suggest you to develop a Web Service or an ASP.NET page that retrieves the "destinations" from the SQL Server, loads the coupon and sends it to them.

Then you just have to develop a very simple COM Class that the ONLY thing it does is calling the Web Service (our ASP.NET page) you developed. There is many documentation on the Web on how to call a COM Class from an SQL Trigger / Job.

It's a bit tricky at the beggining but when you figure it out, it's easy as a walk in the park. And remember, when you need to change your application, you just have to change the .NET Web Service that it's called by the COM Class (much more friendly than coding in T-SQL).

Even if this isn't a web server, you can always create a website on localhost to host the service/page.

Hope I've helped you out!

gonzzas

|||

Thank you both for your input, Ask scotty, it didn't work, got the email out but no image, and of course there is that other option, which unless i can figure this out i will be doing instead. Thank you!

|||

So anyone else have an idea? This is what comes up in the email message, notice the symbols instead of the image...

Thank you Eileen, Here is the coupon you requested /

|||

Anyone else have a suggestion? Here is what comes in the email , notice the symbols instead of the image:

Thank you Eileen, Here is the coupon you requested /

|||

Hi,

The problem is focused on how to display the image which saved as Image Data type in database. Because what you get from database directly is binary steam of the image, it can't be displayed in your email directly. What I suggest is pass the steam (and other information ) from Stored Procedure to .NET, and use SqlDataReader to read the image and send the mail to your customer.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image" ?https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531518&SiteID=1

Hope it can helps. Thanks.

sql