As Gail said, SQL Server won't show an error like that. SSIS might do that and you should specify that because the syntax is completely different.

If the number goes over 99, you'll have problems with the following method and you'll need to change it. Note how I'm including the data in a way that can be easily used by anyone.

DECLARE @test-2 TABLE(

dogbreed varchar(20),

someint int)

INSERT @test-2 VALUES

('boxer', 1),

('lab', 2),

('poodle', 11)

SELECT *,

dogbreed + RIGHT('0' + CAST(someint as varchar(2)), 2),

dogbreed + RIGHT( 100 + someint, 2)

FROM @test-2