Microsoft KB Archive/188574

= PRB: Decimal Values Passed to a Stored Procedure Get Truncated =

Article ID: 188574

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q188574



SYMPTOMS
Values passed into a Stored Procedure may lose their decimal values.



CAUSE
This depends on the ability of the backend server to define constraints or numeric scale for arguments used by a stored procedure. This is possible under SQL Server, but it is not a feature in Oracle.



RESOLUTION
If you are using Oracle and Remote Data Objects (RDO) the workaround is to change the Parameters Type property to rdNumeric. If you are using ActiveX Data Objects (ADO) and Oracle, define an appropriate Parameter object(s) and set the Parameter's NumericScale property accordingly.

Under SQL Server a numeric scale or a constraint must be defined on the stored procedures parameter. If this is defined, then decimal values will be received by the stored procedure. If the parameters are not defined with numeric scale then the decimal values will be truncated.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
  Use the following sample SQL Server Scripts to generate the stored procedures and table. The first stored procedure illustrates the problem. The second stored procedure demonstrates how to define input parameters with numeric scale: if exists (select * from sysobjects where id =  object_id('dbo.numericscale') and sysstat & 0xf = 3      drop table dbo.numericscale   GO

CREATE TABLE dbo.numericscale (     mynum numeric(5, 2) NULL,      mydec decimal(5, 2) NULL   ) GO

if exists (select * from sysobjects where id =  object_id('dbo.SimpleSQL1') and sysstat & 0xf = 4) drop procedure dbo.SimpleSQL1 GO

CREATE PROCEDURE SimpleSQL1 @input1 numeric, @input2 decimal AS  INSERT into numericscale values (@input1, @input2) GO

if exists (select * from sysobjects where id =  object_id('dbo.SimpleSQL2') and sysstat & 0xf = 4) drop procedure dbo.SimpleSQL2 GO

CREATE PROCEDURE SimpleSQL2 @input1 numeric(5,2), @input2 decimal(5,2) AS  INSERT into numericscale values (@input1, @input2) GO                   

The Visual Basic ADO code for calling the stored procedures is shown in step 3 below.

 Add a reference to Microsoft ActiveX Data Objects in the project.  Add the following ADO code to the default form's Load method:

Note You must change User UID= and PWD= to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Dim cn As ADODB.Connection Dim cm1 As ADODB.Command Dim cm2 As ADODB.Command Dim pm1 As ADODB.Parameter Dim pm2 As ADODB.Parameter Dim rs as ADODB.Recordset

Set cn = New ADODB.Connection Set cm1 = New ADODB.Command Set cm2 = New ADODB.Command Set pm1 = New ADODB.Parameter Set pm2 = New ADODB.Parameter Set rs = New ADODB.Recordset

With cn     .ConnectionString = "DRIVER={SQL SERVER};" & _ "SERVER=;" & _ "UID= ;PWD= " .Open .DefaultDatabase = "pubs" End With

With pm1 .Direction = adParamInput .Name = "param1" .NumericScale = 2 ' this corresponds to the second sp's numeric ' scale. .Precision = 10 .Size = 19 .Type = adNumeric .Value = 3.2 End With

With pm2 .Direction = adParamInput .Name = "param2" .NumericScale = 2 .Precision = 10 .Size = 19 .Type = adNumeric .Value = 3.2 End With

With cm1 Set .ActiveConnection = cn     .CommandType = adCmdStoredProc .CommandText = "simplesql1" .Parameters.Append pm1 .Parameters.Append pm2 .Parameters(0).Value = 3.25 .Parameters(1).Value = 4.26 End With cm1.Execute

Set cm1.ActiveConnection = nothing

With cm2 Set .ActiveConnection = cn     .CommandType = adCmdStoredProc .CommandText = "simplesql2" .Parameters.Append pm1 .Parameters.Append pm2 .Parameters(0).Value = 5.35 .Parameters(1).Value = 6.46 End With

cm2.Execute Set cm1 = Nothing Set cm2 = Nothing

Set rs = cn.Execute("select * from numericscale")

While Not rs.EOF Debug.Print rs(0).Name & ": " & rs(0) Debug.Print rs(1).Name & ": " & rs(1) rs.MoveNext Wend

rs.Close Set rs = Nothing cn.Close Set cn = Nothing

Unload Me                    Run the form.</li></ol>

The Visual Basic RDO code for calling the stored procedures is in step 3 below.

 Create a new Visual Basic (VB) Standard EXE project.</li> Add a Project reference to Microsoft Remote Data Objects.</li>  Add the following code to the default form's Load method:

Note You must change User UID = and PWD = to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Dim en As rdoEnvironment Dim cn As rdoConnection Dim rs As rdoResultset Dim rq1 As rdoQuery Dim rq2 As rdoQuery Dim val1 As Double Dim val2 As Double

val1 = 8.2 val2 = 9.2

Set en = rdoEngine.rdoEnvironments(0) en.CursorDriver = rdUseOdbc

Set cn = en.OpenConnection("",rdDriverNoPrompt,," & _                             "DRIVER={SQL Server};" & _                              "Server=matthofa;" & _                              "UID= ;PWD= ;" & _                              "DATABASE=pubs")

Set rq1 = cn.CreateQuery("", "{Call simplesql1(?,?) }") rq1.rdoParameters(0).Direction = rdParamInput rq1.rdoParameters(0).Value = val1 rq1.rdoParameters(1).Direction = rdParamInput rq1.rdoParameters(1).Value = val2

Set rq2 = cn.CreateQuery("", "{Call simplesql2(?,?) }") rq2.rdoParameters(0).Direction = rdParamInput rq2.rdoParameters(0).Value = val1 rq2.rdoParameters(1).Direction = rdParamInput rq2.rdoParameters(1).Value = val2

rq1.Execute rq2.Execute rq1.Close rq2.Close

Set rs = cn.OpenResultset("select * from numericscale") While Not rs.EOF Debug.Print rs(0).Name & ": " & rs(0) Debug.Print rs(1).Name & ": " & rs(1) rs.MoveNext Wend

cn.Close en.Close Unload Me                   </li> Run the application.</li></ol>

Keywords: kboracle kbrdo kbstoredproc kbdatabase kbprb KB188574

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.