Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 748
  • Last Modified:

sp_send_dbmail randomly emailing an empty body

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
uwhc
Asked:
uwhc
  • 2
  • 2
1 Solution
 
gothamiteCommented:
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
 
uwhcAuthor Commented:
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
 
gothamiteCommented:
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
 
uwhcAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now