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

Question

In SQL Server 2008, the following error appears:

Cannot specify decimal type (5.2) (parameter 4) as a substitute for parameter.

Just looked at the trigger on the table, and it looks like the problem is with this if

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

Answer 1

Problem easy to reproduce

 declare @SumField7 decimal(5,2) = 123.45 raiserror ('...%d...', 16, 1, @SumField7) 

You specify %d as the Type specification, which represents a signed integer , but passing decimal to it. Perhaps this has never been type checked in SQL Server 2000.

There seems to be no syntax for decimal holders, and you will need to pass a string as shown 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 RAISERROR and inconsistency vs vs placeholder parameters.

Do you have a trigger with RAISERROR? This is not an INSERT ...