ALter User Defined Data Types
-
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.
-
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
-
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