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,

sorry that doesnt make sense. I would try reducing your query until you can have something working correctly and then start adding fields again. — Juan Carlos Oropeza, Dec 13 '16 at 17:16
What method are you using to write the data to your output file? — Laughing Vergil, Dec 13 '16 at 17:21
just from the Job it self, T-SQL Output to perticular file and append results. — user2040021, Dec 13 '16 at 17:22
What is happening is that the output is being padded based on the possible length of the fields. This happens sometimes when headings are being created for your output, depending on the output method. Basically, the system is trying to format the output into columns without knowing how much data to expect, so it uses the maximum length that it can receive as the column size, and formats the output based on that. — Laughing Vergil, Dec 13 '16 at 17:24

score 2 · Accepted Answer · answered Dec 13 '16 at 17:32

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

Thanks @laughing Vergil for this, it does everything in one line now but still have those -------------------------------------- and white spaces and they are a lot. I guess I can work with this for now, until we figure out how to take care of above "------" and "white Spaces" — user2040021, Dec 13 '16 at 17:40
Actually it worked out perfectly when I write into SQL File. Thanks again. — user2040021, Dec 13 '16 at 17:42

SQL Agent Job output in text file formatting

1 Answers1