Microsoft KB Archive/300480

= FIX: OPENXML WITH TableName Statement May Return Incorrect Result if Datatype is DECIMAL =

Article ID: 300480

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q300480



BUG #: 352890 (SHILOH_BUGS)



SYMPTOMS
When you use an OPENXML WITH TableName statement to view data that uses the DECIMAL data type, the data values may be incorrect. Refer to the &quot;More Information&quot; for an example.



CAUSE
OLE DB does not properly handle the DECIMAL data type.



RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem either:
 * Use the NUMERIC data type if the WITH TableName option is necessary.

-or-


 * Use the WITH SchemaDeclaration option.



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.



Steps to Reproduce Behavior
  Create the tables to be used with OPENXML: drop table openxmltbl_dec go drop table openxmltbl_num go create table openxmltbl_dec (    ContactName varchar(20),     Rating decimal (18,10)   -- Uses DECIMAL data type ) go create table openxmltbl_num (    ContactName varchar(20),     Rating numeric (18,10)   -- Uses NUMERIC data type ) go   Create an internal representation of the XML document to be used by OPENXML: set nocount on DECLARE @idoc int DECLARE @doc varchar(1000) SET @doc ='     '

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc </li>  Execute a SELECT statement that uses the OPENXML rowset provider: -- The following code demonstrates the problem

print 'Using WITH TableName and DECIMAL data type... incorrect results' select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH openxmlTbl_dec

-- The following code demonstrates the workaround

print '' print 'Using WITH TableName and NUMERIC data type... correct results' select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH openxmlTbl_num print '' print 'Using WITH SchemaDeclaration ... correct results' select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH ( ContactName varchar(20),Rating decimal (18,10))

EXEC sp_xml_removedocument @idoc </li></ol>

RESULTS
Using WITH TableName and DECIMAL data type, incorrect results display: <pre class="fixed_text">ContactName         Rating Paul Henriot        2828849149941180153. Using WITH TableName and NUMERIC data type the correct results display: <pre class="fixed_text">ContactName         Rating Paul Henriot        1.0123456789 Using WITH SchemaDeclaration the correct results display: <pre class="fixed_text">ContactName         Rating Paul Henriot        1.0123456789 The correct value for Rating is 1.0123456789

Additional query words: maximum max precision OLEDB

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB300480

-

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

© Microsoft Corporation. All rights reserved.