Solved

sp_send_dbmail randomly emailing an empty body

Posted on 2010-09-13
4
700 Views
Last Modified: 2012-05-10
I have built a SQL 2K5 trigger that randomly doesn't provide anything in the message body of the e-mail.  I have seen the same thing occur with less important triggers.

The thing common between the affected triggers is that the body uses a variable that has a Join, pulling in data from a 'text' (memo) type field along with other variables.  

Triggers I have on the tables within the Join never have this issue, which is why I suspect some issue with the Join and sp_send_dbmail.
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:		

-- Create date: 8/04/10

-- Description:	Sends an e-mail when the Assignee Notification checkbox is set to 'T'

-- =============================================

ALTER TRIGGER [trgAsgnmtAnalystEmail] 

   ON  [dbo].[Asgnmnt] 

   FOR INSERT,UPDATE

AS 

IF UPDATE(NotifyEmail)

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	declare @CallID varchar(8)

	declare @subject varchar(100)

	declare @body varchar(max)

	declare @subsetfirst varchar(100)

	declare @subsetlast varchar(100)

	declare @subsetorg varchar(30)

	declare @subsetdepartment varchar(100)

	declare @subsetlogincontext varchar(50)

	declare @contactbuilding varchar(35)

	declare @contactroom varchar(30)

	declare @printeridHL varchar(30)

	declare @printeridGF varchar(30)

	declare @org varchar(30)

	declare @calldesc nvarchar(max)

	declare @closedesc nvarchar(max)

	declare @analystemail varchar(100)

	declare @contactphone varchar(30)

	declare @bpam varchar(1)

	declare @assignee varchar(96)

	declare @modby varchar(96)

	declare @dateresolv varchar(10)

	declare @subsetrole varchar(50)

--	declare @eventlog nvarchar(max)

		DECLARE @textptr binary(16)

		DECLARE @sstext varchar(max)



	SELECT @CallID = CallID,



@bpam=NotifyEmail,



@assignee=Assignee,



@org=(select distinct cast(dbo.calllog.organization as varchar(30))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@contactphone=(select distinct cast(dbo.calllog.contactphone as varchar(30))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@contactbuilding=(select distinct cast(dbo.calllog.contactbuilding as varchar(35))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@contactroom=(select distinct cast(dbo.calllog.contactroom as varchar(30))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@printeridHL=(select distinct cast(dbo.calllog.hl_print_queue as varchar(30))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@printeridGF=(select distinct cast(dbo.calllog.ntqueue as varchar(30))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetfirst=(select distinct cast(dbo.subset.firstname as varchar(100))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetlast=(select distinct cast(dbo.subset.lastname as varchar(100))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetorg=(select distinct cast(dbo.subset.employer as varchar(30))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetrole=(select distinct cast(dbo.subset.logincontext as varchar(50))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetdepartment=(select distinct cast(dbo.subset.department as varchar(100))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@subsetlogincontext=(select distinct cast(dbo.subset.logincontext as varchar(50))

From dbo.Subset

	inner JOIN dbo.Asgnmnt 

	ON dbo.Subset.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@calldesc=(select distinct cast(dbo.calllog.calldesc as nvarchar(max))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@closedesc=(select distinct cast(dbo.calllog.closedesc as nvarchar(max))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@modby=(select distinct cast(dbo.calllog.modby as varchar(96))

From dbo.CallLog

	inner JOIN dbo.Asgnmnt 

	ON dbo.CallLog.CallID = (SELECT dbo.Asgnmnt.CallID FROM Inserted)

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),



@analystemail=(select distinct dbo.tracker.EmailName

----Join Tracker to CallLog 

	From dbo.Tracker

	INNER JOIN dbo.Asgnmnt 

	ON dbo.Tracker.LoginID = (Select HEATLogin FROM Inserted)

----where all of these are true

	WHERE			

				dbo.Asgnmnt.CallID = (SELECT CallID FROM Inserted) AND				

				dbo.Asgnmnt.HEATSeq = (SELECT HEATSeq FROM Inserted)),

@dateresolv=DateResolv



	FROM INSERTED

IF (@bpam = 'T' and @assignee <> '' and

	(@dateresolv is null or @dateresolv='' ))

BEGIN		

--update Event Log

		SET @sstext = CHAR(13) + CHAR(10) + cast(GETDATE() as nvarchar) + ' system e-mailed tech/analyst: ' + @assignee + ' due to action by ' + @modby + '. [trgAsgnmtAnalystEmail]' + CHAR(13) + CHAR(10)



		SELECT 

			@textptr = TEXTPTR(Event_Log)

		FROM 

			dbo.CallLog	    

		WHERE 

			dbo.CallLog.CallID = (SELECT CallID FROM Inserted)



		UPDATETEXT dbo.CallLog.Event_Log @textptr 0 0 @sstext;



--send e-mail

SET @subject='HEAT Call ID ' + @CallID + ' assigned to you';



SET @body=

N'<html><body>User: ' + @subsetfirst + ' ' + @subsetlast + ', ' + @subsetorg  + ' ' + @subsetdepartment +

N'<BR>Role: ' + @subsetrole +

N'<P>' + 'Phone: ' + @contactphone +

N'<P>' +

N'Building: ' + @org + ' ' + @contactbuilding + 

N'<BR>' + 'Room: ' + @contactroom +

N'<P>' +

N'Printer - Gordon Flesch ID: ' + @printeridGF +

N'<BR>' +

N'Printer - Health Link ID: ' + @printeridHL +

N'<P>' +

N'<B>Call Description: </B>' +

N'<P>' + REPLACE(@calldesc,CHAR(13) + CHAR(10), '<br/>') +

N'<P>' +

--N'<B>Solution Information: </B>' +

--N'<P>' +

--Cast(@closedesc as NVarchar(max)) +

--N'<P>' +

--N'<B>Event Log: </B>' +

--N'<P>' +

--Cast(@eventlog as NVarchar(max)) +

N'</body></html>'

;





--SET @tableHTML =

--    N'<H1>Work Order Report</H1>' +

--    N'<table border="1">' +

--    N'<tr><th>Work Order ID</th><th>Product ID</th>' +

--    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +

--    N'<th>Expected Revenue</th></tr>' +

--    CAST ( ( SELECT td = wo.WorkOrderID,       '',

--                    td = p.ProductID, '',

--                    td = p.Name, '',

--                    td = wo.OrderQty, '',

--                    td = wo.DueDate, '',

--                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty

--              FROM AdventureWorks2008R2.Production.WorkOrder as wo

--              JOIN AdventureWorks2008R2.Production.Product AS p

--              ON wo.ProductID = p.ProductID

--              WHERE DueDate > '2006-04-30'

--                AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 

--              ORDER BY DueDate ASC,

--                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC

--              FOR XML PATH('tr'), TYPE 

--    ) AS NVARCHAR(MAX) ) +

--    N'</table>' ;









EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQL2005_BPAMHEAT',

    @recipients = @analystemail,

    @body = @body,

    @subject = @subject,

	@body_format='HTML',

	@importance='High' ;



END

END

Open in new window

0
Comment
Question by:uwhc
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:gothamite
ID: 33667005
could it be that one or more of the variables/columns that get concatenated in your trigger are NULL? This could make the whole thing null. To prove this you can use

SET CONCAT_NULL_YIELDS_NULL OFF

but you probably want to use

ISNULL(the_suspect_column_or_var, ''')

(for character data types)
0
 

Author Comment

by:uwhc
ID: 33673823
gothamite, where would:
SET CONCAT_NULL_YIELDS_NULL OFF

be placed?

And would I have to use
ISNULL(the_suspect_column_or_var, ''')

for any field that may possibly have a null value?
0
 
LVL 8

Accepted Solution

by:
gothamite earned 500 total points
ID: 33682457
try putting SET CONCAT_NULL_YIELDS_NULL OFF
just before the SET NOCOUNT ON in your trigger

...

yes that is how you would have to use the ISNULL function
0
 

Author Closing Comment

by:uwhc
ID: 33682522
Won't know for sure if this resolves the issue due to the random nature of the issue.  Seems to be working for now though.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now