SQL Query/JOB Error

  • Hi all,

    Need advise for my error

    Executed as user: NT AUTHORITY\NETWORK SERVICE. Arithmetic overflow error converting IDENTITY to data type int. [SQLSTATE 22003] (Error 8115) Arithmetic overflow occurred. [SQLSTATE 01000] (Error 3606). The step failed.

    Need Help and advise.

    Thank you and Regards,

    GNA

  • gunadi.arunanto (11/13/2016)


    Hi all,

    Need advise for my error

    Executed as user: NT AUTHORITY\NETWORK SERVICE. Arithmetic overflow error converting IDENTITY to data type int. [SQLSTATE 22003] (Error 8115) Arithmetic overflow occurred. [SQLSTATE 01000] (Error 3606). The step failed.

    Need Help and advise.

    Thank you and Regards,

    GNA

    Hi and welcome to the forum. We will need more information in order to help you on this one. Can you please post all relevant information such as DDL (create table) scripts, the details of the job that is failing including the job steps commands etc.?

    😎

  • Hi Eirikur,

    thanks for the quick response.

    below is a script that I run :

    USE [msdb]

    GO

    /****** Object: Job [MAP_DAY] Script Date: 11/14/2016 09:55:17 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 11/14/2016 09:55:17 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MAP_DAY',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'ASIA\casury_admin', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [delete today MAP_DAY] Script Date: 11/14/2016 09:55:17 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'delete today MAP_DAY',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'delete from MAP_HOUR

    where CONVERT(VARCHAR(10),DATE,120)=CONVERT(VARCHAR(10),getdate(),120)

    ;

    delete from MAP_DAY

    where CONVERT(VARCHAR(10),DATE,120)=CONVERT(VARCHAR(10),getdate(),120)

    ;

    delete from MAP_MONTH

    where DATE>= DATEADD(dd, -1* DAY(GETDATE()), DATE)

    ;',

    @database_name=N'genesys_datamart',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [insert today MAP_DAY] Script Date: 11/14/2016 09:55:17 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'insert today MAP_DAY',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'insert MAP_HOUR (DATE,DATE_TIME_KEY, AGENT,GROUP_AGENT,CAMPAIGN,FIRSTCALL,LASTCALL, NO_OF_CALLS )

    select

    DATE,

    SUBSTRING(DATE, 7,4) + SUBSTRING(DATE, 1,2) + SUBSTRING(DATE, 4,2) + LEFT(BeginTime, 2) AS DATE_TIME_KEY,

    AGENT,GROUPAGENT,CAMPAIGN,

    MIN(BEGINTIME) FIRSTCALL,MAX(BEGINTIME) LASTCALL, COUNT(DATE) NO_OF_CALLS

    from MAP

    where DATE=CONVERT(VARCHAR(10),getdate(),101)

    GROUP BY DATE, LEFT(BEGINTIME,2), AGENT,GROUPAGENT,CAMPAIGN

    ;

    insert MAP_DAY (DATE,DATE_TIME_KEY, AGENT,GROUP_AGENT,CAMPAIGN,FIRSTCALL,LASTCALL, NO_OF_CALLS )

    select

    SUBSTRING(DATE, 7,4) + SUBSTRING(DATE, 1,2) + SUBSTRING(DATE, 4,2) AS DATE,

    SUBSTRING(DATE, 7,4) + SUBSTRING(DATE, 1,2) + SUBSTRING(DATE, 4,2) AS DATE_TIME_KEY,

    AGENT,GROUPAGENT,CAMPAIGN,

    MIN(BEGINTIME) FIRSTCALL,MAX(BEGINTIME) LASTCALL, COUNT(DATE) NO_OF_CALLS

    from MAP

    where DATE=CONVERT(VARCHAR(10),getdate(),101)

    GROUP BY DATE,AGENT,GROUPAGENT,CAMPAIGN

    ;

    insert MAP_MONTH (DATE,DATE_TIME_KEY, AGENT,GROUP_AGENT,CAMPAIGN,FIRSTCALL,LASTCALL, NO_OF_CALLS )

    select

    DATEADD(dd, -1* DAY(GETDATE())+1, DATE),

    LEFT(DATE_TIME_KEY, 6) DATE_TIME_KEY,

    AGENT, GROUP_AGENT, CAMPAIGN, MIN(FIRSTCALL), MAX(LASTCALL), SUM(NO_OF_CALLS)

    from MAP_DAY

    where DATE>= DATEADD(dd, -1* DAY(GETDATE())+1, DATE)

    GROUP BY DATEADD(dd, -1* DAY(GETDATE())+1, DATE), LEFT(DATE_TIME_KEY, 6), AGENT,GROUP_AGENT,CAMPAIGN

    ;

    ',

    @database_name=N'genesys_datamart',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'hourly_job',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=8,

    @freq_subday_interval=1,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20141028,

    @active_end_date=99991231,

    @active_start_time=83000,

    @active_end_time=200000,

    @schedule_uid=N'24bf9502-abf7-4939-9470-a457cd4ceb16'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

    Best Regards,

    Gunadi

  • Can you post the DDL (create table) scripts for MAP, MAP_DAY and MAP_MONTH please?

    😎

  • Hi Eirikur,

    from the script that I tell whether it could be seen where there are errors?

    Thank you.

    Rgds,

    Gunadi

  • Hi All.

    Anyone can help for this.... Thank you....

  • Script the tables in question as shown in the picture and the post the generated scripts here

    😎

  • Hi Eirikur,

    Script Table from MAP_DAY?

  • Hi Eirikur,

    fya

  • Hi Eirikur,

    Sorry wrong file, i re attached file for DDL.

    USE [genesys_datamart]

    GO

    /****** Object: Table [dbo].[MAP_MONTH] Script Date: 11/15/2016 17:58:19 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[MAP_MONTH](

    [SYS_ID] [int] IDENTITY(1,1) NOT NULL,

    [DATE] [datetime] NULL,

    [DATE_TIME_KEY] [varchar](10) NULL,

    [AGENT] [nvarchar](50) NULL,

    [GROUP_AGENT] [nvarchar](50) NULL,

    [CAMPAIGN] [nvarchar](50) NULL,

    [FIRSTCALL] [varchar](50) NULL,

    [LASTCALL] [varchar](50) NULL,

    [NO_OF_CALLS] [int] NULL,

    CONSTRAINT [PK_MAP_MONTH] PRIMARY KEY CLUSTERED

    (

    [SYS_ID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[MAP_HOUR] Script Date: 11/15/2016 17:58:19 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[MAP_HOUR](

    [SYS_ID] [int] IDENTITY(1,1) NOT NULL,

    [DATE] [datetime] NULL,

    [DATE_TIME_KEY] [varchar](10) NULL,

    [AGENT] [nvarchar](50) NULL,

    [GROUP_AGENT] [nvarchar](50) NULL,

    [CAMPAIGN] [nvarchar](50) NULL,

    [FIRSTCALL] [varchar](50) NULL,

    [LASTCALL] [varchar](50) NULL,

    [NO_OF_CALLS] [int] NULL,

    CONSTRAINT [PK_MAP_HOUR] PRIMARY KEY CLUSTERED

    (

    [SYS_ID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[MAP_DAY] Script Date: 11/15/2016 17:58:19 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[MAP_DAY](

    [SYS_ID] [int] IDENTITY(1,1) NOT NULL,

    [DATE] [datetime] NULL,

    [DATE_TIME_KEY] [nvarchar](50) NULL,

    [AGENT] [nvarchar](50) NULL,

    [GROUP_AGENT] [nvarchar](50) NULL,

    [CAMPAIGN] [nvarchar](50) NULL,

    [FIRSTCALL] [varchar](50) NULL,

    [LASTCALL] [varchar](50) NULL,

    [NO_OF_CALLS] [int] NULL,

    CONSTRAINT [PK_MAP_DAY] PRIMARY KEY CLUSTERED

    (

    [SYS_ID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[MAP] Script Date: 11/15/2016 17:58:19 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[MAP](

    [ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL,

    [Agent] [varchar](50) NULL,

    [GroupAgent] [varchar](50) NULL,

    [Campaign] [varchar](50) NULL,

    [Sponsor] [varchar](50) NULL,

    [Phone] [varchar](50) NULL,

    [BeginTime] [varchar](50) NULL,

    [Date] [varchar](50) NULL,

    CONSTRAINT [PK_MAP] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

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

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Rgds,

    Gunadi

  • From what I can see, you will have to change the data type of the ID column in the MAP table to INT, currently it is NUMERIC(9,0) (999999999) which is far less than the maximum INT value (2147483647).

    😎

    Consider this

    DECLARE @INT INT = 2147483647;

    DECLARE @NUMERIC NUMERIC(9, 0) = 0;

    BEGIN TRY

    SET @NUMERIC = @INT

    END TRY

    BEGIN CATCH

    SELECT

    ERROR_NUMBER() AS ERR_NO

    ,ERROR_MESSAGE() AS ERR_MSG

    END CATCH

    ;

    Output

    ERR_NO ERR_MSG

    ----------- --------------------------------------------------------------

    8115 Arithmetic overflow error converting int to data type numeric.

  • so i must change from INT to BigINT?

  • or wich part can i change INT?

  • gunadi.arunanto (11/15/2016)


    or wich part can i change INT?

    Before changing anything, what is the output of this query?

    😎

    SELECT

    'dbo.MAP_MONTH' AS TABLE_NAME

    ,MAX(MM.SYS_ID) AS MAX_VAL

    FROM dbo.MAP_MONTH MM

    UNION ALL

    SELECT

    'dbo.MAP_HOUR' AS TABLE_NAME

    ,MAX(MM.SYS_ID) AS MAX_VAL

    FROM dbo.MAP_HOUR MM

    UNION ALL

    SELECT

    'dbo.MAP_DAY' AS TABLE_NAME

    ,MAX(MM.SYS_ID) AS MAX_VAL

    FROM dbo.MAP_DAY MM

    UNION ALL

    SELECT

    'dbo.MAP' AS TABLE_NAME

    ,MAX(MM.ID) AS MAX_VAL

    FROM dbo.MAP MM;

  • :Wow: Thanks...... let me try

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply