Jeff Moden - Tuesday, March 13, 2018 8:06 PM
Sure, but I realized late yesterday that since it's Unicode, I can just convert to Varchar and be done with it :Whistling:
DECLARE @varTable TABLE (ServerName NVARCHAR(20), JobName NVARCHAR(1000), Step INT, StepName NVARCHAR(1000), Message NVARCHAR(MAX)
, Status NVARCHAR(20), RunDate INT, RunTime INT, Duration INT)
DECLARE @Result NVARCHAR(MAX)= '', @Nchar NCHAR(1), @Position INT, @ServerName NVARCHAR(20) = 'COM01-SQL02', @JobName NVARCHAR(1000)
, @Step INT, @StepName NVARCHAR(1000), @Message NVARCHAR(MAX), @status NVARCHAR(20), @RunDate INT, @RunTime INT, @Duration INT
DECLARE curMessage CURSOR FOR
SELECT @ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
, Message = LEFT(REPLACE(HIST.Message, '?', ''), 4000)
, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
FROM msdb.dbo.sysjobs JOB
INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE HIST.Message LIKE '%?%'
ORDER BY HIST.run_date, HIST.run_time
OPEN curMessage
FETCH NEXT FROM curMessage INTO @ServerName, @JobName, @Step, @StepName, @Message, @status, @RunDate, @RunTime, @Duration
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Position = 1
SET @Result = ''
WHILE @Position <= LEN(@Message)
BEGIN
SET @Nchar = SUBSTRING(@Message, @Position, 1)
IF UNICODE(@Nchar) BETWEEN 32 AND 255
SET @Result = @Result + @Nchar
SET @Position = @Position + 1
END
select @Result
--INSERT INTO @varTable (ServerName, JobName, Step, StepName, Message, Status, RunDate, RunTime, Duration)
--SELECT @ServerName, @JobName, @Step, @StepName, @Result, @status, @RunDate, @RunTime, @Duration
--SELECT 'COM01-SQL02' AS ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
--, Message = @Result
--, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
--WHEN HIST.run_status = 1 THEN 'Succeeded'
--WHEN HIST.run_status = 2 THEN 'Retry'
--WHEN HIST.run_status = 3 THEN 'Canceled'
--END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
--FROM msdb.dbo.sysjobs JOB
--INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
--WHERE HIST.Message LIKE '%?%'
FETCH NEXT FROM curMessage INTO @ServerName, @JobName, @Step, @StepName, @Message, @status, @RunDate, @RunTime, @Duration
END
CLOSE curMessage
DEALLOCATE curMessage
SELECT *
FROM @varTable
UNION
SELECT 'COM01-SQL02' AS ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
, Message = LEFT(HIST.Message, 4000)
, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
FROM msdb.dbo.sysjobs JOB
INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE HIST.Message NOT LIKE '%?%'
There is an exception to every rule, except this one...