June 1, 2010 at 3:24 pm
Hi all, new to the db scene and first time poster here, so be gentle 🙂 I recently inherited ownership of a daily job that sends an email alert populated with the results sets of three queries (packaged as variables) and is distributed in HTML format. Everything had been working great until one of the queries produced a blank result set (no returns). This is fine, as the alert is designed to give sight to issues - no issues, no problem - and after verifying that the blank result set was accurate, I'm happy that it is running as expected.
The problem is that this causes the entire email to be mailed blank - no text, no attachments, nothing. There are three sections of the email, each populated with their own results sets, so at least two of the three should be coming through. In testing, I've found that if I use a dummy set of data that WILL produce an alert for the third query, the email sends perfectly, so the issue seems to be with the blank results set.
Has anyone encountered this before, and if so, how can I get the email to send regardless of the blank result set? I've not found much regarding this on the net, so any insight will be helpful and thanks in advance for your time!
Brian
June 1, 2010 at 4:58 pm
Hello and welcome,
Some code would help here but I'll take a wild guess.
Are you concatenating variables? A NULL value combined with another value will often produce NULL depending on your sever settings.
DECLARE @var1 char(1)
DECLARE @var2 char(2)
SET @var1 = '1'
SELECT @var1 + @var2 (this will produce a NULL since @var2 is NULL )
try using COALESCE
SELECT COALESCE(@var1,'') + COALESCE(@var2,'')
COALESCE will get the first Non-Null value so in the example you would replace the NULL value of @var2 with the empty string.
June 2, 2010 at 8:39 am
Here are the variables and how the email is set to deploy:
DECLARE @subjectHTML NVARCHAR(150);
DECLARE @MissingLatestTableHTML NVARCHAR(MAX) ;
DECLARE @MissingSeriesTableHTML NVARCHAR(MAX);
DECLARE @MissingFinInfo NVARCHAR(MAX);
DECLARE @Notes NVARCHAR(MAX);
DECLARE @infoHTML NVARCHAR(MAX) ;
DECLARE @infoSection1 NVARCHAR(MAX) ;
DECLARE @infoSection2 NVARCHAR(MAX) ;
DECLARE @infoSection3 NVARCHAR(MAX) ;
DECLARE @assumptionsHTML NVARCHAR(MAX);
DECLARE @allHTML NVARCHAR(MAX);
The third section of code that is returning blank is @MissingFinInfo and is (only posting this section for brevity's sake, but other variables are similar in design):
SET@MissingFinInfo =
N'<table border="1">' +
N'<font size=1><tr><font face="sans-serif"><th>BankID</th><th>Gen2 ID</th>' +
N'<th>Name</th><th>Assets</th><th>Deposits</th>' +
N'<th>Loan Portfolio</th>' +
N'<th>Loan Portfolio Yield</th>' +
N'<th>Investment Portfolio</th>' +
N'<th>Investment Portfolio Yield</th>' +
N'<th>Cost of Funds</th>' +
N'<th>Reinvestment Rate</th>' +
N'<th>AC</th>' +
N'<th>TE</th></tr><font face="sans-serif">' +
CAST((SELECTtd = CI.BillingID, '',
td = FIN.InstitutionID, '',
td = CI.InstitutionName, '',
td = LEFT(Assets, CHARINDEX('.', Assets)+1), '',
td = LEFT(Deposits, CHARINDEX('.', Deposits)+1), '',
td = LEFT(LoanPortfolio, CHARINDEX('.', LoanPortfolio)+1), '',
td = LoanPortfolioYield, '',
td = LEFT(InvestmentPortfolio, CHARINDEX('.', InvestmentPortfolio)+1), '',
td = InvestmentPortfolioYield, '',
td = CostofFunds, '',
td = ReinvestmentRate, '',
td = CASE
WHEN LEN(accounts_cstm_contact_ac_ema)=0 THEN ''
ELSE LEFT(accounts_cstm_contact_ac_ema, CHARINDEX('.', accounts_cstm_contact_ac_ema)-1)+' '+ SUBSTRING(accounts_cstm_contact_ac_ema, CHARINDEX('.', accounts_cstm_contact_ac_ema)+1, CHARINDEX('@',accounts_cstm_contact_ac_ema)-CHARINDEX('.', accounts_cstm_contact_ac_ema)-1)
END, '',
td = CASE
WHEN LEN(accounts_cstm_contact_te_ema)=0 THEN ''
ELSE LEFT(accounts_cstm_contact_te_ema, CHARINDEX('.', accounts_cstm_contact_te_ema)-1)+' '+ SUBSTRING(accounts_cstm_contact_te_ema, CHARINDEX('.', accounts_cstm_contact_te_ema)+1, CHARINDEX('@',accounts_cstm_contact_te_ema)-CHARINDEX('.', accounts_cstm_contact_te_ema)-1)
END, ''
FROMStaging.FinancialInfo FIN
INNER JOIN Staging.CoreInstitution CI ON FIN.InstitutionID = CI.InstitutionID
INNER JOIN Staging.CRM_FIData CRM ON CI.BillingID = CRM.accounts_cstm_fid_num_c
WHERE(Assets IN (0, NULL) OR
Deposits IN (0, NULL) OR
LoanPortfolio IN (0, NULL) OR
LoanPortfolioYield IN (0, NULL) OR
InvestmentPortfolio IN (0, NULL) OR
InvestmentPortfolioYield IN (0, NULL) OR
CostofFunds IN (0, NULL) OR
ReinvestmentRate IN (0, NULL))
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX)) +
N'</table></font size=1>'
Then the variable @allHTML is put together as:
SET @allHTML = @infoHTML + @infoSection1 + @MissingLatestTableHTML + @infoSection2 + @MissingSeriesTableHTML + @assumptionsHTML + @Notes + @MissingFinInfo
And finally, the email is sent using:
EXEC msdb.dbo.sp_send_dbmail
@recipients='User@domain',
@subject = @subjectHTML,
@body = @allHTML,
@body_format = 'HTML',
@file_attachments = 'C:\ReportImages\RedAlert.png' ;
So the @allHTML variable is what is tying the strings together, not a SELECT.
June 2, 2010 at 9:23 am
Okay, I'm an idiot...
Was trying to get the COALESCE suggestion to work and just when I was about to pull out my hair, I realized that I had used a double-quote (") instead of single-quotes (' ') for the second part of the function. I'm green at this stuff, but not that green!
Thanks emily for your suggestion...it is now running perfectly!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply