June 30, 2010 at 1:04 pm
Is it possible to get error record from a table? Not error_line() function. Say there are 1000 rows in a table and i join it with other 100 lines for an insert operation. If there are any error occured, can i get that error row from the table.
June 30, 2010 at 3:20 pm
Not sure what kind of error you're talking about. Sounds like you want to know if records fail to join, but that wouldn't be an error ... it would just be two records that aren't joined. Maybe you can be a bit more specific.
July 1, 2010 at 3:08 am
Im currently migrating one if my existing application. In existing app. the error handling is done row by row. Data from text file have to be loaded in sql with lot of business rules. On doing this if any error found, that particular record will be written in error file.
Is this possible in stored procedures?
July 1, 2010 at 9:00 am
As you havn't posted any sample data or table srtucture this example below might not be what your looking for but might give you some inspiration.
It is a cursor which could be slow depending on the number of rows your talking about. You would import your text file to varchar or nvarchar columns using SSIS, bcp etc. But include two extra colimns IsValid and ErrorNum.
Then test each constraint i.e can you convert a column to datetime, if so check the next row if not set the row as invalid and set the error num.
Once complete query the table for bad data, fix the problems, retest then move it to wherever it needs to go.
--Create a test table
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'ImportedData')
DROP TABLE ImportedData
CREATE Table ImportedData (
Col1DateValueString varchar(255),
IsValid bit NULL,
ErrorNum int NULL)
--Create test data
INSERT INTO ImportedData (Col1DateValueString)
SELECT '01-01-2010 15:38:09.650' UNION ALL
SELECT '2010-07-01 15:38:09.650' UNION ALL
SELECT '10 August 2010' UNION ALL
SELECT 'Invalid Data' UNION ALL
SELECT '2010-08-02 15:38:09.650' UNION ALL
SELECT 'A12B&^*' UNION ALL
SELECT '2010-07-01 15:38:09.650'
-- Temporarily add a id column to ImportedData table to allow for updates
ALTER TABLE ImportedData ADD ID INT IDENTITY(1,1)
GO
--Declare Variables for use throughout the data testing
DECLARE @ID int,
@STRING varchar(255),
@test1 datetime
-- Use a cursor test each conversion constraint
DECLARE test_validity_cursor CURSOR
FOR SELECT ID, Col1DateValueString
FROM ImportedData
OPEN test_validity_cursor
FETCH test_validity_cursor INTO @ID, @STRING
WHILE @@FETCH_STATUS = 0
BEGIN
-- Test String to see if its a valid date value
BEGIN TRY
SELECT @test1 = CAST(@STRING AS datetime)
UPDATE ImportedData
SET IsValid = 1
END TRY
--If test of date causes error update the IsValid and ErrorNum fields of the row.
BEGIN CATCH
UPDATE ImportedData
SET IsValid = 0,
ErrorNum = ERROR_NUMBER()
WHERE ID = @ID
END CATCH
FETCH test_validity_cursor INTO @ID, @STRING
END
CLOSE test_validity_cursor
DEALLOCATE test_validity_cursor
--Remove the ID Column From the Staging table
ALTER TABLE ImportedData DROP COLUMN ID
GO
--View Invalid Data
SELECT * FROM ImportedData
MCITP SQL 2005, MCSA SQL 2012
July 1, 2010 at 11:09 am
Karthikeyan-418364 (7/1/2010)
Im currently migrating one if my existing application. In existing app. the error handling is done row by row. Data from text file have to be loaded in sql with lot of business rules. On doing this if any error found, that particular record will be written in error file.Is this possible in stored procedures?
You can do a couple of things:
1. Use SSIS and handle all the evaluations as part of the process of importing from the text file. This is good if the evaluation of each row is self-contained (not dependent on any other row.)
2. Do a full import of the text file into an intermediary table and handle the translation of the final results to your regular table(s) via set-based processing. (Very very rarely will row-by-row be either necessary or desirable.)
Without knowing anything about your structure or what kind of rules you're wanting to enforce I cannot be any more specific than that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply