db_send_dbmail

I have a series of SP's that send email reminders to seminar attendees before their seminar and a separate one for after the seminar,

Using one of those SP's I have created another SP, attached, and all I get is 3, correct number of emails, al lNULL content.  The only difference I can see is the new base html document has tables.  Other than that, the structure of the html file is identical to the working copies.  I you do need them I can get copies of the HTML and SP's that work.
Jeff_KingstonAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You don't have to change the method of concat'ing the template input file, just don't do it multiple times:


declare @firmsize int
declare @basesub decimal(15,2)
declare @reducedol decimal(15,2)
declare @netcost2 decimal(15,2)
declare @webqty integer
declare @webdol decimal(15,2)
declare @ipqty decimal(15,2)
declare @ipdol decimal(15,2)
declare @bkqty decimal(15,2)
declare @bkdol decimal(15,2)
declare @totdol decimal(15,2)
declare @netcost decimal(15,2)
declare @addpct varchar(250)
declare @email varchar(100)
declare @embedded varchar(800)
declare @FileContents_Template varchar(8000)
declare @FileContents varchar(8000)
declare @promocode varchar(250)

DECLARE @FileName varchar(255)
DECLARE @ExecCmd varchar(255)
DECLARE @template_row_count bigint
DECLARE @template_current_row bigint

DECLARE @id varchar(10)
declare @body varchar(5000)

--
-- Get template of email to be sent
--
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ThisLine VARCHAR(255))
SET @FileName = 'c:\EmailHTML\individual_paid.html'
SET @ExecCmd = 'type ' + @FileName

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @template_row_count = count(*) from #tempXML

SET @template_current_row = 0
SET @Filecontents_Template = ''
WHILE @template_current_row <> @template_row_count
      BEGIN
          SET @template_current_row = @template_current_row + 1
          SELECT @FileContents_Template = @FileContents_Template + ThisLine
          from #tempXML
          WHERE PK = @template_current_row and thisline is not null
      END


--
-- Use (Gasp!!) cursor to process records from dataset
--

DECLARE db_cursor CURSOR FOR  
SELECT
      n_email,
      ump_size_of_firm,
      o_total_charges,
      calc_basesub,
      web_qty,
      web_dol,
      ip_qty,
      ip_dol,
      bk_qty,
      bk_dol,
      promo_code
from ninetydayworkfile
where ump_size_of_firm is null

OPEN db_cursor  
FETCH NEXT
      FROM db_cursor
      INTO
            @email,
            @firmsize,
            @reducedol,
            @basesub,
            @webqty,
            @webdol,
            @ipqty,
            @ipdol,
            @bkqty,
            @bkdol,
            @promocode

WHILE @@FETCH_STATUS = 0  
BEGIN
--
-- Open template file
--

--
-- Perform substitutions
--
      --select @fulldate = DATEname(MM,@begindate) + ' ' + DATEname(DD,@begindate) + ', ' + DATEname(YYYY,@begindate)

 
      select @filecontents = replace(replace(replace(replace(@filecontents_template,
          'Basesub',ISNULL(@basesub, 'N/A')),
               --'coup_code',ISNULL(@constreplace, '')),
               --'exp_date',ISNULL(@expiration_date, '')),
          'NetCost2',ISNULL(@netcost, 'N/A')),
          'reducedol',ISNULL(@reducedol, 'N/A')),
          'WebQty',ISNULL(@webqty, 'N/A'))

      SELECT @FileContents

      EXEC msdb.dbo.sp_send_dbmail
      'CustomerService2',
      @recipients='jeff.kingston@gmail.com',
      --@blind_copy_recipients ='jeff.kingston@gmail.com',
          @subject = 'testing ninetyday',
          @body = @FileContents,
          @body_format = 'HTML' ;

      drop table #tempXML

FETCH NEXT
      FROM db_cursor
      INTO
            @email,
            @firmsize,
            @reducedol,
            @basesub,
            @webqty,
            @webdol,
            @ipqty,
            @ipdol,
            @bkqty,
            @bkdol,
            @promocode
END  

