Solved

error converting data type nvarchar to numeric

Posted on 2006-10-27
11
1,181 Views
Last Modified: 2012-06-27
Hi,

I am writing some XML data to tables  using OPENXML.  I have a problem writing to 1 table.  The error I get is 'error converting data type nvarchar to numeric'.  There is no nvarchar column in the table, only 1 int column and the rest are all numeric (18, 0).  below is the stored procedure block that attempts to write to my table (bm_overheads) and the XML element holding the data.  I hope someone can figure out where the error is coming from.

====== sproc block =========

            -- Add to bm_overheads
            INSERT INTO
                  bm_overheads
            SELECT
                  @bmId AS bmId, FullTimeLabour, FullTimeLabourCC, FullTimeLabourCCPercent, PartTimeLabour, PartTimeLabourCC, PartTimeLabourCCPercent,
                  NumberOfPropriators, NotionalLabourCost, NotionalLabourCostCC, NotionalLabourCostCCPercent, TotalLabourCost, TotalLabourCostCC,
                  OtherLabourCosts, MachineryDepn, MachineryRepairs, MachineryRepairsCC, MachineryRepairsCCPercent, VehicleRepairs, VehicleRepairsCC,
                  VehicleRepairsCCPercent, MachineryFuel, MachineryFuelCC, MachineryFuelCCPercent, VehicleFuel, VehicleFuelCC, VehicleFuelCCPercent,
                  Haulage, HaulageCC, HaulageCCPercent, GrainStorage, GrainStorageCC, GrainStorageCCPercent, ElectricAndGas, ElectricAndGasCC,
                  ElectricAndGasCCPercent, ContractStaff, ContractStaffCC, ContractStaffCCPercent, TotalMachineryCost, TotalMachineryCostCC,
                  OtherMachineryCosts, PropertyDepn, Repairs, RepairsCC, RepairsCCPercent, CouncilTax, CouncilTaxCC, CouncilTaxCCPercent, WaterRates,
                  WaterRatesCC, WaterRatesCCPercent, Drainage, DrainageCC, DrainageCCPercent, Lime, LimeCC, LimeCCPercent, PestControl, PestControlCC,
                  PestControlCCPercent, TotalPropertyCost, TotalPropertyCostCC, OtherPropertyCosts, InsuranceCosts, InsuranceCostsCC, InsuranceCostsCCPercent,
                  ProfessionalFees, ProfessionalFeesCC, ProfessionalFeesCCPercent, OtherAdminCosts, OtherAdminCostsCC, OtherAdminCostsCCPercent,
                  Subscriptions, SubscriptionsCC, SubscriptionsCCPercent, Miscellaneous, MiscellaneousCC, MiscellaneousCCPercent, SecretarialSupport,
                  SecretarialSupportCC, SecretarialSupportCCPercent, TotalAdminCost, TotalAdminCostCC, OtherAdministrationCosts, AhaTenancies, AhaTenanciesCC,
                  AhaTenanciesCCPercent, FarmTenancies, FarmTenanciesCC, FarmTenanciesCCPercent, NoRentArea, NoRentAreaRent, NotionalRentCC, NotionalRentCCPercent,
                  TotalRentCost, TotalRentCostCC, OtherRentCosts, OverdraftInterest, OverdraftInterestCC, OverdraftInterestCCPercent, BankCharges,
                  BankChargesCC, BankChargesCCPercent, LoanInterest, LoanInterestCC, LoanInterestCCPercent, HpInterest, HpInterestCC, HpInterestCCPercent,
                  TotalFinanceCostCC, OtherFinanceCosts
            FROM
                  OPENXML (@idoc, 'Benchmark/FarmDetails/Overheads', 2)
            WITH (FullTimeLabour       numeric (15, 2)                                          'FullTimeLabour',
                        FullTimeLabourCC       numeric (15, 2)                                    'FullTimeLabourCC',
                        FullTimeLabourCCPercent       numeric (15, 2)                        'FullTimeLabourCCPercent',
                        PartTimeLabour       numeric (15, 2)                                          'PartTimeLabour',
                        PartTimeLabourCC       numeric (15, 2)                                    'PartTimeLabourCC',
                        PartTimeLabourCCPercent      numeric (15, 2)                              'PartTimeLabourCCPercent',
                        NumberOfPropriators       numeric (15, 2)                              'NumberOfPropriators',
                        NotionalLabourCost       numeric (15, 2)                                    'NotionalLabourCost',
                        NotionalLabourCostCC       numeric (15, 2)                              'NotionalLabourCostCC',
                        NotionalLabourCostCCPercent       numeric (15, 2)                  'NotionalLabourCostCCPercent',
                        TotalLabourCost       numeric (15, 2)                                    'TotalLabourCost',
                        TotalLabourCostCC      numeric (15, 2)                                    'TotalLabourCostCC',
                        OtherLabourCosts       numeric (15, 2)                                    'OtherLabourCosts',
                        MachineryDepn       numeric (15, 2)                                          'MachineryDepn',
                        MachineryRepairs       numeric (15, 2)                                    'MachineryRepairs',
                        MachineryRepairsCC       numeric (15, 2)                                    'MachineryRepairsCC',
                        MachineryRepairsCCPercent       numeric (15, 2)                        'MachineryRepairsCCPercent',
                        VehicleRepairs       numeric (15, 2)                                          'VehicleRepairs',
                        VehicleRepairsCC       numeric (15, 2)                                    'VehicleRepairsCC',
                        VehicleRepairsCCPercent       numeric (15, 2)                        'VehicleRepairsCCPercent',
                        MachineryFuel       numeric (15, 2)                                          'MachineryFuel',
                        MachineryFuelCC       numeric (15, 2)                                    'MachineryFuelCC',
                        MachineryFuelCCPercent       numeric (15, 2)                              'MachineryFuelCCPercent',
                        VehicleFuel       numeric (15, 2)                                          'VehicleFuel',
                        VehicleFuelCC       numeric (15, 2)                                          'VehicleFuelCC',
                        VehicleFuelCCPercent       numeric (15, 2)                              'VehicleFuelCCPercent',
                        Haulage       numeric (15, 2)                                                'Haulage',
                        HaulageCC       numeric (15, 2)                                                'HaulageCC',
                        HaulageCCPercent       numeric (15, 2)                                    'HaulageCCPercent',
                        GrainStorage       numeric (15, 2)                                          'GrainStorage',
                        GrainStorageCC       numeric (15, 2)                                          'GrainStorageCC',
                        GrainStorageCCPercent       numeric (15, 2)                              'GrainStorageCCPercent',
                        ElectricAndGas       numeric (15, 2)                                          'ElectricAndGas',
                        ElectricAndGasCC       numeric (15, 2)                                    'ElectricAndGasCC',
                        ElectricAndGasCCPercent       numeric (15, 2)                        'ElectricAndGasCCPercent',
                        ContractStaff       numeric (15, 2)                                          'ContractStaff',
                        ContractStaffCC       numeric (15, 2)                                    'ContractStaffCC',
                        ContractStaffCCPercent       numeric (15, 2)                              'ContractStaffCCPercent',
                        TotalMachineryCost       numeric (15, 2)                                    'TotalMachineryCost',
                        TotalMachineryCostCC       numeric (15, 2)                              'TotalMachineryCostCC',
                        OtherMachineryCosts       numeric (15, 2)                              'OtherMachineryCosts',
                        PropertyDepn       numeric (15, 2)                                          'PropertyDepn',
                        Repairs       numeric (15, 2)                                                'Repairs',
                        RepairsCC       numeric (15, 2)                                                'RepairsCC',
                        RepairsCCPercent       numeric (15, 2)                                    'RepairsCCPercent',
                        CouncilTax       numeric (15, 2)                                                'CouncilTax',
                        CouncilTaxCC       numeric (15, 2)                                          'CouncilTaxCC',
                        CouncilTaxCCPercent       numeric (15, 2)                              'CouncilTaxCCPercent',
                        WaterRates       numeric (15, 2)                                                'WaterRates',
                        WaterRatesCC       numeric (15, 2)                                          'WaterRatesCC',
                        WaterRatesCCPercent       numeric (15, 2)                              'WaterRatesCCPercent',
                        Drainage       numeric (15, 2)                                                'Drainage',
                        DrainageCC       numeric (15, 2)                                                'DrainageCC',
                        DrainageCCPercent       numeric (15, 2)                                    'DrainageCCPercent',
                        Lime       numeric (15, 2)                                                      'Lime',
                        LimeCC       numeric (15, 2)                                                      'LimeCC',
                        LimeCCPercent       numeric (15, 2)                                          'LimeCCPercent',
                        PestControl       numeric (15, 2)                                          'PestControl',
                        PestControlCC       numeric (15, 2)                                          'PestControlCC',
                        PestControlCCPercent       numeric (15, 2)                              'PestControlCCPercent',
                        TotalPropertyCost       numeric (15, 2)                                    'TotalPropertyCost',
                        TotalPropertyCostCC       numeric (15, 2)                              'TotalPropertyCostCC',
                        OtherPropertyCosts       numeric (15, 2)                                    'OtherPropertyCosts',
                        InsuranceCosts       numeric (15, 2)                                          'InsuranceCosts',
                        InsuranceCostsCC       numeric (15, 2)                                    'InsuranceCostsCC',
                        InsuranceCostsCCPercent      numeric (15, 2)                              'InsuranceCostsCCPercent',
                        ProfessionalFees       numeric (15, 2)                                    'ProfessionalFees',
                        ProfessionalFeesCC       numeric (15, 2)                                    'ProfessionalFeesCC',
                        ProfessionalFeesCCPercent       numeric (15, 2)                        'ProfessionalFeesCCPercent',
                        OtherAdminCosts       numeric (15, 2)                                    'OtherAdminCosts',
                        OtherAdminCostsCC       numeric (15, 2)                                    'OtherAdminCostsCC',
                        OtherAdminCostsCCPercent       numeric (15, 2)                        'OtherAdminCostsCCPercent',
                        Subscriptions       numeric (15, 2)                                          'Subscriptions',
                        SubscriptionsCC       numeric (15, 2)                                    'SubscriptionsCC',
                        SubscriptionsCCPercent       numeric (15, 2)                              'SubscriptionsCCPercent',
                        Miscellaneous       numeric (15, 2)                                          'Miscellaneous',
                        MiscellaneousCC       numeric (15, 2)                                    'MiscellaneousCC',
                        MiscellaneousCCPercent       numeric (15, 2)                              'MiscellaneousCCPercent',
                        SecretarialSupport      numeric (15, 2)                                    'SecretarialSupport',
                        SecretarialSupportCC       numeric (15, 2)                              'SecretarialSupportCC',
                        SecretarialSupportCCPercent       numeric (15, 2)                  'SecretarialSupportCCPercent',
                        TotalAdminCost       numeric (15, 2)                                          'TotalAdminCost',
                        TotalAdminCostCC       numeric (15, 2)                                    'TotalAdminCostCC',
                        OtherAdministrationCosts       numeric (15, 2)                        'OtherAdministrationCosts',
                        AhaTenancies       numeric (15, 2)                                          'AhaTenancies',
                        AhaTenanciesCC      numeric (15, 2)                                          'AhaTenanciesCC',
                        AhaTenanciesCCPercent       numeric (15, 2)                              'AhaTenanciesCCPercent',
                        FarmTenancies       numeric (15, 2)                                          'FarmTenancies',
                        FarmTenanciesCC       numeric (15, 2)                                    'FarmTenanciesCC',
                        FarmTenanciesCCPercent       numeric (15, 2)                              'FarmTenanciesCCPercent',
                        NoRentArea       numeric (15, 2)                                                'NoRentArea',
                        NoRentAreaRent       numeric (15, 2)                                          'NoRentAreaRent',
                        NotionalRentCC       numeric (15, 2)                                          'NotionalRentCC',
                        NotionalRentCCPercent      numeric (15, 2)                              'NotionalRentCCPercent',
                        TotalRentCost       numeric (15, 2)                                          'TotalRentCost',
                        TotalRentCostCC       numeric (15, 2)                                    'TotalRentCostCC',
                        OtherRentCosts       numeric (15, 2)                                          'OtherRentCosts',
                        OverdraftInterest       numeric (15, 2)                                    'OverdraftInterest',
                        OverdraftInterestCC       numeric (15, 2)                              'OverdraftInterestCC',
                        OverdraftInterestCCPercent       numeric (15, 2)                        'OverdraftInterestCCPercent',
                        BankCharges      numeric (15, 2)                                                'BankCharges',
                        BankChargesCC       numeric (15, 2)                                          'BankChargesCC',
                        BankChargesCCPercent       numeric (15, 2)                              'BankChargesCCPercent',
                        LoanInterest       numeric (15, 2)                                          'LoanInterest',
                        LoanInterestCC       numeric (15, 2)                                          'LoanInterestCC',
                        LoanInterestCCPercent       numeric (15, 2)                              'LoanInterestCCPercent',
                        HpInterest       numeric (15, 2)                                                'HpInterest',
                        HpInterestCC       numeric (15, 2)                                          'HpInterestCC',
                        HpInterestCCPercent      numeric (15, 2)                                    'HpInterestCCPercent',
                        TotalFinanceCostCC       numeric (15, 2)                                    'TotalFinanceCostCC',
                        OtherFinanceCosts      numeric (15, 2)                                    'OtherFinanceCosts')


====== XML element =========

    <Overheads>
      <FullTimeLabour>0</FullTimeLabour>
      <FullTimeLabourCC>0</FullTimeLabourCC>
      <FullTimeLabourCCPercent>0</FullTimeLabourCCPercent>
      <PartTimeLabour>0</PartTimeLabour>
      <PartTimeLabourCC>0</PartTimeLabourCC>
      <PartTimeLabourCCPercent>0</PartTimeLabourCCPercent>
      <NumberOfPropriators>0</NumberOfPropriators>
      <NotionalLabourCost>0</NotionalLabourCost>
      <NotionalLabourCostCC>0</NotionalLabourCostCC>
      <NotionalLabourCostCCPercent>0</NotionalLabourCostCCPercent>
      <TotalLabourCost>0</TotalLabourCost>
      <TotalLabourCostCC>0</TotalLabourCostCC>
      <OtherLabourCosts />
      <MachineryDepn>0</MachineryDepn>
      <MachineryRepairs>0</MachineryRepairs>
      <MachineryRepairsCC>0</MachineryRepairsCC>
      <MachineryRepairsCCPercent>0</MachineryRepairsCCPercent>
      <VehicleRepairs>0</VehicleRepairs>
      <VehicleRepairsCC>0</VehicleRepairsCC>
      <VehicleRepairsCCPercent>0</VehicleRepairsCCPercent>
      <MachineryFuel>0</MachineryFuel>
      <MachineryFuelCC>0</MachineryFuelCC>
      <MachineryFuelCCPercent>0</MachineryFuelCCPercent>
      <VehicleFuel>0</VehicleFuel>
      <VehicleFuelCC>0</VehicleFuelCC>
      <VehicleFuelCCPercent>0</VehicleFuelCCPercent>
      <Haulage>0</Haulage>
      <HaulageCC>0</HaulageCC>
      <HaulageCCPercent>0</HaulageCCPercent>
      <GrainStorage>0</GrainStorage>
      <GrainStorageCC>0</GrainStorageCC>
      <GrainStorageCCPercent>0</GrainStorageCCPercent>
      <ElectricAndGas>0</ElectricAndGas>
      <ElectricAndGasCC>0</ElectricAndGasCC>
      <ElectricAndGasCCPercent>0</ElectricAndGasCCPercent>
      <ContractStaff>0</ContractStaff>
      <ContractStaffCC>0</ContractStaffCC>
      <ContractStaffCCPercent>0</ContractStaffCCPercent>
      <TotalMachineryCost>0</TotalMachineryCost>
      <TotalMachineryCostCC>0</TotalMachineryCostCC>
      <OtherMachineryCosts />
      <MachineryDepreciation>
        <CurrentYear>0</CurrentYear>
        <Expanded>false</Expanded>
        <Machines>
          <DepreciationBase>
            <Index>0</Index>
            <Description>Car 1</Description>
            <PurchasedValue>2000.00</PurchasedValue>
            <PurchasedYear>1998</PurchasedYear>
            <DepnRatePercent>0.2</DepnRatePercent>
            <StartOfYearValue>419.43</StartOfYearValue>
            <CurrentYearDepn>83.89</CurrentYearDepn>
            <EndOfYearValue>335.54</EndOfYearValue>
            <CcDepnPercent>0.1</CcDepnPercent>
            <CurrentYearCCDepn>8.39</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>37.75</CcCurrentYearAverageCapital>
          </DepreciationBase>
          <DepreciationBase>
            <Index>1</Index>
            <Description>Car 2</Description>
            <PurchasedValue>7800</PurchasedValue>
            <PurchasedYear>2003</PurchasedYear>
            <DepnRatePercent>0.12</DepnRatePercent>
            <StartOfYearValue>6040.32</StartOfYearValue>
            <CurrentYearDepn>724.84</CurrentYearDepn>
            <EndOfYearValue>5315.48</EndOfYearValue>
            <CcDepnPercent>0.04</CcDepnPercent>
            <CurrentYearCCDepn>28.99</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>227.12</CcCurrentYearAverageCapital>
          </DepreciationBase>
        </Machines>
      </MachineryDepreciation>
      <PropertyDepn>0</PropertyDepn>
      <Repairs>0</Repairs>
      <RepairsCC>0</RepairsCC>
      <RepairsCCPercent>0</RepairsCCPercent>
      <CouncilTax>0</CouncilTax>
      <CouncilTaxCC>0</CouncilTaxCC>
      <CouncilTaxCCPercent>0</CouncilTaxCCPercent>
      <WaterRates>0</WaterRates>
      <WaterRatesCC>0</WaterRatesCC>
      <WaterRatesCCPercent>0</WaterRatesCCPercent>
      <Drainage>0</Drainage>
      <DrainageCC>0</DrainageCC>
      <DrainageCCPercent>0</DrainageCCPercent>
      <Lime>0</Lime>
      <LimeCC>0</LimeCC>
      <LimeCCPercent>0</LimeCCPercent>
      <PestControl>0</PestControl>
      <PestControlCC>0</PestControlCC>
      <PestControlCCPercent>0</PestControlCCPercent>
      <TotalPropertyCost>0</TotalPropertyCost>
      <TotalPropertyCostCC>0</TotalPropertyCostCC>
      <OtherPropertyCosts />
      <PropertyDepreciation>
        <CurrentYear>0</CurrentYear>
        <Expanded>false</Expanded>
        <Properties>
          <DepreciationBase>
            <Index>0</Index>
            <Description>House</Description>
            <PurchasedValue>10000</PurchasedValue>
            <PurchasedYear>2001</PurchasedYear>
            <DepnRatePercent>0.11</DepnRatePercent>
            <StartOfYearValue>6274.22</StartOfYearValue>
            <CurrentYearDepn>690.16</CurrentYearDepn>
            <EndOfYearValue>5584.06</EndOfYearValue>
            <CcDepnPercent>0.12</CcDepnPercent>
            <CurrentYearCCDepn>82.82</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>711.50</CcCurrentYearAverageCapital>
          </DepreciationBase>
          <DepreciationBase>
            <Index>1</Index>
            <Description>House 2</Description>
            <PurchasedValue>0</PurchasedValue>
            <PurchasedYear>0</PurchasedYear>
            <DepnRatePercent>0</DepnRatePercent>
            <StartOfYearValue>0</StartOfYearValue>
            <CurrentYearDepn>0</CurrentYearDepn>
            <EndOfYearValue>0</EndOfYearValue>
            <CcDepnPercent>0</CcDepnPercent>
            <CurrentYearCCDepn>0</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>0</CcCurrentYearAverageCapital>
          </DepreciationBase>
        </Properties>
      </PropertyDepreciation>
      <InsuranceCosts>0</InsuranceCosts>
      <InsuranceCostsCC>0</InsuranceCostsCC>
      <InsuranceCostsCCPercent>0</InsuranceCostsCCPercent>
      <ProfessionalFees>0</ProfessionalFees>
      <ProfessionalFeesCC>0</ProfessionalFeesCC>
      <ProfessionalFeesCCPercent>0</ProfessionalFeesCCPercent>
      <OtherAdminCosts>0</OtherAdminCosts>
      <OtherAdminCostsCC>0</OtherAdminCostsCC>
      <OtherAdminCostsCCPercent>0</OtherAdminCostsCCPercent>
      <Subscriptions>0</Subscriptions>
      <SubscriptionsCC>0</SubscriptionsCC>
      <SubscriptionsCCPercent>0</SubscriptionsCCPercent>
      <Miscellaneous>0</Miscellaneous>
      <MiscellaneousCC>0</MiscellaneousCC>
      <MiscellaneousCCPercent>0</MiscellaneousCCPercent>
      <SecretarialSupport>0</SecretarialSupport>
      <SecretarialSupportCC>0</SecretarialSupportCC>
      <SecretarialSupportCCPercent>0</SecretarialSupportCCPercent>
      <TotalAdminCost>0</TotalAdminCost>
      <TotalAdminCostCC>0</TotalAdminCostCC>
      <OtherAdministrationCosts />
      <AhaTenancies>0</AhaTenancies>
      <AhaTenanciesCC>0</AhaTenanciesCC>
      <AhaTenanciesCCPercent>0</AhaTenanciesCCPercent>
      <FarmTenancies>0</FarmTenancies>
      <FarmTenanciesCC>0</FarmTenanciesCC>
      <FarmTenanciesCCPercent>0</FarmTenanciesCCPercent>
      <NoRentArea>0</NoRentArea>
      <NoRentAreaRent>0</NoRentAreaRent>
      <NotionalRentCC>0</NotionalRentCC>
      <NotionalRentCCPercent>0</NotionalRentCCPercent>
      <TotalRentCost>0</TotalRentCost>
      <TotalRentCostCC>0</TotalRentCostCC>
      <OtherRentCosts />
      <OverdraftInterest>0</OverdraftInterest>
      <OverdraftInterestCC>0</OverdraftInterestCC>
      <OverdraftInterestCCPercent>0</OverdraftInterestCCPercent>
      <BankCharges>0</BankCharges>
      <BankChargesCC>0</BankChargesCC>
      <BankChargesCCPercent>0</BankChargesCCPercent>
      <LoanInterest>0</LoanInterest>
      <LoanInterestCC>0</LoanInterestCC>
      <LoanInterestCCPercent>0</LoanInterestCCPercent>
      <HpInterest>0</HpInterest>
      <HpInterestCC>0</HpInterestCC>
      <HpInterestCCPercent>0</HpInterestCCPercent>
      <TotalFinanceCostCC>0</TotalFinanceCostCC>
      <OtherFinanceCosts />
    </Overheads>
0
Comment
Question by:gbzhhu
11 Comments
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 170 total points
ID: 17818779
What value do you pass in from empty properties (e.g. OtherPropertyCosts)?

If you don't convert an empty string to numeric, you will probably get this error. populate with zero when this is missing.
0
 
LVL 2

Expert Comment

by:PErdu
ID: 17818802
you're trying to push a nvarchar value into a numeric field...
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17818834
PErdu

Error suggests that, but my data contains only numeric values

Nightman,

I am leaving empty properties empty, is this seen as string data?  I am putting default value for all column to 0 now and see what happens
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17819002
I have given default value of 0 to all columns and also changed the stored procedure for the columns that can be blank like this

In the select statement

OtherPropertyCosts = isnull(OtherPropertyCosts, 0)

instead of

OtherPropertyCosts

This has not solved the problem.

Other ideas please.

Thanks
H
0
 
LVL 9

Accepted Solution

by:
gpompe earned 330 total points
ID: 17820126
The problem is that you have empty tags like <OtherLabourCosts /> (there are more in the XML)  replaced by <OtherLabourCosts>0</OtherLabourCosts> and it worked

This is the XML I used:


<Benchmark>
      <FarmDetails>
            <Overheads>
                  <FullTimeLabour>0</FullTimeLabour>
                  <FullTimeLabourCC>0</FullTimeLabourCC>
                  <FullTimeLabourCCPercent>0</FullTimeLabourCCPercent>
                  <PartTimeLabour>0</PartTimeLabour>
                  <PartTimeLabourCC>0</PartTimeLabourCC>
                  <PartTimeLabourCCPercent>0</PartTimeLabourCCPercent>
                  <NumberOfPropriators>0</NumberOfPropriators>
                  <NotionalLabourCost>0</NotionalLabourCost>
                  <NotionalLabourCostCC>0</NotionalLabourCostCC>
                  <NotionalLabourCostCCPercent>0</NotionalLabourCostCCPercent>
                  <TotalLabourCost>0</TotalLabourCost>
                  <TotalLabourCostCC>0</TotalLabourCostCC>
                  <OtherLabourCosts>0</OtherLabourCosts>
                  <MachineryDepn>0</MachineryDepn>
                  <MachineryRepairs>0</MachineryRepairs>
                  <MachineryRepairsCC>0</MachineryRepairsCC>
                  <MachineryRepairsCCPercent>0</MachineryRepairsCCPercent>
                  <VehicleRepairs>0</VehicleRepairs>
                  <VehicleRepairsCC>0</VehicleRepairsCC>
                  <VehicleRepairsCCPercent>0</VehicleRepairsCCPercent>
                  <MachineryFuel>0</MachineryFuel>
                  <MachineryFuelCC>0</MachineryFuelCC>
                  <MachineryFuelCCPercent>0</MachineryFuelCCPercent>
                  <VehicleFuel>0</VehicleFuel>
                  <VehicleFuelCC>0</VehicleFuelCC>
                  <VehicleFuelCCPercent>0</VehicleFuelCCPercent>
                  <Haulage>0</Haulage>
                  <HaulageCC>0</HaulageCC>
                  <HaulageCCPercent>0</HaulageCCPercent>
                  <GrainStorage>0</GrainStorage>
                  <GrainStorageCC>0</GrainStorageCC>
                  <GrainStorageCCPercent>0</GrainStorageCCPercent>
                  <ElectricAndGas>0</ElectricAndGas>
                  <ElectricAndGasCC>0</ElectricAndGasCC>
                  <ElectricAndGasCCPercent>0</ElectricAndGasCCPercent>
                  <ContractStaff>0</ContractStaff>
                  <ContractStaffCC>0</ContractStaffCC>
                  <ContractStaffCCPercent>0</ContractStaffCCPercent>
                  <TotalMachineryCost>0</TotalMachineryCost>
                  <TotalMachineryCostCC>0</TotalMachineryCostCC>
                  <OtherMachineryCosts>0</OtherMachineryCosts>
                  <MachineryDepreciation>
                        <CurrentYear>0</CurrentYear>
                        <Expanded>false</Expanded>
                        <Machines>
                              <DepreciationBase>
                                    <Index>0</Index>
                                    <Description>Car 1</Description>
                                    <PurchasedValue>2000.00</PurchasedValue>
                                    <PurchasedYear>1998</PurchasedYear>
                                    <DepnRatePercent>0.2</DepnRatePercent>
                                    <StartOfYearValue>419.43</StartOfYearValue>
                                    <CurrentYearDepn>83.89</CurrentYearDepn>
                                    <EndOfYearValue>335.54</EndOfYearValue>
                                    <CcDepnPercent>0.1</CcDepnPercent>
                                    <CurrentYearCCDepn>8.39</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>37.75</CcCurrentYearAverageCapital>
                              </DepreciationBase>
                              <DepreciationBase>
                                    <Index>1</Index>
                                    <Description>Car 2</Description>
                                    <PurchasedValue>7800</PurchasedValue>
                                    <PurchasedYear>2003</PurchasedYear>
                                    <DepnRatePercent>0.12</DepnRatePercent>
                                    <StartOfYearValue>6040.32</StartOfYearValue>
                                    <CurrentYearDepn>724.84</CurrentYearDepn>
                                    <EndOfYearValue>5315.48</EndOfYearValue>
                                    <CcDepnPercent>0.04</CcDepnPercent>
                                    <CurrentYearCCDepn>28.99</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>227.12</CcCurrentYearAverageCapital>
                              </DepreciationBase>
                        </Machines>
                  </MachineryDepreciation>
                  <PropertyDepn>0</PropertyDepn>
                  <Repairs>0</Repairs>
                  <RepairsCC>0</RepairsCC>
                  <RepairsCCPercent>0</RepairsCCPercent>
                  <CouncilTax>0</CouncilTax>
                  <CouncilTaxCC>0</CouncilTaxCC>
                  <CouncilTaxCCPercent>0</CouncilTaxCCPercent>
                  <WaterRates>0</WaterRates>
                  <WaterRatesCC>0</WaterRatesCC>
                  <WaterRatesCCPercent>0</WaterRatesCCPercent>
                  <Drainage>0</Drainage>
                  <DrainageCC>0</DrainageCC>
                  <DrainageCCPercent>0</DrainageCCPercent>
                  <Lime>0</Lime>
                  <LimeCC>0</LimeCC>
                  <LimeCCPercent>0</LimeCCPercent>
                  <PestControl>0</PestControl>
                  <PestControlCC>0</PestControlCC>
                  <PestControlCCPercent>0</PestControlCCPercent>
                  <TotalPropertyCost>0</TotalPropertyCost>
                  <TotalPropertyCostCC>0</TotalPropertyCostCC>
                  <OtherPropertyCosts>0</OtherPropertyCosts>
                  <PropertyDepreciation>
                        <CurrentYear>0</CurrentYear>
                        <Expanded>false</Expanded>
                        <Properties>
                              <DepreciationBase>
                                    <Index>0</Index>
                                    <Description>House</Description>
                                    <PurchasedValue>10000</PurchasedValue>
                                    <PurchasedYear>2001</PurchasedYear>
                                    <DepnRatePercent>0.11</DepnRatePercent>
                                    <StartOfYearValue>6274.22</StartOfYearValue>
                                    <CurrentYearDepn>690.16</CurrentYearDepn>
                                    <EndOfYearValue>5584.06</EndOfYearValue>
                                    <CcDepnPercent>0.12</CcDepnPercent>
                                    <CurrentYearCCDepn>82.82</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>711.50</CcCurrentYearAverageCapital>
                              </DepreciationBase>
                              <DepreciationBase>
                                    <Index>1</Index>
                                    <Description>House 2</Description>
                                    <PurchasedValue>0</PurchasedValue>
                                    <PurchasedYear>0</PurchasedYear>
                                    <DepnRatePercent>0</DepnRatePercent>
                                    <StartOfYearValue>0</StartOfYearValue>
                                    <CurrentYearDepn>0</CurrentYearDepn>
                                    <EndOfYearValue>0</EndOfYearValue>
                                    <CcDepnPercent>0</CcDepnPercent>
                                    <CurrentYearCCDepn>0</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>0</CcCurrentYearAverageCapital>
                              </DepreciationBase>
                        </Properties>
                  </PropertyDepreciation>
                  <InsuranceCosts>0</InsuranceCosts>
                  <InsuranceCostsCC>0</InsuranceCostsCC>
                  <InsuranceCostsCCPercent>0</InsuranceCostsCCPercent>
                  <ProfessionalFees>0</ProfessionalFees>
                  <ProfessionalFeesCC>0</ProfessionalFeesCC>
                  <ProfessionalFeesCCPercent>0</ProfessionalFeesCCPercent>
                  <OtherAdminCosts>0</OtherAdminCosts>
                  <OtherAdminCostsCC>0</OtherAdminCostsCC>
                  <OtherAdminCostsCCPercent>0</OtherAdminCostsCCPercent>
                  <Subscriptions>0</Subscriptions>
                  <SubscriptionsCC>0</SubscriptionsCC>
                  <SubscriptionsCCPercent>0</SubscriptionsCCPercent>
                  <Miscellaneous>0</Miscellaneous>
                  <MiscellaneousCC>0</MiscellaneousCC>
                  <MiscellaneousCCPercent>0</MiscellaneousCCPercent>
                  <SecretarialSupport>0</SecretarialSupport>
                  <SecretarialSupportCC>0</SecretarialSupportCC>
                  <SecretarialSupportCCPercent>0</SecretarialSupportCCPercent>
                  <TotalAdminCost>0</TotalAdminCost>
                  <TotalAdminCostCC>0</TotalAdminCostCC>
                  <OtherAdministrationCosts>0</OtherAdministrationCosts>
                  <AhaTenancies>0</AhaTenancies>
                  <AhaTenanciesCC>0</AhaTenanciesCC>
                  <AhaTenanciesCCPercent>0</AhaTenanciesCCPercent>
                  <FarmTenancies>0</FarmTenancies>
                  <FarmTenanciesCC>0</FarmTenanciesCC>
                  <FarmTenanciesCCPercent>0</FarmTenanciesCCPercent>
                  <NoRentArea>0</NoRentArea>
                  <NoRentAreaRent>0</NoRentAreaRent>
                  <NotionalRentCC>0</NotionalRentCC>
                  <NotionalRentCCPercent>0</NotionalRentCCPercent>
                  <TotalRentCost>0</TotalRentCost>
                  <TotalRentCostCC>0</TotalRentCostCC>
                  <OtherRentCosts>0</OtherRentCosts>
                  <OverdraftInterest>0</OverdraftInterest>
                  <OverdraftInterestCC>0</OverdraftInterestCC>
                  <OverdraftInterestCCPercent>0</OverdraftInterestCCPercent>
                  <BankCharges>0</BankCharges>
                  <BankChargesCC>0</BankChargesCC>
                  <BankChargesCCPercent>0</BankChargesCCPercent>
                  <LoanInterest>0</LoanInterest>
                  <LoanInterestCC>0</LoanInterestCC>
                  <LoanInterestCCPercent>0</LoanInterestCCPercent>
                  <HpInterest>0</HpInterest>
                  <HpInterestCC>0</HpInterestCC>
                  <HpInterestCCPercent>0</HpInterestCCPercent>
                  <TotalFinanceCostCC>0</TotalFinanceCostCC>
                  <OtherFinanceCosts>0</OtherFinanceCosts>
            </Overheads>
      </FarmDetails>
</Benchmark>
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 12

Author Comment

by:gbzhhu
ID: 17820297
Ok,  This was suggested by Nightman earlier.  I haven't modified the XML, I tried the modify the stored procedure which didn't help.  I will try to modify m,y program that generates the XML to insert 0 if no values exist for an element

Thanks
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17820362
gpompe,

Yep, that worked.  It was a big mistake on my part.  Elements OtherLabourCosts, OtherMachineryCosts, ... OtherxxxxxxCosts are arrays!!!  They didn't have array elements (0 lenght).  I removed then from the stored procedure code.  I will have to deal with them separately, maybe write them to a seperate table and associate them with the bm_overheads table

Thank you for help.  Since Nightman suggested this before you but it didn't direct me to a solution, I will divide points between you 2.  2/3 to you and 1/3 to him

Thank you again both of you.

H
0
 
LVL 9

Expert Comment

by:gpompe
ID: 17820447
In that case what you can do is read the XML as varchar and convert the values in the select clause

For instance

FullTimeLabour      Varchar(20)                                   'FullTimeLabour',

and in the select clause something like

convert(numeric(15,2),case when ISNUMERIC(FullTimeLabour)=1 then FullTimeLabour else '0' end)

because FullTimeLabour is returned as empty string.

I know is a lot of writing, but it is a solution
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17820566
gpompe,

