[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sp_send_dbmail randomly emailing an empty body

Posted on 2010-09-13
4
Medium Priority
?
718 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

650 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