locked
Copy Activity on Stored Procedure with TVP: Arithmetic overflow error converting numeric to data type numeric. RRS feed

  • Question

  • Hi, I have 2 Azure SQL databases that I'm transferring data between using and ADF pipeline calling into a StoredProc with a Table Valued Parameter and there's something going wrong.

    Both tables have a number of decimal(5,2) columns. When processing a slice execution fails with the message: "Database operation failed. Error message from database execution : Arithmetic overflow error converting numeric to data type numeric.
    The statement has been terminated.."

    All the columns in question have a decimal(5,2) data type, the datasets used to transport these use a Decimal column in the table Schema.

    As I suspected the data was at issue I updated all of the decimal columns in the source to have value 0 after which the slices processed correctly. Given this I suspect some form of rounding / floating point issue while the data is in motion causing the overflow when writing into the table.

    Since then I've blown away all the data in both the source and destination and re-created the source data and set the batch size to two to try and identify the rows causing issue and the first set of rows where it fails there is a data value of 16.20 in a decimal(5,2) column, which should work just fine as far as I can tell.

    Friday, July 24, 2015 5:43 AM

Answers

  • Hi Gavin,

    For the error message "Arithmetic overflow error converting numeric to data type numeric", it mostly means there is a value larger than the decimal(5,2) type definition, namely larger than 999.99. (refer to this post for the same error). You can try changing the type to decimal(10,2) to see what values would be copied.

    If such issue still reproed by changing the type, it would be helpful if you can further provide the definition of source table schema, table type schema, stored procedure, and the target table schema.

    Thanks,

    Yingqin

    Friday, July 24, 2015 6:49 AM
  • using

    SELECTMAX([Morning] )

    ,MAX([MidDay] )

    ,MAX([DinnerTime])

    ,MAX([BedTime] )

    ,MAX([Additional])

    FROM[dbo].[destination]

    GO

    SELECTid,[Morning]

    ,[MidDay]

    ,[DinnerTime]

    ,[BedTime]

    ,[Additional]

    FROM[dbo].[destination]

    WHEREMORNING =999.99

    ORDINNERTIME =999.99

    GO

    I've found a single row that has 999.99 values for some of the decimal columns

    Looks like I just need to expand my decimal columns by a single point of scale to ensure that data will migrate.

    Friday, July 24, 2015 9:39 AM

