Invalid scale value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invalid scale value

Author

Topic

Shanti
Starting Member

3 Posts

Posted - 2005-05-20 : 16:06:11

When trying to call the following function I'm getting an ODBC SQL Server Driver error, "Invalid scale value". I haven't ever had to pass a Numeric like this, and I'm unsure how I should go about it.

Any help is most appreciated! :)

Function GetEOITriggers(conn, PlnKey, PlnEOIBenAmtTrigger, PlnEOIMultiplierTrigger)
	Dim cmd
	Set cmd = server.createobject("ADODB.Command")

	cmd.CommandText = "sp_GetEOITriggers"
	cmd.ActiveConnection = conn
	cmd.CommandType = adCmdStoredProc
	cmd.Parameters.Append cmd.CreateParameter("PlnKey", adInteger, adParamInput, 4, PlnKey)
	cmd.Parameters.Append cmd.CreateParameter("PlnEOIBenAmtTrigger", adCurrency, adParamOutput, 4, PlnEOIBenAmtTrigger)
	cmd.Parameters.Append cmd.CreateParameter("PlnEOIMultiplierTrigger", adNumeric, adParamOutput, 18, PlnEOIMultiplierTrigger)
	cmd.Parameters("PlnEOIMultiplierTrigger").NumericScale=10

	cmd.Execute
	PlnEOIBenAmtTrigger = cmd.parameters("PlnEOIBenAmtTrigger").Value
	PlnEOIMultiplierTrigger = cmd.parameters("PlnEOIMultiplierTrigger").Value
	Set cmd = Nothing
End Function

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-20 : 16:25:06

I've never had to use the NumericScale property on a Parameter. Are you sure you didn't really just want to set the .Value property instead?

---------------------------
EmeraldCityDomains.com

Shanti
Starting Member

3 Posts

Posted - 2005-05-20 : 16:30:16

I found the NumericScale reference on this post: http://sqlteam.com/Forums/topic.asp?TOPIC_ID=14773

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-20 : 17:08:45

OK, so did you double-check your arguments list in the stored procedure to make sure that you are using the same precision and scale? RE-read Rob's comments about how they need to match. So, if you are setting the NumericScale in .NET to be 10, then you must have in your stored procedure a parameter defined as numeric(x,10) where x is whatever appropriate precision.

---------------------------
EmeraldCityDomains.com

Shanti
Starting Member

3 Posts

Posted - 2005-05-23 : 12:59:45

Yes, I am aware that the numbers need to match. Here is my stored proc:

ALTER  PROCEDURE [sp_GetEOITriggers] @PlnKey INT, @PicMax MONEY OUTPUT, @BimMult NUMERIC(18,10) OUTPUT
AS
BEGIN

DECLARE main_cursor CURSOR FOR
	SELECT PicMax, BimMult FROM IHBenInsConfig
	INNER JOIN IHBenInsConfigMult ON PicKey = BimPicKey
	WHERE PicPlnKey = @PlnKey
OPEN main_cursor
FETCH NEXT FROM main_cursor INTO @PicMax, @BimMult
SET NOCOUNT ON 
CLOSE main_cursor
DEALLOCATE main_cursor
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources