November 8, 2011 at 11:51 am
SELECT id, CASE WHEN Subject IS NULL THEN 'Change to HR System' ELSE '' END AS SubjectDesc, Email, FirstName, LastName, ChangeDate, OldStatus, NewStatus,
OldTitle, OldDepartment, OldSupervisor, CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,
FROM dbo.HRChangeLog
How do I add a carriage return in this line after the name?
CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,
Any help would be greatly appreciated. Thank you.
November 8, 2011 at 12:00 pm
Add CHAR(13)
Ex:
CASE WHEN NewTitle IS NOT NULL THEN 'HR Change for: '+ CAST(FirstName + ' ' + LastName AS varchar(100))+ CHAR(13) + ', Previous Title: '+ CAST(OldTitle AS varchar(800))+', New Title: '+ CAST(NewTitle AS varchar(800)) ELSE '' END AS UpdateTitleDesc,
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 8, 2011 at 12:45 pm
I still can't get it to work. I copied and pasted the code and it still came out in one line?? :unsure:
[font="Arial Black"]HR Change for: John Doe, Previous Title: Admin, New Title: Developer[/font]
November 8, 2011 at 12:58 pm
Are you outputting te results to grid or text?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 8, 2011 at 1:02 pm
The results are in a grid. I'm using SQL Server Management Studio if that makes a difference. My goal is once I get this to work to create a job to insert the output into the description of another table which will then create a task to send to the appropriate office.
November 8, 2011 at 1:05 pm
November 8, 2011 at 1:40 pm
littlelisa1111 (11/8/2011)
The results are in a grid. I'm using SQL Server Management Studio if that makes a difference. My goal is once I get this to work to create a job to insert the output into the description of another table which will then create a task to send to the appropriate office.
grid mode NEVER shows CrLf, it converts them to spaces for display purposes only..
the CrLf may exist in the data, but you have to switch to TextMode to see it.
Lowell
November 8, 2011 at 1:49 pm
You CANNOT view this in grid mode. Each line in the grid output is a new "ROW".
See for your self, Try this:
-- Run this in "Results to Text"
SELECT 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'
It appears to return all one line of text correct?
Now, change to "Results to Text" and try this:
-- Run this in "Results to Text"
SELECT 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'
-- Execute this as-is
DECLARE @BODY varchar(150), @Subject varchar(50)
SET @Subject = 'This!'
SET @BODY = 'This!' + CHAR(13) + 'is ' + CHAR(13) + 'test'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mydoggiejessie@abc.com',
@subject = @Subject,
@body = @Body
Not only is your first SELECT statement wrapped on different lines, the email you'll receive text body is also wrapped...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 8, 2011 at 1:51 pm
Thanks Lowell! I was now able to see the carriage returns.
November 8, 2011 at 1:52 pm
@mydoggiejessie Thank you! the Char(13) worked, I just couldn't see it
March 13, 2018 at 8:23 am
Change Options (Query Results, SQL Server, Results to Grid, "Retain CR/LF on copy or save")
March 13, 2018 at 7:42 pm
daroberts2 - Tuesday, March 13, 2018 8:23 AMChange Options (Query Results, SQL Server, Results to Grid, "Retain CR/LF on copy or save")
Now, store that in a table without human intervention. 😉
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply