August 17, 2017 at 4:00 am
Hi SQLExperts,
I work in a data warehouse environment and I deal a lot of data related issues on day to day basis.
So, in the process I wanted to know is there a better way to fix below data related issue. This is just one of the data related issue.
I have a source table and a destination table. However, for simplicity sake I am just using a source sql query to load data into a table.
I wanted to know is there a better way to figure out which column and what value in the column is causing the error.
I have a table called "test_tbl" and the problematic column c2 whose varchar(5). When I am trying to load data using the select qry it throws are error.
I am discussing 2 scenario. Scenario 1 is success scenario and Scenario 2 is the failure scenario and the one which I need help figuring out 2 things.
1. Which row is causing the problem.
2. which column & column valumn is causing the error.
Sample data
create table test_tbl
( c1 int,
c2 varchar(5), ---- problematic column
c3 int,
c4 int,
c5 varchar(10)
)
go
scenario 1 : success run and works perfectly
truncate table test_tbl
insert into test_tbl
select 1 as c1,10 as c2,1 as c3,1 as c4,1 as c5
union all
select 2,12,2,2,2
union all
select 3,777,3,3,3
union all
select 4,83.1,4,4,4
go
select * From test_tbl
scenario 2 : FAILURE run . this is the one I need help in building a tsql script/xevent which can tell us which row and which column is causing the issue.
truncate table t2
insert into t2
select 1 as c1,10 as c2,1 as c3,1 as c4,1 as c5
union all
select 2,12,2,2,2
union all
select 3,777,3,3,3
union all
select 4,83.1,4,4,4
union all
select 5,99.999,5,5,5 ------ >>>> /// problematic row and c2 value is having the issue.
go
Error message:
Msg 8115, Level 16, State 5, Line 73
Arithmetic overflow error converting numeric to data type varchar.
The statement has been terminated.
select * from t2
I tried using a Extended events but the problem I am not able figure out the exact row causing the issue. Had it been above select is individual INSERT Statement then my
extended event session would tell me that so and so row caused the Error. However, I am looking for a alternative way (if any) using tsql which will check the max size of the column
and cross check the data and tell me/output so and so row is the one causing the error and so and so column value was having the issue.
Below is my extended event code:
use master
go
DROP EVENT SESSION [ArithmeticOverflowError] ON SERVER
GO
create event session ArithmeticOverflowError
on server
add event sqlserver.error_reported
(
action
(
sqlserver.sql_text,
sqlserver.database_name
)
where
( error_number = 8115 --- filter on errorno
)
)
add target package0.event_file
(
SET FILENAME = N'C:\xevents\ArithmeticOverflowError.xel', -- CHECK that these are cleared
METADATAFILE = N'C:\xevents\ArithmeticOverflowError.xem'
);
alter event session ArithmeticOverflowError
on server
state = start;
go
--- analysis queries
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('C:\xevents\ArithmeticOverflowError*.xel',null, null, null)
-- Query the Event data from the Target.
-- Query the Event data from the Target.
SELECT
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
--n.value('(@id)[1]', 'int') AS id,
--n.value('(@version)[1]', 'int') AS version,
-- DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(@timestamp)[1]', 'datetime2')) AS [EventTime],
n.value('(data[@name="error_number"]/value)[1]', 'int') as error,
n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
n.value('(data[@name="state"]/value)[1]', 'varchar(max)') as state_number,
n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as [message],
n.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text],
n.value('(./action[@name="database_name"]/value)[1]', 'varchar(max)') as [database_name]
into master..ArithmeticOverflowTruncationErrors
FROM
(SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\xevents\ArithmeticOverflowError*.xel', null, null, null)
) as tab
CROSS APPLY event_data.nodes('event') as q(n)
go
select * from master..ArithmeticOverflowTruncationErrors
go
DROP EVENT SESSION [ArithmeticOverflowError] ON SERVER
GO
Thanks in Advance.
August 17, 2017 at 4:42 am
Does t2 have the same structure as test_tbl? If so, why are you trying to insert the value "99.999" into a varchar(5) column. And why is the column varchar at all, given that all your sample data is numeric?
Edit - I've just re-read your post, and I see that this data is just to illustrate your point. One way for you to proceed is to put the data into a staging table where all the columns are varchar(4000), or some other type that you know all your values will fit into. You can then analyse the data before inserting into the destination.
John
August 17, 2017 at 5:52 am
1) TRY_CONVERT could help
2) SSIS has a Data Profiling tool that could help
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 17, 2017 at 6:59 am
scenario 2 is a deliberate scenario and using some tsql method I wanted to check what rows and what column is causing the issue.
August 17, 2017 at 9:38 am
August 17, 2017 at 10:21 am
Have you looked at SSIS? If one row of data from table1 does not “fit” into table2 for whatever reason, you can redirect just the one error row, along with an error message into an error table, allowing the rest of the data to load.
The following might help.
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/error-handling-in-data
If you do end up creating an error table I would recommend making one generic “error table” otherwise you will end up with tables all over the place.
I also think John’s comment above is a good one, validating/checking is better than trying to deal with an error.
August 17, 2017 at 1:14 pm
Hi Joe Torre,
Thanks for the suggestion. I tried below but couldnt able to get the errornous record / errornous column getting logged.
Can anyone help tweaking the below code so that I can get the error row / error column / error column value causing the error.
Appreciate if someone can help here tweaking below piece of code.
CREATE TABLE [dbo].[src_tbl](
[c1] [int] NOT NULL,
[c2] [numeric](6, 3) NOT NULL,
[c3] [int] NOT NULL,
[c4] [int] NOT NULL,
[c5] [int] NOT NULL
)
GO
insert into src_tbl
select *
from
(
select 1 as c1,10 as c2,1 as c3,1 as c4,1 as c5
union all
select 2,12,2,2,2
union all
select 3,777,3,3,3
union all
select 4,83.1,4,4,4
union all
select 5,99.999,5,5,5
) as a
select * from src_tbl
create table trg_tbl
( c1 int,
c2 decimal(4,3), ---- problematic column
c3 int,
c4 int,
c5 varchar(10)
)
go
-------------------- Error Handling -----------------------------------
-- Error logging table
IF OBJECT_ID('[dbo].[MyErrorLog]') IS NOT NULL
BEGIN
DROP TABLE [dbo].[MyErrorLog];
END
GO
CREATE TABLE [dbo].[MyErrorLog]
(
[MyErrorLog_ID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[ERROR_DATE] DATETIME2 DEFAULT SYSDATETIME(),
[ERROR_NUMBER] INT NULL,
[ERROR_MESSAGE] NVARCHAR(4000) NULL,
[ERROR_SEVERITY] INT NULL,
[ERROR_STATE] INT NULL,
[ERROR_LINE] INT NULL,
[ERROR_PROCEDURE] NVARCHAR(128) NULL
);
GO
----------------------------------------------------------------------------------
-- The batch with error
BEGIN TRY
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO trg_tbl
SELECT * FROM src_tbl
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT '1'
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
INSERT INTO [dbo].[MyErrorLog]
([ERROR_NUMBER],
[ERROR_MESSAGE],
[ERROR_SEVERITY],
[ERROR_STATE],
[ERROR_LINE],
[ERROR_PROCEDURE])
SELECT ERROR_NUMBER() AS [Error_Number],
ERROR_MESSAGE() AS [Error_Message],
ERROR_SEVERITY() AS [Error_Severity],
ERROR_STATE() AS [Error_State],
ERROR_LINE() AS [Error_Line],
ERROR_PROCEDURE() AS [Error_Procedure];
--THROW;
END CATCH
GO
----------------------------------------------------
-- check error log contents
SELECT [MyErrorLog_ID],
[ERROR_DATE],
[ERROR_NUMBER],
[ERROR_MESSAGE],
[ERROR_SEVERITY],
[ERROR_STATE],
[ERROR_LINE],
[ERROR_PROCEDURE]
FROM [dbo].[MyErrorLog];
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply