2

UPDATE

I dont know why but instead of using the concatenation with the plus sign SET @bodyHTMLBuild = '<html><and more..' + @company + '..more></html>' I have changed to CONCAT set @bodyHTMLBuild =CONCAT('<html><and more..', @company ,'...more hmtl></hmtl>')

And now all the Hmtl is successfully sent by Exec sp_send_dbmail @body = @bodyHTMLBuild, @body_format = 'HMTL' :)

the >8000 still is fixed by using the set @moreThan8000 = CONCAT(CAST(' 7999 chars...') as VARCHAR(max) , CAST('More chars (e.g 5000)') as VARCHAR(max))

select LEN(@moreThan8000) will show : 12999

ORIGINAL POST

I am trying to send an HTML body Email using [dbo].[sp_send_dbmail] when a specific table triggers and insert .

the problem is that is truncating the Mail up to 8000 chars I have read some forums about Casting the string as Varchar(max) but I dont see is working.

Here the code: Please any help is greatly appreciated

`DECLARE @bodyHTMLBuild NVARCHAR(max)

DECLARE @ProdId VARCHAR(30)
DECLARE @Company VARCHAR(30)
DECLARE @CreatedBy VARCHAR(30)
DECLARE @itemId VARCHAR(30)

set @ProdId = 'prod'
set @Company = 'Comp'
set @CreatedBy = 'CreatedBy'
set @itemId = 'ItemId'




SET @bodyHTMLBuild = CAST('<!doctype html>
        <html>
          <head>
            <meta name="viewport" content="width=device-width">
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
            <title></title>
            <style>
            /* -------------------------------------
                
            ------------------------------------- */
            /* -------------------------------------
                RESPONSIVE AND MOBILE FRIENDLY STYLES
            ------------------------------------- */
            @media only screen and (max-width: 620px) {
              table[class=body] h1 {
                font-size: 28px !important;
                margin-bottom: 10px !important;
              }
              table[class=body] p,
                    table[class=body] ul,
                    table[class=body] ol,
                    table[class=body] td,
                    table[class=body] span,
                    table[class=body] a {
                font-size: 16px !important;
              }
              table[class=body] .wrapper,
                    table[class=body] .article {
                padding: 10px !important;
              }
              table[class=body] .content {
                padding: 0 !important;
              }
              table[class=body] .container {
                padding: 0 !important;
                width: 100% !important;
              }
              table[class=body] .main {
                border-left-width: 0 !important;
                border-radius: 0 !important;
                border-right-width: 0 !important;
              }
              table[class=body] .btn table {
                width: 100% !important;
              }
              table[class=body] .btn a {
                width: 100% !important;
              }
              table[class=body] .img-responsive {
                height: auto !important;
                max-width: 100% !important;
                width: auto !important;
              }
            }

            /* -------------------------------------
                
            ------------------------------------- */
            @media all {
              .ExternalClass {
                width: 100%;
              }
              .ExternalClass,
                    .ExternalClass p,
                    .ExternalClass span,
                    .ExternalClass font,
                    .ExternalClass td,
                    .ExternalClass div {
                line-height: 100%;
              }

              }
            }
            </style>
          </head>
          <body class="" style="background-color: #40668d; font-family: sans-serif; -webkit-font-smoothing: antialiased; font-size: 14px; line-height: 1.4; margin: 0; padding: 0; -ms-text-size-adjust: 100%; -webkit-text-size-adjust: 100%;">
            <table border="0" cellpadding="0" cellspacing="0" class="body" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; background-color: #243241;">
              <tr>
                <td style="font-family: sans-serif; font-size: 14px; vertical-align: top;">&nbsp;</td>
                <td class="container" style="font-family: sans-serif; font-size: 14px; vertical-align: top; display: block; Margin: 0 auto; max-width: 580px; padding: 10px; width: 580px;">
                  <div class="content" style="box-sizing: border-box; display: block; Margin: 0 auto; max-width: 580px; padding: 10px;">

                    <!-- START CENTERED WHITE CONTAINER -->
                    <span class="preheader" style="color: transparent; display: none; height: 0; max-height: 0; max-width: 0; opacity: 0; overflow: hidden; mso-hide: all; visibility: hidden; width: 0;">Alarm: Detection of BOM Circularity at Production:'+@ProdId+'</span>
                    <table class="main" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; background: #ffffff; border-radius: 3px;">

                      <!-- START MAIN CONTENT AREA -->
                      <tr>
                        <td class="wrapper" style="font-family: sans-serif; font-size: 14px; vertical-align: top; box-sizing: border-box; padding: 20px;">
                          <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%;">
                            <tr>
                              <td style="font-family: sans-serif; font-size: 14px; vertical-align: top;">
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;"><strong style="color:#D40A2B">BOM Circularity Detected</strong> in Production Nr: <strong style="color:#074DBF">'+@ProdId+'</strong>:</p>
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;">Axapta has detected a BOM Circularity in  <strong style="color:#074DBF">' AS VARCHAR(MAX) )
                                
                                + CAST(@Company AS VARCHAR(MAX)) +
                                
                             CAST('</strong> </p>
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;">for the related Item Number: <strong style="color:#074DBF">' AS VARCHAR(MAX))
                                
                                +CAST(@ItemId AS VARCHAR(MAX))+
                                
                                CAST('</strong>.</p>
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;">Production resposable: <strong style="color:#074DBF">' AS varchar(MAX))
                                
                                +CAST(@CreatedBy AS VARCHAR(MAX))+
                                
                                CAST('</strong>.</p>
                                <table border="0" cellpadding="0" cellspacing="0" class="btn btn-primary" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%; box-sizing: border-box;">
                                  <tbody>
                                    <tr>
                                      <td align="left" style="font-family: sans-serif; font-size: 14px; vertical-align: top; padding-bottom: 15px;">
                                        <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: auto;">
                                          <tbody>

                                          </tbody>
                                        </table>
                                      </td>
                                    </tr>
                                  </tbody>
                                </table>                                
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;">fix as soon as possible!</p>
                                <p style="font-family: sans-serif; font-size: 14px; font-weight: normal; margin: 0; Margin-bottom: 15px;">Please do not reply to this mail.</p>
                              </td>
                            </tr>
                          </table>
                        </td>
                      </tr>

                    <!-- END MAIN CONTENT AREA -->
                    </table>

                    <!-- START FOOTER -->
                    <div class="footer" style="clear: both; Margin-top: 10px; text-align: center; width: 100%;">
                      <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: separate; mso-table-lspace: 0pt; mso-table-rspace: 0pt; width: 100%;">
                        <tr>
                          <td class="content-block" style="font-family: sans-serif; vertical-align: top; padding-bottom: 10px; padding-top: 10px; font-size: 12px; color: #999999; text-align: center;">
                            <span class="apple-link" style="color: #999999; font-size: 12px; text-align: center;">' AS VARCHAR(MAX))
                            
                            +CAST(@Company AS VARCHAR(MAX))+
                            
                            CAST(' Sp. z.o.o</span>
                            <br> Anything wrong? please forward to <a style="text-decoration: underline; color: #999999; font-size: 12px; text-align: center;">[email protected]</a>.
                          </td>
                        </tr>
                        <tr>

                    <!-- END FOOTER -->

                  <!-- END CENTERED WHITE CONTAINER -->
                  </div>
                </td>
                <td style="font-family: sans-serif; font-size: 14px; vertical-align: top;">&nbsp;</td>
              </tr>
            </table>
          </body>
        </html>
            ' AS VARCHAR(MAX))

        print   @bodyHTMLBuild

`

