The connection setting SET ANSI_PADDING controls the behavior that you're seeing. If SET ANSI_PADDING is ON, trailing blanks inserted into a varchar column are not truncated. If SET ANSI_PADDING is OFF, trailing blanks inserted into a varchar column ARE truncated.

-Mike