Getting errors from a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting errors from a table

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-13 : 23:26:02

Guys, I have a major problem! I have these table which I import from a flat file to SQL table in SQL database. However, my job is to convert the datatypes that is coming in from a different format to the one that my DBA has already assigned. Problem is, I have to point out 4 possibilities of errors(if any), and then send it to another table called Success_Failure. If there is no errors, it should send back the file "Success" to the sender which I have done. I am only stuck with the error part only.

I have to check about 50 columns on each 4 tables and return any errors to the Success_Failure table

The 4 errors that I must find are:
1. Missing Data(Nulls where it should be Not Null)
2.Invalid Datatype(varchar(50) when it suppose to be numeric38, 0)
3.Length(75 characters when it should only be 50)
4.Invalid date(1986 instead of 1986-12-3-00-00-00)

How do I do this?!

I have no idea at all? Can someone help with a complete description. A good set of SQL statement will help a bunch!

Thanking you guys in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 01:05:03

You could get records directly from flat file onto a staging table with all varchar nullable fields and then validate data from staging table against your rules and then populate your Suceess_Failure table based on results.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-14 : 11:26:34

How do i validate if data type is Numeric(38,0) for example,Do I use Derived Column or some sort of scripting task before moving data into SUCCESS and ERROR table.I have in total 50 columns, that I need to match the data type, length, null or not null.
The Error table would be sent out as a flat file back with the following syntax

ERROR ROW [Entire row that has error]| Table Name | Date row loaded[Using GETDATE()|Column Name[ Column that has error]| ERROR CODE[Not SSIS generated]|Error Description [Not SSIS generated]

Example:
A@|123|56.09|Lambert Drive|NY%|Table1|2/7/2008 1:34:23 PM|ID|1000|"ID is not INT"
A@|123|56.09|Lambert Drive|NY%|Table1|2/7/2008 1:34:23 PM|State|1004|"State is not of valid length"

IF more than 1 column in a row has ERROR, Ouput would look like above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 11:54:11

I think you can bring data from file file into a stging table which has columns all of varchar(50) say.
Then check each column values of this table whether they are as per reqd format of destination table using functions like ISNUMERIC() to check numeric and check whether it has decimal parts by taking right part after'.' and look if its >0.
similary for varchar field use LEN() to check if its >50.
for not null check count(*) where col is null
for date use ISDATE() and check if len() is greater than 18
each check will put records to error table if failed with corresponding reason.
All which passes all these tests can be put into success table.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-14 : 11:55:19

Do I write a stored procedure or do you think there is a transformation in SSIS that would be able to do it ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 12:24:56

what i told was to write a stored procedure which you can use to get the data from staging file, do the validations and put result in respective tables.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-14 : 12:31:37

Is it something like this, Im not sure if ISNULL function is doing write thing.Im getting a few errors though.And would the syntax be for 50 columns [IF ELSE]?

CREATE PROCEDURE CHECK1
(
@CustomerID varchar(10)
,@CustomerFirstName varchar(200)
,@CustomerLastName varchar(200)
,@CustomerPhoneType varchar(4)
,@InputDate datetime)
AS
DECLARE @sql1 varchar(8000);

set @sql1='SELECT''CONVERT(varchar(12),'+@CustomerID+', 101)''+'
''+@CustomerFirstName+'''+'''+@CustomerLastName+'''+'+@CustomerPhoneType+
'''+''CONVERT(varchar(12),'+@InputDate+',101)''FROM dbo.Customer'
)
IF(ISNULL(@Customer_ID)=1)---Check Customer_ID
BEGIN
INSERT INTO dbo.Customer
(Customer_ID,CustomerFirstName,CustomerLastName,CustomerPhoneType,InputDate)
VALUES(@Customer_ID,@CustomerFirstName,@CustomerLastName,@CustomerPhoneType,@InputDate)
ELSE
INSERT INTO dbo.ERROR
([ErrorRow],[ErrorColumn],[ErrorCode],[ErrorDes],[TableName])
VALUES(@sql1,'Customer_ID','1000','IS NOT NULL','Customer')
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 12:47:51

why are you using dynamic SQL?

like this

CREATE PROCEDURE ValidateData
AS

IF EXISTS (SELECT * FROM StagingTable WHERE Customer_ID IS NULL)
BEGIN
INSERT INTO ErrorTable (fields, Table Name ,Date row loaded,Column Name,ERROR CODE,Error Description)
SELECT fields,
<Destination table name>,
GETDATE(),
'Customer_ID',
1000,
'Customer_ID is null'
FROM StagingTable
WHERE Customer_ID IS NULL
END

IF EXISTS (SELECT * FROM StagingTable WHERE LEN(State) > 50)
BEGIN
INSERT INTO ErrorTable (fields, Table Name ,Date row loaded,Column Name,ERROR CODE,Error Description)
SELECT fields,
<Destination table name>,
GETDATE(),
'State',
1001,
'State is not of valid length'
FROM StagingTable
WHERE LEN(State) > 50
END
....
similarly for others
finally,

select * FROM StagingTable st
LEFT OUTER JOIN ErrorTable et
ON et.STPKCol=st.PKCol
AND et.Table=<destinationtable>
AND date=DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
WHERE et.STPKCol is null

gives all which are success ( didnt got populated in error table)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-14 : 12:47:51

Corrected IF(@Customer_ID IS NOT NULL)---Check Customer_Property_ID

BUT Still has 2 errors
Msg 102, Level 15, State 1, Procedure CHECK1, Line 12
Incorrect syntax near ''.
Msg 156, Level 15, State 1, Procedure CHECK1, Line 20
Incorrect syntax near the keyword 'ELSE'.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-02-14 : 12:59:07

Well I created this thinking I can directly apply this if data is coming in from flat file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources