Jeff Moden - Wednesday, March 14, 2018 2:17 PM

SQLHeap - Wednesday, March 14, 2018 6:22 AM

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

Such a conversion might not be the right thing to do.  Also, that While Loop is going to cause performance to suffer a bit if you use it on something larger.