CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-agent-job
user2040021
user2040021

Reputation: 309

SQL Agent Job output in text file formatting

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.

enter image description here

Actual Text file, enter image description here

enter image description here

enter image description here

Upvotes: 2

Views: 3741

Answers (1)

Laughing Vergil
Laughing Vergil

Reputation: 3756

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

Upvotes: 2

Related Questions