January 19, 2007 at 1:14 pm
I have created the following trigger:
ALTER
TRIGGER [dbo].[EmailFormRequest] ON [dbo].[mpi_Client]
AFTER
INSERT, UPDATE
AS
BEGIN
TRUNCATE TABLE dbo.mpi_HoldClientID
INSERT INTO dbo.mpi_HoldClientID
SELECT i.ID
FROM inserted AS i
EXEC msdb.dbo.sp_send_dbmail
@recipients
= 'pozer@merion.com',
@subject
= 'Custom Promotions - Email Sign Up Request',
@query
= '
SELECT
[Date Entered] = pr.CreateDate,
[Profile Type] = ''Contact Us'',
[First Name] = cl.FirstName,
[Last Name] = cl.LastName,
[Company Name] = ISNULL(cl.CompanyName,''''),
[Department] = ISNULL(cl.Department,''''),
[Job Title] = ISNULL(cl.JobTitle,''''),
[Phone Number] = ISNULL(cl.PhoneNumber,''''),
[Phone Extension] = ISNULL(cl.PhoneExtension,''''),
[Email] = cl.Email,
[Preferred Contact Method] =
CASE
WHEN cl.ContactMethodID IS NULL THEN ''(none)''
WHEN cl.ContactMethodID = 1 THEN ''Phone''
ELSE ''Email''
END,
[Referral Method] = rm.[Name],
[Wants Mailing List] =
CASE
WHEN cl.MailingList = 1 THEN ''Yes''
ELSE ''No''
END
FROM AHS_QA.dbo.mpi_HoldClientID AS hc
JOIN AHS_QA.dbo.mpi_Client AS cl ON hc.ID = cl.ID
LEFT JOIN AHS_QA.dbo.mpi_ClientXProfileResponse AS cpr ON cl.ID = cpr.ClientID
LEFT JOIN AHS_QA.dbo.mpi_ProfileResponse AS pr ON cpr.ProfileResponseID = pr.ID
JOIN AHS_QA.dbo.mpi_ReferralMethod AS rm ON cl.ReferralMethodID = rm.ID
'
,
@attach_query_result_as_file
= 0,
@query_result_width
= 256
END
When I run an update statement to test the trigger I get an open transaction that never ends. I actually have to stop MSSQLSERVER service and restart. Any one have any thoughts? I have written triggers like this before and never had a problem. It seems that it doesn't seem to like the combination of the Insert Into and the DBMail Statement.
Thanks ahead of time for any help you can give.
January 19, 2007 at 1:34 pm
Did you test the database mail first before using in TRIGGER?
If trigger is not working properly then transaction will be left open..
you don't need to restart the sql to close open transaction...
Use DBCC OPENTRAN and Kill the offend spid in the ouput of DBCC...
It is not advisable to Sending emails using triggers...
MohammedU
Microsoft SQL Server MVP
January 19, 2007 at 2:41 pm
In 2005 this really should be accomplished by using service broker, IMHO. It's a lot more complicated but because it uses a queuing system the rest of your transaction can carry on even if the mail system isn't working quite right.
January 21, 2007 at 8:29 am
Database Mail is working fine. I have other items using it. Also I can't kill the transaction which is why I have to stop services. Service broker isn't really an option right now. This is just a temporary fix until the developers can program this. Any other thoughts?
January 22, 2007 at 12:02 am
Instead of sending the mail in a trigger you can add the column called Flag bit..when you insert or update flag values can be zero ...
Create another procedure and schedule it to run every minute to check the flag =0 and send an email and update the flag to 1 after sending the mail...
This way you will have one minute latency in sending an email ...
MohammedU
Microsoft SQL Server MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply