Weird character conversion
-
Hi, Maybe somebody can explain a weird phenomenon I am, sometimes, seeing. Using Microsoft SQL server (edition 2000) I have a stored procedure which returns a particular set of results. The result set is a simple SELECT from a temporary table, the odd result occurs in this returned column:
WC_Diff = CASE WHEN EmpDiff > 0 AND WC_ID > 0 THEN
'+' + CAST(EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST (NoOfEmp AS VARCHAR(3)) + ')'
WHEN EmpDiff < 0 AND WC_ID > 0 THEN
CAST (EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST(NoOfEmp AS VARCHAR(3)) + ')'
ELSE NULL END,The weird bit is the way the '»' character changes every now and again. Using Microsoft's Query analyzer (or a variety of other client programs) the '»' character is sometimes modified to a '+' character. In the few cases it happens all I need to do to get the right output again is recompile the procedure without any change at all. I first thought it was a problem with 8 bit ASCII to 7 bit ASCII conversion but that is not right: '»' has hex code 0xBB which would be converted to 0x3B (';') but it becomes 0x2b ('+'). I am baffled: anyone out there who knows what may be wrong ? Any suggestions much appreciated.
-
Hi, Maybe somebody can explain a weird phenomenon I am, sometimes, seeing. Using Microsoft SQL server (edition 2000) I have a stored procedure which returns a particular set of results. The result set is a simple SELECT from a temporary table, the odd result occurs in this returned column:
WC_Diff = CASE WHEN EmpDiff > 0 AND WC_ID > 0 THEN
'+' + CAST(EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST (NoOfEmp AS VARCHAR(3)) + ')'
WHEN EmpDiff < 0 AND WC_ID > 0 THEN
CAST (EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST(NoOfEmp AS VARCHAR(3)) + ')'
ELSE NULL END,The weird bit is the way the '»' character changes every now and again. Using Microsoft's Query analyzer (or a variety of other client programs) the '»' character is sometimes modified to a '+' character. In the few cases it happens all I need to do to get the right output again is recompile the procedure without any change at all. I first thought it was a problem with 8 bit ASCII to 7 bit ASCII conversion but that is not right: '»' has hex code 0xBB which would be converted to 0x3B (';') but it becomes 0x2b ('+'). I am baffled: anyone out there who knows what may be wrong ? Any suggestions much appreciated.
Hi Again, It took a while but I figured out what is the cause and the solution (google helped a lot). Cause: each night the procedure is recompiled using 'OSQL' (to keep a development server up to date after copying the real server's data bases just before that). That command line utility uses OEM code page 437 and when reading the usual ANSI format SQL files it converts various things which causes the character to change. Solution: when reading UNICODE format files OSQL makes no conversions and everything works. Both the Query analyzer and OSQL produce the same result. Once you know its a piece of cake and very logical but it definitely does not show up in any manuals, at least none that I have seen. Bye for now, Filip