"Cannot specify decimal(5,2) data type (parameter 4) as a substitution parameter" error on SQL Server 2008

Question

In SQL Server 2008 I get the following error:

Cannot specify decimal(5,2) data type (parameter 4) as a substitution parameter.

Just had a look at the trigger on the table and it looks like the issue has to do with this if

if @SumField7 <> 100
    begin
        rollback tran
        raiserror ('...%d...', 16, 1, @SumField7)
    end

Answer 1

The issue is easy to reproduce

declare @SumField7 decimal(5,2) = 123.45

raiserror ('...%d...', 16, 1, @SumField7)

You are specifying %d as the Type specification which represents signed integer but passing it a decimal. Maybe this was never type checked in SQL Server 2000.

It looks like there is no syntax for decimal place holders and you would need to pass a string instead as below.

declare @SumField7 decimal(5,2) = 123.45
declare @SumField7String varchar(7) = @SumField7

raiserror ('...%s...', 16, 1, @SumField7String)

Answer 2

The error is usually caused by a RAISERROR and mismatch of parameters vs placeholder.

Do you have a trigger with RAISERROR? It isn't the INSERT...