I'm to replace 2 hidden characters CHAR(0xC2) and CHAR(0x92).
SELECT CHAR(0xC2) CHAR1, CHAR(0x92) CHAR2
I can select them and replace them with this:
SELECT REPLACE(Column1, CHAR(0xC2) + CHAR(0x92), '')
, but can't replace them when selecting from the table. Any ideas why?
Can you post the actual code that you're using when trying to select from the table along with the datatype and collation of the column in question, please?
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 '%?%'