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