Making part of a convert BOLD 1

[Cpreston]

Hi

We have a stored procedure that send text via email.

What we would like to do (if possible) is make part of the text stand out somehow perhaps in BOLD. I have out some spaces in between the fields so it is easier to read, but would like the @Q to stand out more.

SET @Message = @Message + CONVERT(varchar(12),@Inv) + ' ' + CONVERT(varchar(20),@InvDate) + ' ' + CONVERT(varchar(20),@Q) + ' ' + CONVERT(varchar(20),@A) + ' ' + @Desc + CHAR(13)


Any ideas please

Thanks

 

[SgtJarrow]

Not really a SQL function. Best handled by your front end presentation layer.

BUT...since you saying you are building the text of an email, you [probably] can wrap the portion of the text you want with proper HTML tags and your email client should display it. That also depends on certain setting and the like on the receiver's email client. For example, I can FORCE my email client to ignore/disable all embedded HTML tags by setting all my email to be VIEWED as PLAIN TEXT. The embedded HTML code just shows as regular text.

Something like the below MIGHT work for you to just bold the value returned by @Q:

SET @Message = @Message + CONVERT(varchar(12),@Inv) + ' ' + CONVERT(varchar(20),@InvDate) + ' <b>' + CONVERT(varchar(20),@Q) + '</b> ' + CONVERT(varchar(20),@A) + ' ' + @Desc + CHAR(13)

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

SQL Server is about data, not how you present it.

However, if you are getting the contents of an email, and you are sending the email in HTML format, you could try something like this:

SET @Message = @Message 
               + CONVERT(varchar(12),@Inv) 
               + ' ' 
               + CONVERT(varchar(20),@InvDate) 
               + ' ' 
               + ' <span style="font-weight:bold; color:red;">'
               + CONVERT(varchar(20),@Q) 
               + '</span>'
               + ' ' 
               + CONVERT(varchar(20),@A) 
               + ' ' 
               + @Desc + CHAR(13)

If you are sending this message as text, the above method will not work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks for the replys, I will try them out and get back to you, many thanks

 

[ElEye]

Also, be sure you're using the HTML option of DBMail. Unfortunately DBMail does not allow for simultaneous text and HTML email, even though email can have both embedded. So, it's either HTML or TEXT. Use HTML in this case, obviously.

BTW, if you are using SS2012 or newer I advise changing your string of pluses to a CONCAT string. Some strings may not run as you expect in a SS version newer than what you're using. And CONCAT handles NULLs in the string better.

Dave
[

http://flashspot.com

]

 

[Cpreston]

Hi

gmmastros I tried your code but the result came in the email like below

718274 Jun 17 2015 3:13PM <span style="font-weight:bold; color:red;">1.000</span> 0.000 Full load 718273 Jun 17 2015 3:13PM <span style="font-weight:bold; color:red;">1.000</span> 0.000 Please deliver to: Amanda Harris.
Nateby Crossing Lane, Preston,
Lancashire.PR3 0JJ. 718273 Jun 17 2015 3:13PM <span style="font-weight:bold; color:red;">1.000</span> 0.000 Please call Amanda on 07735098039 to provide notice.

I added in the code you gave as below, any ideas what went wrong and how to correct please

SET @Message = @Message
+ CONVERT(varchar(12),@Inv)
+ ' '
+ CONVERT(varchar(20),@InvDate)
+ ' '
+ ' <span style="font-weight:bold; color:red;">'
+ CONVERT(varchar(20),@Q)
+ '</span>'
+ ' '
+ CONVERT(varchar(20),@A)
+ ' '
+ @Desc + CHAR(13)

 

[jbenson001]

The problem here is not George's code, it's the fact that the email is being sent as TEXT instead of HTML. Are you sending the email through SQL or a front end app. I suggest you use a front end app for this, as well as doing any formatting through it instead of SQL

 

[Cpreston]

Hi

Unfortunately the text is coming straight from a stored procedure result so I cannot do anything with a front end app.

Thanks

 

[jbenson001]

Of course you can. Call the SP from your app and take the result, format it and send it.
if you are using Visual Studio, the .NET framework makes is very easy.

 

[ElEye]

In the sp_send_dbmail portion of your SP look for @body_format and set it HTML like this: @body_format = 'HTML'. It's probably like this right now: @body_format = 'TEXT'.

Dave
[

http://flashspot.com

]

 

[Cpreston]

Hi

Thanks for the replys

jbenson001 - I do have Visual Studio, the .NET framework makes is very easy but I am not familiar with it that well so would be lost trying to do this.

ElEye - In the sp_send_dbmail portion I have this ( I have put **** in our profile name and recipient name)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '*****',
@recipients = @Recipient1,
@blind_copy_recipients = '****;',
@subject = @Subject1,
@body = @Message

The @body = @Message is not @body_format = 'TEXT'. so is message the same please?

Thanks

 

[ElEye]

@body_format determines HTML or text. @body is the content. To force HTML add @body_format = 'HTML' to your EXEC:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = '*****',
@recipients = @Recipient1,
@blind_copy_recipients = '****;',
@subject = @Subject1,
@body = @Message,
@body_format = 'HTML'

Dave
[

http://flashspot.com

]

 

[Cpreston]

Hi

I changed it to @body_format = 'HTML' and now it displays the qty in red as expected but somehow now the lines are all mixed in together, it does not carriage return on change of order number which it used to do before the changes. see example below of first few lines of email (first one is 718632 and then 718624 etc....)

718632 Jun 19 2015 4:59PM 1.000 0.000 Trex Display 718624 Jun 19 2015 4:59PM 1.000 0.000 PArkers please deliver ASAP to Brooklands Landscapes - James Barlow on 07912 883522 - PLEASE CALL IN ADVANCE 718624 Jun 19 2015 4:59PM 1.000 0.000 Deliver to 20 Sunny Close, Goring on Sea, West Sussex. BN12 4BD 718623 Jun 19 2015 4:59PM 1.000 0.000 Howarth's please deliver TUESDAY to: City carpet care. 24


The code I set like this

SET @Message = @Message
+ CONVERT(varchar(12),@Inv)
+ ' '
+ CONVERT(varchar(20),@InvDate)
+ ' '
+ ' <span style="font-weight:bold; color:red;">'
+ CONVERT(varchar(20),@Q)
+ '</span>'
+ ' '
+ CONVERT(varchar(20),@A)
+ ' '
+ @Desc + CHAR(13)

Any ideas please.

Many thanks

 

[ElEye]

A carriage return (aka line break) in HTML is '<br>' (or '<br />') so place either of those in your code for a line break. A CHAR(13), and/or a CHAR(10), in your HTML will only render in your code without creating a line break in your displayed text.

Dave
[

http://flashspot.com

]

 

[Cpreston]

Hi

This is my code now and it as worked, thanks for the help
SET @Message = @Message
+ CONVERT(varchar(12),@Inv)
+ ' '
+ CONVERT(varchar(20),@InvDate)
+ ' '
+ ' <span style="font-weight:bold; color:red;">'
+ CONVERT(varchar(20),@Q)
+ '</span>'
+ ' '
+ CONVERT(varchar(20),@A)
+ ' '
+ @Desc + CHAR(13)
'<br />'