I'm not sure I'd have been so quick to change the collation of the column without understanding the implications of doing so. If it's just one piece of code affected, I think I'd have added a COLLATE clause to the query, something like this:

DECLARE @unitID Int

SET @unitID = (SELECT TOP 1 ID FROM Units)

INSERT INTO UnitLogos (LogoPath, UnitID, PrimaryLogo) (SELECT DISTINCT ProgramLogo COLLATE SQL_Latin1_General_CP1_CI_AS, @unitID, 0 FROM Programs WHERE LTRIM(RTRIM(ProgramLogo)) <> '' AND LTRIM(RTRIM(ProgramLogo)) NOT IN (SELECT LogoPath FROM UnitLogos))

UPDATE Programs SET UnitLogoID = (SELECT ID FROM UnitLogos WHERE LTRIM(RTRIM(LogoPath)) = LTRIM(RTRIM(Programs.ProgramLogo))) WHERE LTRIM(RTRIM(Programs.ProgramLogo)) <> ''

GO

John