November 1, 2005 at 12:18 pm
I have a scenario where I want to use a bigint variable as a substitution parameter within a RAISERROR statement. When the value of the bigint variable exceeds max(int) the result is wrong in SQL 2000. The statement produces error 2786 (The data type of substitution parameter 1 does not match the expected type of the format specification.) in SQL 2005 regardless of the value of the bigint.
Has anyone else come across this? Does anyone know why it doesn't work, or why BOL isn't clear on this? Am I missing something with regards to the parameter markers in the message string?
The workaround I am using is to cast the bigint to a varchar then use the varchar as the substitution parameter.
Regards, Rhys
Sample code;
declare @i bigint
set @i = 0
raiserror('My zero bigint value is %i.',10,1,@i)
set @i = 2147483647
raiserror('My max-int bigint value is %i.',10,1,@i)
set @i = 9223372036854775807
raiserror('My max-bigint bigint value is %i.',10,1,@i)
November 2, 2005 at 6:36 am
RAISERROR arugment type i specifies an integer. BigInt is not an integer (which uses 4 bytes of storage). Although similar, they are not the same datatype. When you run your code in SQL Server 2000, RAISERROR just looks at the low-order 4 bytes, so 9223372036854775807 is interpreted as a signed 4 byte integer. Since all bits are set, this is -1.
Also, BOL does mention this data type limitation.
-------------------------------------------------------
argument
Is the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: int1, int2, int4, char, varchar, binary, or varbinary. No other data types are supported.
-------------------------------------------------------
If bigint was supported, it would have listed int8.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply