SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacing hidden characters


Replacing hidden characters

Author
Message
SQLHeap
SQLHeap
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 789
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?















There is an exception to every rule, except this one...
sgmunson
sgmunson
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85852 Visits: 6727
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?



Any chance that the characters are NOT contiguous? You could test for this as follows:
SELECT REPLACE(REPLACE(Column1, CHAR(0xC2), ''), CHAR(0x92), '')



Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
SQLHeap
SQLHeap
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 789

Tried that already, didn't work. Thanks for the reply though.



There is an exception to every rule, except this one...
ZZartin
ZZartin
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 16453
Try doing a varbinary on the column in question and make sure that data is actually what you expect.
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52953 Visits: 8908
I don't know what the issue is here but you could use ngrams8k to perform an character by character analysis like this:

-- sample data
DECLARE @table table(someId int identity, someString varchar(100));
INSERT @table VALUES ('xxx123'),(CONCAT('abc',CHAR(0xC2),CHAR(0x92),'!'))

-- character-by-character analysis
SELECT
t.someId,
t.someString,
ng.position,
ng.token,
ascii_value = ASCII(ng.token),
unicode_value = UNICODE(ng.token)
FROM @table t
CROSS APPLY dbo.ngrams8k(t.someString,1) ng;


Returns:



-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)

Group: General Forum Members
Points: 846441 Visits: 46650
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?


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLHeap
SQLHeap
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 789
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...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)

Group: General Forum Members
Points: 846441 Visits: 46650
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.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLHeap
SQLHeap
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 789
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.

Just curious, why wouldn't the conversion be the right thing to do? And yes, a while loop in a cursor typically is horrible performance. It's RBAR^2 lol.


There is an exception to every rule, except this one...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)SSC Guru (846K reputation)

Group: General Forum Members
Points: 846441 Visits: 46650
SQLHeap - Thursday, March 15, 2018 5:46 AM
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.

Just curious, why wouldn't the conversion be the right thing to do? And yes, a while loop in a cursor typically is horrible performance. It's RBAR^2 lol.

The Conversion (appears to be a simple RBAR exclusion of characters here) may not be the thing to do because there actually could be data that needs to be NVARCHAR() to accommodate special characters in this world wide computing environment. You also have some hidden datatype mismatches and at least one of them may cause an error someday in the future. No object name in SQL Server will ever be longer than NVARCHAR(128) so things like having NVARCHAR(1000) for a job name or step name is serious overkill. That's why the "SYSNAME" alias datatype is an NVARCHAR(128) under the hood. That also means that a ServerName could also be much longer than just 20 characters. Because of the datatype mismatches, you're also doing implicit conversions all over the place and even the easy ones take extra clock cycles and a bit of memory.

I realize that you might think that "performance isn't important" here but it's always important and a very close second only to producing correct results. MSDB history can actually become very large thanks to job/step history and then there's the idea that however you've written this code might be what someone else uses as a model in a pinch.

And please understand that I'm not slamming you. Please consider this to be a peer review with some tips for not only this code, but for other code. Implicit conversions due to datatype/length matches (especially when it comes to join and filter criteria) and excluding characters just to make something work can and will become very serious problems in the future.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search