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

HELP! sp_send_dbmail error

I'm having trouble sending an email through DBMail. I'm logged in a a SQL Login user named "ServiceUser". I have added this user to the msdb database and as a rolemember to "DatabaseMailUserRole"

USE [msdb]

GO

CREATEUSER [ServiceUser] FORLOGIN [ServiceUser] WITHDEFAULT_SCHEMA=[guest]

GO

USE [msdb]

GO

EXEC sp_addrolemember N'DatabaseMailUserRole', N'ServiceUser'

GO

When I try to send the email:

exec msdb.dbo.sp_send_dbmail

@.profile_name=@.DatabaseMailProfileToUse,

@.recipients=@.EmailAddress,

@.subject=@.Subject,

@.body=@.MessageBody,

@.mailitem_id=@.MYmailitem_id OUTPUT

I get the following error:

Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 62

Could not obtain information about Windows NT group/user 'ServiceUser', error code 0xffff0002.

I executed the following on msdb and it didn't help:

execsp_changedbowner'sa'

xp_logininfo also returns the error:

EXECxp_logininfo'ServiceUser'

What is wrong? HELP!

My database mail profile was not public! It needs to be public or set to private for the user that is going to access it.

HELP! Question on SQL Mail

I am trying to send an email through an SQL Stored
procedure using Lotus Notes system.
I have read the article entitled: "An Introduction to
SQL Mail and SQLAgentMail".
I have a few questions:
1. Can I use SQL Mail even if my mail server is Lotus
Notes?
2. Do I need to install Lotus Notes client where the
SQL Server is?
OR is it: as long as the SQL Server can detect the
Lotus Notes server through the network, I just need to
do the configuration as described in the article?
I would appreciate it if someone can respond as soon as
possible.
Thank you very much.
Regards,
GGCThanks Jens for the links! I am going to check them.
Regards,
GGC

Help! Help!

Hi All,
What would be the best way to log table deletion on a
certain database to a file or email when table deletion
occur.
Thanks in advance!
Tom
Use a tool from www.lumigent.com called Entegra.
Andrew J. Kelly SQL MVP
"tuand2001@.yahoo.com" <anonymous@.discussions.microsoft.com> wrote in message
news:1161401c416a3$b948e8e0$a001280a@.phx.gbl...
> Hi All,
> What would be the best way to log table deletion on a
> certain database to a file or email when table deletion
> occur.
> Thanks in advance!
> Tom

HELP! CDONTS Attachment in SQL server

Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

I have been trying several methods and i'm stuck

Right now, I'm using the next SP:

DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)

DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)

EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT

IF @.Hresult = 0 begin
--SET SOME PROPERTIES

SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY

EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0

--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL

--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end

Thanks in advance for your helpHave you tried the following code for sending the attachment mate?

-- Check for multiple attachments separated by a semi-colon ';'.
If @.vcAttachments is not null
Begin
If right(@.vcAttachments,1) <> ';'
Select @.vcAttachments = @.vcAttachments + '; '
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
While @.iPos > 0
Begin
Select @.vcAttachment = ltrim(rtrim(substring(@.vcAttachments, 1, @.iPos -1)))
Select @.vcAttachments = substring(@.vcAttachments, @.iPos + 1, Len(@.vcAttachments)-@.iPos)
EXEC @.iHr = sp_OAMethod @.iMessageObjId, 'AddAttachment', @.iRtn Out, @.vcAttachment
IF @.iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @.iMessageObjId, @.vcErrSource Out, @.vcErrDescription Out
Select @.vcBody = @.vcBody + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @.vcErrSource + char(13) + char(10) +
@.vcAttachment
End
Select @.iPos = CharIndex(';', @.vcAttachments, 1)
End
End

----------

you can find more info about this at www.sqlservercentral.com

Hope this helps...

have a good one|||Originally posted by saulo70
Hi Everybody,
This is my first post in the forum.
Can someone give me an example how to send an email with an attachment in a SP ?? I have been searching and i haven't found anything useful.

I have been trying several methods and i'm stuck

Right now, I'm using the next SP:

DROP PROCEDURE ST_S_SendMail
GO
CREATE PROCEDURE ST_S_SendMail
(@.FROM NVARCHAR(255),
@.TO NVARCHAR(255),
@.SUBJECT NVARCHAR(255),
@.BODY NVARCHAR(4000))
AS
DECLARE @.Object int
DECLARE @.Hresult int
DECLARE @.ErrorSource varchar (255)
DECLARE @.ErrorDesc varchar (255)
DECLARE @.V_BODY NVARCHAR(4000)

DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)

EXEC @.Hresult = sp_OACreate 'CDONTS.NewMail', @.Object OUT

IF @.Hresult = 0 begin
--SET SOME PROPERTIES

SET @.V_BODY = '' + @.BODY
EXEC @.Hresult = sp_OASetProperty @.Object, 'From', @.FROM
EXEC @.Hresult = sp_OASetProperty @.Object, 'To', @.TO
EXEC @.Hresult = sp_OASetProperty @.Object, 'Subject', @.SUBJECT
EXEC @.Hresult = sp_OASetProperty @.Object, 'Body', @.V_BODY

EXEC @.Hresult = sp_OASetProperty @.Object, 'MailFormat', 0

