Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sp_send_dbmail Arrives Blank

Posted on 2009-02-09
3
Medium Priority
?
744 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:blob150
  • 2
3 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 200 total points
ID: 23592243
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
 

Author Comment

by:blob150
ID: 23593476
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
 

Accepted Solution

by:
blob150 earned 0 total points
ID: 23593809
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

564 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