All replies

  • runid = d7076340-4d3a-443e-a2a6-a9260749cc7e_635733117000000000_635733126000000000_ProfileMedicationTarget

    Run started at = ‎07‎/‎24‎/‎2015‎ ‎05‎:‎40‎:‎29‎ ‎AM UTC

    Friday, July 24, 2015 5:51 AM
  • Hi Gavin,

    For the error message "Arithmetic overflow error converting numeric to data type numeric", it mostly means there is a value larger than the decimal(5,2) type definition, namely larger than 999.99. (refer to this post for the same error). You can try changing the type to decimal(10,2) to see what values would be copied.

    If such issue still reproed by changing the type, it would be helpful if you can further provide the definition of source table schema, table type schema, stored procedure, and the target table schema.

    Thanks,

    Yingqin

    Friday, July 24, 2015 6:49 AM
  • Thanks for the reply. All the columns in question are declared as Decimal(5,2) so I don't understand how any of these columns could contain a figure greater than 999.99.

    Furthermore I've found the rows that appear to be the issue by running in batches of 2 and looking at the first two rows not to have been migrated and after selecting them the highest value of those columns in question is 16.20, well under 999.99. Also when running a MAX() over all of the decimal columns the highest value found is 500.

    Nonetheless,

    Source table:

    CREATETABLE[dbo].[source](

    [id] [int] IDENTITY(100108,1)NOTNULL,

    [UserId] [int] NOTNULLDEFAULT ((0)),

    [Name] [varchar](60)NOTNULLDEFAULT (N'0'),

    [Morning] [decimal](5,2)NULLDEFAULT (NULL),

    [MidDay] [decimal](5,2)NULLDEFAULT (NULL),

    [DinnerTime] [decimal](5,2)NULLDEFAULT (NULL),

    [BedTime] [decimal](5,2)NULLDEFAULT (NULL),

    [Additional] [decimal](5,2)NULLDEFAULT (NULL),

    [OtherTimesId] [int] NOTNULLDEFAULT ((0)),

    [ClientID] [int] NULLDEFAULT ((1)),

    [ChangedAt] [datetime2](0)NULLDEFAULT ('1900-01-01 00:00:00'),

    [Class] [int] NULLDEFAULT ((1)),

     CONSTRAINT[PK_source_id] PRIMARYKEYCLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON),

    Friday, July 24, 2015 8:08 AM
  • Destination:

    CREATETABLE[dbo].[destination](

    [id] [int] NOTNULL,

    [UserId] [int] NOTNULLDEFAULT ('0'),

    [Name] [varchar](60)NOTNULLDEFAULT ('0'),

    [Morning] [decimal](5,2)NULLDEFAULT (NULL),

    [MidDay] [decimal](5,2)NULLDEFAULT (NULL),

    [DinnerTime] [decimal](5,2)NULLDEFAULT (NULL),

    [BedTime] [decimal](5,2)NULLDEFAULT (NULL),

    [Additional] [decimal](5,2)NULLDEFAULT (NULL),

    [OtherTimesId] [int] NOTNULLDEFAULT ('0'),

    [ClientID] [int] NULLDEFAULT ('1'),

    [ChangedAt] [datetime] NULLDEFAULT ('1900-01-01 00:00:00'),

    [Class] [int] NULLDEFAULT ('1')

    PRIMARYKEYCLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)

    )

    Friday, July 24, 2015 8:09 AM
  • TableType for StoredProc

    CREATETYPE[dbo].[InboundType] ASTABLE

    (

    [id] intNOTNULLPRIMARYKEY,

    [UserId] intNOTNULLdefault'0',

    [Name] varchar(60)NOTNULLdefault'0',

    [Morning] decimal(5,2)defaultNULL,

    [MidDay] decimal(5,2)defaultNULL,

    [DinnerTime] decimal(5,2)defaultNULL,

    [BedTime] decimal(5,2)defaultNULL,

    [Additional] decimal(5,2)defaultNULL,

    [OtherTimesId] intNOTNULLdefault'0',

    [ClientID] intdefault'1',

    [ChangedAt] datetimedefault'1900-01-01 00:00:00',

    [Class] intdefault'1'

    )

    Friday, July 24, 2015 8:09 AM
  • Stored Proc:

    CREATEPROCEDURE[dbo].[MigrateDestination]

    @inbound [dbo].[InboundType] READONLY

    AS

    INSERTINTO[dbo].[destination]([id],

    [UserId],

    [Name],

    [Morning],

    [MidDay],

    [DinnerTime],

    [BedTime],

    [Additional],

    [OtherTimesId],

    [ClientID],

    [ChangedAt],

    [Class],

    [Type])

    SELECTm.[id],

    m.[UserId],

    m.[Name],

    m.[Morning],

    m.[MidDay],

    m.[DinnerTime],

    m.[BedTime],

    m.[Additional],

    m.[OtherTimesId],

    m.[ClientID],

    m.[ChangedAt],

    m.[Class],

    'MigratedFromRemote'FROM@inbound m

    WHEREm.id NOTIN(SELECTlocal.id

    FROM[dbo].[destination] local)

    RETURN0

    Friday, July 24, 2015 8:09 AM
  • Hi Gavin,

    Thanks for providing the detailed information. But it doesn't match with the information in your provided runId "d7076340-4d3a-443e-a2a6-a9260749cc7e", which use the source table "Medicne_Profile_Seizure" and should contain 25 columns. Do you have another run with your provided information? If yes, could you further provide that runId?

    BTW, in the stored procedure "MigrateDestination", it inserts column "Type" to the target table "destination", but the table "destination" doesn't contain a column "Type". Is it correct?

    Friday, July 24, 2015 8:34 AM
  • I trimmed out a bunch of varchar and date columns due to post size restrictions.

    The destination table does contain a type column, I'd using this process to collapse a bunch of tables with the same schema into a single table with a type column.

    After expanding the Destination table the data is migrating cleanly but I'm still puzzled as to why I would need to do that given both tables have the same dimensions defined for all of the decimal tables.

    Here's another failed runid: b52c1224-7bba-482b-83b0-de1f66c801a9_635732433000000000_635732442000000000_ProfileMedicationTarget

    Friday, July 24, 2015 8:59 AM
  • Oh and I only changed the column definitions for the decimals on the target table and not for the TableType at all.
    Friday, July 24, 2015 9:18 AM
  • As you can make it work by changing the type, do you observe any abnormal data in the target table?

    I have tried copy the data "16.20" with decimal(5,2), and it can work as expected.

    Friday, July 24, 2015 9:20 AM
  • using

    SELECTMAX([Morning] )

    ,MAX([MidDay] )

    ,MAX([DinnerTime])

    ,MAX([BedTime] )

    ,MAX([Additional])

    FROM[dbo].[destination]

    GO

    SELECTid,[Morning]

    ,[MidDay]

    ,[DinnerTime]

    ,[BedTime]

    ,[Additional]

    FROM[dbo].[destination]

    WHEREMORNING =999.99

    ORDINNERTIME =999.99

    GO

    I've found a single row that has 999.99 values for some of the decimal columns

    Looks like I just need to expand my decimal columns by a single point of scale to ensure that data will migrate.

    Friday, July 24, 2015 9:39 AM