sp_send_dbmail Arrives Blank

I have a trigger that sends an email when a record is created in a table.  The email executes a query with several variables that are then included in the body of the email.  When I include 5 or 6 variables in the body and comment out the rest, the email arrives and reads as expected.  But when I try to include all the variables in the email the email arrives blank.  I can include any group of variables in the 6, but once I add a few more, regardless of which ones I add, the email arives blank.  The only exception to this is the last variable which holds an xml message.  That one does not come through at all even if it's the only one in message.  Any suggestions?
ALTER TRIGGER [SCRIBE].[GreenvilleImportAlert] ON [SCRIBE].[ER_STD_GREENVILLE] AFTER INSERT AS
 
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @SourceRows int
DECLARE @StepNumber smallint 
DECLARE @Table varchar(32)
DECLARE @Operation char(2)
DECLARE @RowNumber int
DECLARE @Error varchar(4000)
DECLARE @Fname varchar(50)
DECLARE @Lname varchar(50)
DECLARE @Street1 varchar(50)
DECLARE @Street2 varchar(50)
DECLARE @City varchar(50)
DECLARE @State varchar(50)
DECLARE @Zip varchar(20)
DECLARE @IDName varchar(50)
DECLARE @IDValue varchar(50)
DECLARE @OppType varchar(50)
DECLARE @Term varchar(50)
DECLARE @OppStatus varchar(50)
DECLARE @Act1Value varchar(50)
DECLARE @Act1Date varchar(50)
DECLARE @Act1Desc varchar(500)
DECLARE @Act2Value varchar(50)
DECLARE @Act2Date varchar(50)
DECLARE @Act2Desc varchar(500)
DECLARE @XML varchar(MAX)
 
 
SELECT                Top 1 @StartTime = SCRIBE.USERVIEW_EXECUTIONLOG.STARTTIME, @EndTime = SCRIBE.USERVIEW_EXECUTIONLOG.ENDTIME, 
                      @SourceRows = SCRIBE.USERVIEW_EXECUTIONLOG.SOURCEROWS, 
                      @StepNumber = SCRIBE.USERVIEW_TRANSACTIONERRORS.STEPNUMBER, @Table = SCRIBE.USERVIEW_TRANSACTIONERRORS.TARGETTABLE, 
                      @Operation = SCRIBE.USERVIEW_TRANSACTIONERRORS.OPERATION, @RowNumber = SCRIBE.ER_STD_GREENVILLE.REJECTROWNUM, @Error = SCRIBE.ER_STD_GREENVILLE.KSERRORMSG, @Fname = SCRIBE.ER_STD_GREENVILLE.FirstName_Continuum422_Perso_006, 
                      @Lname = SCRIBE.ER_STD_GREENVILLE.LastName_Continuum422_Person_008, @Street1 = SCRIBE.ER_STD_GREENVILLE.Address_01_Street1_Continuum_049, 
                      @Street2 = SCRIBE.ER_STD_GREENVILLE.Address_01_Street2_Continuum_050, @City = SCRIBE.ER_STD_GREENVILLE.Address_01_City_Continuum422_052, @State = SCRIBE.ER_STD_GREENVILLE.Address_01_StateProvince_Con_053, 
                      @Zip = SCRIBE.ER_STD_GREENVILLE.Address_01_PostalCode_Contin_054, @IDName = SCRIBE.ER_STD_GREENVILLE.ID_01_Type_Continuum422_Pers_068, 
                      @IDValue = SCRIBE.ER_STD_GREENVILLE.ID_01_Value_Continuum422_Per_069, @OppType = SCRIBE.ER_STD_GREENVILLE.OpportunityType_Continuum422_078, 
                      @Term = SCRIBE.ER_STD_GREENVILLE.EntryTerm_Continuum422_Perso_079, @OppStatus = SCRIBE.ER_STD_GREENVILLE.OpportunityStatus_Continuum4_080, 
                      @Act1Value = SCRIBE.ER_STD_GREENVILLE.Activity_01_Value_Continuum4_134, @Act1Date = SCRIBE.ER_STD_GREENVILLE.Activity_01_Date_Continuum42_135, 
                      @Act1Desc = SCRIBE.ER_STD_GREENVILLE.Activity_01_Description_Cont_136, @Act2Value = SCRIBE.ER_STD_GREENVILLE.Activity_02_Value_Continuum4_137, 
                      @Act2Date = SCRIBE.ER_STD_GREENVILLE.Activity_02_Date_Continuum42_138, @Act2Desc = SCRIBE.ER_STD_GREENVILLE.Activity_02_Description_Cont_139, @XML = SCRIBE.USERVIEW_EXECUTIONLOG.MESSAGE
FROM         SCRIBE.ER_STD_GREENVILLE INNER JOIN
                      SCRIBE.USERVIEW_EXECUTIONLOG ON SCRIBE.ER_STD_GREENVILLE.KSSTARTTIME = SCRIBE.USERVIEW_EXECUTIONLOG.STARTTIME INNER JOIN
                      SCRIBE.USERVIEW_TRANSACTIONERRORS ON 
                      SCRIBE.USERVIEW_EXECUTIONLOG.EXECID = SCRIBE.USERVIEW_TRANSACTIONERRORS.EXECID
 
  BEGIN
    DECLARE @msg nvarchar(MAX)
    SET @msg = 'The folowing record had errors during your import into CRM:' + char(13) + char(13) +
'Start Time: ' + CAST(@StartTime as varchar(50)) + char(13)+
'End Time: ' + CAST(@EndTime as varchar(50)) + char(13)+
'Source Rows: ' + CAST(@SourceRows as int) + char(13)+
'Step Number: ' + CAST(@StepNumber as smallint) + char(13)+
'Target Table: ' + @Table + char(13)+
'Operation: ' + @Operation + char(13)+
'Row Number: ' + CAST(@RowNumber as int) + char(13)+
'Error: ' + @Error + char(13)+
'First Name: ' + @Fname + char(13)+
'Last Name: ' + @Lname + char(13)+
'Street1: ' + @Street1 + char(13)+
'Street2: ' + @Street2 + char(13)+
'City: ' + @City + char(13)+
'State: ' + @State + char(13)+
'Zip: ' + @Zip + char(13)+
'ID Name: ' + @IDName + char(13)+
'ID Value: ' + @IDValue + char(13)+
'Oppotunity Type: ' + @OppType + char(13)+
'Entry Term: ' + @Term + char(13)+
'Opportunity Status: ' + @OppStatus + char(13)+
'Activity 1 Value: ' + @Act1Value + char(13)+
'Activity 1 Date: ' + CAST(@Act1Date as varchar(50)) + char(13)+
'Activity 1 Description: ' + @Act1Desc + char(13)+
'Activity 2 Value: ' + @Act2Value + char(13)+
'Activity 2 Date: ' + CAST(@Act2Date as varchar(50)) + char(13)+
'Activity 2 Description: ' + @Act2Desc + char(13)
'Full XML Message: ' + @XML + char(13)
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail
		@recipients=N'bprocida@422group.com',
		@body= @msg, 
		@subject = 'SQL Server Trigger Mail',
		@profile_name = 'AppNotify'
  END

Open in new window

blob150Asked:
Who is Participating?
 
blob150Author Commented:
I resolved the problem.  Since I was concatenating values in the message body when a parameter came through as null the whole message was null (in SQL null + something = null).  Once I applied - IsNull(<parameter>, ) to all parameters in the messages body the emails came through correctly.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Odds are that there is a character in the message body that is causing the sp_send_dbmail problems. Ordinarily the procedure just send a blank message rather than throwing any errors.

I would get your trigger to write the message to a dummy table so that you can see exactly what it is trying to send.
0
 
blob150Author Commented:
OK.  After some trial and error I discovered that if any of the fields in the query were NULL, it would cause the email body to be blank.  How can I address the issue of NULLs being passed to my parameters?
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.

All Courses

From novice to tech pro — start learning today.