September 6, 2005 at 4:28 am
September 6, 2005 at 8:13 am
let me define the problem so that maybe you can be of greater help.
i have an sql db 2000 with a stored proc that reads an xml file.the file contains a contribution amount EE.
----------------------------------------------
SELECT @BatchNo,
dbo.ftn_MemberCode2(@FundCode,BranchCode,EmployeeNo,surname,firstname)
,pSalary = ((100*EE)/@EERate),
0 as 'IsRate',AVC,GETDATE(), CURRENT_USER
FROM OPENXML (@idoc, '/NewDataSet/cp_getClientEESchedule',2)
WITH (
BranchCode pen_ClientCode,
EmployeeNo pen_StaffCode,
Surname pen_Surname,
FirstName pen_Firstname,
EE pen_Money,
AVC pen_Money)
-----------------------------------------------
when i read a value like 40682.9800
it is converted to 40683.0000
mind you i am using user defined data types like pen_Money
with a base type decimal(20,4)
when i put the actual base type i.e. decimal(20,4) the amount comes out right.
what is the problem if any with user defined datatypes when using openxml.
🙁
September 7, 2005 at 2:29 am
I think the problem is here:
pSalary = ((100*EE)/@EERate)
Change to:
pSalary = ((CAST(100 AS money)*EE)/@EERate),
SQL Server takes the left most value in an expression as the resulting data type, and since your 100 did not define a data type, SQL Server converts 100 into an int, which results in nothing in the decimal portion of the resulting expression.
Andy
September 7, 2005 at 7:13 am
thanks for the reply but i don't think that, that is where the problem is.
because when i changed
EE pen_Money,
AVC pen_Money
in the openxml part to
EE Money,
AVC Money
or
EE decimal(20,4),
AVC decimal(20,4)
the problem was not repeated. so i am still thinking that the problem is with the udt. 🙁
don't i need to apply service pack 3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply