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.
Larry RungrenDirector of TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Larry RungrenDirector of TechnologyAuthor 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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
Larry RungrenDirector of TechnologyAuthor Commented:
The only issue I see is all my email is now in Chinese????????????????????????????
0
Larry RungrenDirector of TechnologyAuthor 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry RungrenDirector of TechnologyAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.