1
  • you are alsways stuck on vaerchar(max) try to use css in your tavbke and get rid of most of the style as it is bad style anyway
    – nbk
    Apr 21, 2021 at 17:01

1 Answer 1

2

Firigid Shortcomings

Print can only print so much, but there are a couple things you can do to work around that.

Option One: How Long Is A String?

Figure out how long your string is, and print substrings of it.

print   LEN(@bodyHTMLBuild);
print   SUBSTRING(@bodyHTMLBuild, 1, 8000);
print   SUBSTRING(@bodyHTMLBuild, 8000, 16000);

Option Two: XML All The Things

If you want something that you can click on, use this:

SELECT @bodyHTMLBuild AS [processing-instruction(_)] FOR XML PATH('');

Option Three: Yildirim Kocdag

You can use Helper_LongPrint to print longer strings.

2
  • Hi Erik, thanks for replying, But I want to send this by Email. not to print (the print was only you to test as an example. I want to store that HTML into a Varchar(max ) and call the EXEC msdb.dbo.sp_send_dbmail profile_name = 'SQL Pol-AX Admin', recipients = '[email protected]', body = @bodyHTMLbuild, subject = 'buject', body_format = 'HTML';
    – Nestor C.
    Apr 22, 2021 at 5:06
  • @NestorC.I get that, but your concern was that your string is being truncated, and I'm giving you ways to show that it's not. Something else is going on with your code, and you can use my answer to troubleshoot that. Apr 22, 2021 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.