SQL Agent Job output in text file formatting

Question

I am trying to write an output of SQL Agent Job to the text or excel or CSV file.

But it's writing all over it's making it very huge file with white spaces and line like '-------'see below pic. I tried trimming, removing special characters.

Here is my job.

Select data.BlockingSessionID,data.VictimSessionID,LTRIM(RTRIM(data.BlockingQuery)),LTRIM(RTRIM(data.VictimQuery)),data.WaitDurationSecond,data.WaitType,data.BlockingQueryCompletePercent

from(
SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,

     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,

     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0) data

and here is the screen shots or the file where whole Victim and Blocking query get separated by spaces.

Here are some screen shots as how it looks like when it writes to file.This is the result when lock happens, i would like to write this to text file but it writes with thousands of white spaces. See below pic.

Actual Text file,

Answer 1

Manually format your output as a single string to avoid this. For example:

Select cast(data.BlockingSessionID as varchar(max)) + '||' + 
    cast(data.VictimSessionID as varchar(max) + '||' + 
    LTRIM(RTRIM(data.BlockingQuery)) + '||' + 
    LTRIM(RTRIM(data.VictimQuery)) + '||' + 
    CAST(data.WaitDurationSecond as varchar(max)) + '||' + 
    data.WaitType + '||' +
    Cast(data.BlockingQueryCompletePercent as varchar(max))
from(
SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,




     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,

     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0) data