Solved

sp_send_dbmail randomly emailing an empty body

Posted on 2010-09-13
4
695 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

13 Experts available now in Live!

Get 1:1 Help Now