Jeff Moden (5/27/2014)

---

Ryan Keast (5/27/2014)

---

Hi there,

I had the following script that used to work in SQL2000 but now that I have migrated it, it no longer does.

,TEN.[tncy-sys-ref]

+ CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0'END AS 'ID'

This used to either add a 1 or a 2 to the end of the TEN.[tncy-sys-ref]. What it does now is actually make an addition.

Example the TEN.[tncy-sys-ref] is 25337.

On SQL2000 it is 253371 for a Male or 253372 for a female.

On SQL2012 it's coming back 25338 for a male and 25339 for a female.

I need it to concatenate instead of addition.

If that's true, then something went haywire with the migration because that code will still work as you have it if TEN.[tncy-sys-ref] is still a VARCHAR() and could only work as is if it was a VARCHAR(). Check the old system that you migrated and see if the column was, infact a VARCHAR() because I think it was and something changed it during the migration.