CLOSE db_cursor  
DEALLOCATE db_cursor
0
 
ste5anSenior DeveloperCommented:
hmm, what is your exact problem here?

Do you assign the correct body parameter? Do you use string concatenation for HTML generation and haven't initalized your variable?

Please post a concise and complete example.
0
 
Jeff_KingstonAuthor Commented:
I attached a copy of the sp as a text file... but I will attach again

body_type = HTML
template file loaded to a string variable that has been initialized
then do some simple replacements
toexpertsexchange.txt
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
Check whether any of:
@basesub
@reducedol
@netcost
@webqty
are NULL.

Btw, you shouldn't drop and recreate the temp table and load the email shell multiple times.  Just load it once, save it as a template, then do separate replaces for each detail row.
0
 
ste5anSenior DeveloperCommented:
E.g.

-- Markup.
DECLARE @REDUCEDOL_TAG NVARCHAR(MAX);
DECLARE @BASESUB_TAG NVARCHAR(MAX);
DECLARE @WEBQTY_TAG NVARCHAR(MAX);

SET @REDUCEDOL_TAG = N'<!-- REDUCEDOL -->';
SET @BASESUB_TAG = N'<!-- BASESUB -->';
SET @WEBQTY_TAG = N'<!-- WEBQTY -->';

-- CAVEAT: file must be UCS-2/UTF-16 encoded.
DECLARE @MailTemplateFile NVARCHAR(MAX); 
DECLARE @MailTemplate NVARCHAR(MAX);
DECLARE @Mail NVARCHAR(MAX);

SET @MailTemplateFile = N'C:\Temp\test.html';
SET @MailTemplateFile = N'SELECT @MailTemplate = BulkColumn FROM OPENROWSET(BULK ''' + @MailTemplateFile + ''', SINGLE_BLOB) O;';

EXECUTE dbo.sp_executesql @MailTemplateFile, N'@MailTemplate NVARCHAR(MAX) OUTPUT', @MailTemplate = @MailTemplate OUTPUT;

DECLARE db_cursor CURSOR
FOR
    SELECT  n_email ,
            REPLACE(REPLACE(REPLACE(@MailTemplate, 
				@REDUCEDOL_TAG, CAST(o_total_charges AS NVARCHAR(255))), 
				@BASESUB_TAG, CAST(calc_basesub AS NVARCHAR(255))),
				@WEBQTY_TAG, CAST(web_qty AS NVARCHAR(255)))
    FROM    ninetydayworkfile
    WHERE   ump_size_of_firm IS NULL
            AND o_total_charges IS NOT NULL
            AND calc_basesub IS NOT NULL
            AND web_qty IS NOT NULL;

OPEN db_cursor  ; 
FETCH NEXT FROM db_cursor INTO @email, @Mail;

WHILE @@FETCH_STATUS = 0
    BEGIN 
        EXECUTE msdb.dbo.sp_send_dbmail 'CustomerService2', @recipients = 'jeff.kingston@gmail.com', @subject = 'testing ninetyday', @body = @Mail,
            @body_format = 'HTML';
        FETCH NEXT FROM db_cursor INTO @email, @Mail;
    END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

Open in new window


Use regulare comments in your template file. This allows you to preview the template in the browser.

<html>
	<body>
		<h1><!-- TITLE --><h1>
		<p><!-- CONTENT --><p>
	</body>
</html>

Open in new window

0
 
Jeff_KingstonAuthor Commented:
The only issue I see is all my email is now in Chinese????????????????????????????
0
 
Jeff_KingstonAuthor Commented:
Here's the template
htmlforee.txt
0
 
ste5anSenior DeveloperCommented:
That is the caveat: You need an UCS-2 or UTF-16 encoded file. UTF-8 doesn't work, cause it's not supported by SQL Server. Use e.g. Notepad++ to check/change the file encoding.
0
 
Jeff_KingstonAuthor Commented:
Sorry I didn't close this, got the fix and all is well with the emailing process, now trying to add several more fields to the email but that shouldn't be a problem



Kudos for noting I was opening the template file for each pass.  speed things up when you are processing a large number of emails.
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.