Search code examples
sqlsql-serversp-send-dbmaildbmail

SQL Split text from column in several lines in select statement

I'm creating a job in SQL Server 2014 and the last step is to send the output of the first step to my email. I'm already sending the email with the result of the next query:

select TOP 1 
    msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', 
    message 
from 
    sysjobhistory 
where 
    job_id = 'SOMETHING' and step_id = 1
order by 
    run_date DESC, run_time DESC

The message column has a big text and it outputs all in one line as you can see in this image:

Example of the email received

I want to know if it's possible to split the text into several lines. I can do it based in a delimiter. I saw some functions but all they do is to separate the text in columns and rows and I don't want that.

Thank you :)

Solution

  • It looks like "Executed:" turns up a bunch in the string, so I might start with something like:

    SELECT TOP 1
    msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
    REPLACE(message, 'Executed:', CHAR(13) + 'Executed:') AS message
FROM
    sysjobhistory
WHERE
    job_id = 'SOMETHING' and step_id = 1
ORDER BY
    run_date DESC,
    run_time DESC