• Jeff Moden - Tuesday, March 13, 2018 8:06 PM

    SQLHeap - Tuesday, March 13, 2018 8:35 AM

    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 '%?%'



    There is an exception to every rule, except this one...