--CALL ATTACHMENT METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Attachfile', 'C:\Inetpub\wwwroot\desarrollo\PolizasCP\PolC20031 103.txt', 'poliza.txt', 1

--CALL SEND METHOD
EXEC @.Hresult = sp_OAMethod @.Object, 'Send', NULL

--DESTROY THE OBJECT
EXEC @.Hresult = sp_OADestroy @.Object
end
else
begin
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
end

Thanks in advance for your help

Here;s a sp which will make your life easier ...

CREATE PROCEDURE [dbo].[sp_CDOMail]
@.To varchar(1000),
@.From varchar(100),
@.Subject varchar(100),
@.Body varchar(4000),
@.CC varchar(100) = null,
@.BCC varchar(100) = null,
@.FilePath varchar(100) = Null, --inlcude last slash
@.FileName varchar(100) = Null

/************************************************** *******************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

DATE By Descript
------ ------ ----------------
11/01/03 SP --Modified to Send Attachments from the original query designed by Microsoft.

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

Declare @.attachment varchar(200)

set @.attachment = @.FilePath + @.FileName

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'Mozart'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
EXEC @.hr = sp_OASetProperty @.iMsg, 'CC', @.CC
EXEC @.hr = sp_OASetProperty @.iMsg, 'BCC', @.BCC

--Add Attachment if exists.
if @.Attachment <> ''
EXEC @.hr = sp_OAMethod @.iMsg, 'AddAttachment',null, @.Attachment , @.FileName

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
SELECT @.output = @.output + '
Description: ' + @.description
Select @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg

GO

IF you still have problems email me @. shaileshpatangay@.hotmail.com

--shailesh|||Thanks a lot for your help

It's working now

help!

hi all,
i'm having problem with this stored procedure
this doesn't work!
select count(Email)
from Jobseeker
where Email = @.EMAIL and cast(Password as varbinary) = cast(@.PASSWORD as varbinary)

but this works , why? and this one works even if the passwords are in different cases, how do i fix this??
select count(Email)
from Jobseeker
where Email = @.EMAIL and Password = @.PASSWORD

cheers :)If the sort sequence is case insensitive, then string comparisons will be case insensitive as well.

What are you trying to achieve?

Help woth cursor!

Hi,
I have a stored procedure which cycles through a select statement and loads
the results into a cursor. It then sends an email off for each result. I
was wondering if it was possible to group all the results into one email?
My stored procedure is below for reference:-
OPEN surveillance_cursor
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
select @.sbj = 'List of Renewal Dates'
select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Any help on this would be greatly appreciated
Thanks
Damon> I have a stored procedure which cycles through a select statement and
> loads the results into a cursor. It then sends an email off for each
> result. I was wondering if it was possible to group all the results into
> one email?
You'll have to be more specific. Do you mean one e-mail for each unique
@.officer_email, or one e-mail total?|||Create another variable e.g @.allMSG , keep adding the data for every
cursor and then do the "exec sp_sendmail after the cursor has finished.
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Damon" <nonsense@.nononsense.com> wrote in message
news:2F4Ef.71406$zt1.64049@.newsfe5-gui.ntli.net...
> Hi,
> I have a stored procedure which cycles through a select statement and
loads
> the results into a cursor. It then sends an email off for each result. I
> was wondering if it was possible to group all the results into one email?
> My stored procedure is below for reference:-
> OPEN surveillance_cursor
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
> select @.sbj = 'List of Renewal Dates'
> select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
> 'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
> Any help on this would be greatly appreciated
> Thanks
>
> Damon
>

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with UPDATE trigger

I am trying to setup a trigger that sends an email if a field is changed to specific data. The trigger works when ever the field is changed, but I only need an email if the field is changed to 'In Review'
Any help is greatly appreciated.

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain.com',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain.com',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email serverl'
select RC = @.rc
END

goYou need to check for the updated value.|||I'm sorry, but I am new to SQL.
Where and how do I insert CHECK|||Got it.

I added an IF
Here is the code I have if anyone needs

-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[reviewntc]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [reviewntc]
GO

-- Create the trigger
CREATE TRIGGER reviewntc

--indicate which table the trigger is to be executed on
ON CltDue

--indicate that this an UPDATE Trigger
FOR UPDATE
AS

IF UPDATE(CDSTATUS)
BEGIN
set nocount on
--holds the changes
declare @.CDStatus varchar(40), @.CDClientName varchar (40), @.CDEventDesc varchar (40)
--grabs the data that we need
SELECT @.CDStatus = CDStatus, @.CDClientName = CDClientName, @.CDEventDesc = CDEventDesc
FROM inserted
IF @.CDStatus = 'In Review'
BEGIN
declare @.rc int, @.mymessage nvarchar(4000), @.mysubject varchar (4000)
SET @.mymessage = N'The '+@.CDClientName+' "'+@.CDEventDesc+'" project has been changed to '+@.CDStatus+''
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'sender@.domain',
@.FROM_NAME = N'sender',
@.TO = N'rcpt@.domain',
@.subject = N'A project has been changed to "In review"',
@.message = @.mymessage,
@.type = N'text/plain',
@.server = N'email server'
select RC = @.rc

END
END
go