Solved

sp_send_dbmail randomly emailing an empty body

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

840 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