Maybe you didn't understand what I meant.  All elements are numeric data except few that are named like OtherxxxCosts (where xxx = Labour or Machinery or Property etc).  These elements represent an array in my C# code.  When the array has values the XML portion that represents them looks like this

      <OtherLabourCosts>
        <OtherLabourCost">
          <CostName>    One</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherLabourCost>
        <OtherLabourCost">
          <CostName>    Two</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherLabourCost>
      </OtherLabourCosts>

      <OtherPropertyCosts>
        <OtherPropertyCost">
          <CostName>    House</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherPropertyCost>
      </OtherPropertyCosts>

They all have the same structure.  The only way I can think of dealing with them is to write them all to one table which will have cost type (so I can tell if I am dealing with Labour costs or property costs etc) and ID that associates it with the parent row (bm_overheads) and the actual data columns.  I think that should work.  If you can think of an improvement/better soluton please let me know.

Thanks, your input is much appreciated
H



0
 
LVL 9

Expert Comment

by:gpompe
ID: 17820767
I understand, but your problem is in SQL SERVER.

This the thing : When SQL SERVER reads the XML is trying to convert what it reads to Numeric(15,2) because you defined that.
So in this line, SQL SERVER reads an empty tag it fail to convert (implicit) it to numeric and that the error you get.

Now my approach is to overcome the problem with the implicit convertion is read the XML into varchar field (does not matter if there is a number or not).
So now you can read the XML, but the fields in your table (bm_overheads) are numeric. So you have to convert them.

If we try to convert them as is, it will fail again. To overcome this I am using the ISNUMERIC function (it receives a varchar and returns 1 if the value can be convert to a numeric value)

case when ISNUMERIC(FullTimeLabour)=1 then FullTimeLabour else '0' end means if SQL can convert the value of the tag into numeric value, do it. Else the value is 0

The convert function is to explicitly convert the value, but you can omitt it and the values will be converted during the insert.

With this approach you do not have to modify the XML, just Insert query and final result is the same.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17820845
OK, I am with you.  However, I removed these columns from the table as I cannot represent an array data in one column.  I also removed them from the select in the stored procedure.  This works fine now.  It doesn't deal with these fields.  I am the thinking of writing another insert into another table that holds these each array element in a row.  will this work?

If I follow your suggestion, I believe the insert into bm_overheads will work ok.  bm_overheads originally had 1 field for each array which was a mistake.  It is not possible to insert the arrays into bm_overheads as I don't know how many array elements there will be at runtime.  I will have to deal with the array data insert seperate from single item data insert into bm_overheads.  I am not SQL savvy, so i maybe lost.

Cheers
H
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now