July 10, 2009 at 7:23 am
Hey Guys,
I need some suggestion on this. I need to use files, stage them and create few reports. I have a logging table that has flags and a place holder to enter reasons for failures. What I need to do is periodically, I need to use that logging table and process the files that are not processed.... I am using a cursor to go through the file one by one... if say the processing fails on the 3rd file, I want to update the logging table with failure reasons and then continue on with the 4th file... Guys what is the best way to handle this scenario... any suggestion is appreciated. Do you think TRY Catch Error in Sql 2005 is helpful?
Thanks
July 10, 2009 at 7:43 am
Try/catch could worl, but a lot depends on the type of failure, there are types that even a try/catch can't capture because they kill the connection.
CEWII
July 10, 2009 at 7:49 am
How about failures like file was not found or file format was wrong? Can we log the failure in the logging table and proceed to next one? Thanks for your input.
July 10, 2009 at 10:41 am
bumping this up... thanks!
July 10, 2009 at 11:03 am
How about posting the code you are working with and the table structure of your logging table. A little hard to provide good advice when we are basically shooting in the dark here.
July 10, 2009 at 12:26 pm
---This is a draft that I have yet to work on, but should tell you about the approach...
logging_tbl is the logging table with a flag called bulk_insert_flag
table stricture:
file_key int,
file_location varchar(1000),
file_path varchar(2000),
bcp_table_name varchar(255),
bulk_insert_flag bit,
failreason varchar(1000)
drop cursor crsr_process_files
DECLARE crsr_process_files CURSOR STATIC FOR
SELECT file_key
, CASE WHEN RIGHT(RTRIM(file_location), 1) = '\'
THEN LTRIM(RTRIM(file_location))
ELSE LTRIM(RTRIM(file_location)) + '\' END + LTRIM(filename) as file_path
, bcp_table_name
FROM db..logging_tbl
WHERE bulk_insert_flag = 0
OPEN crsr_process_files
FETCH NEXT FROM crsr_process_files INTO @file_id, @file_path, @bcp_table
WHILE @@FETCH_STATUS = 0
BEGIN
-- Here the processing will be done with the files..
--- Calls couple of other PROC that loads that stages the files and creates reports based of that
IF Fails
--- update field failreason and go to next file (next record in logging table)
END
---updates the flag for the files loaded
UPDATE db..logging_tbl
SET bulk_insert_flag = 1
WHERE file_key = @file_id
FETCH NEXT FROM crsr_process_files INTO @file_id, @file_path, @bcp_table
END
July 16, 2009 at 11:58 pm
Hi,
You can use @@error variable. If the value is 0 that means some error has occured.
For ex:
IF @@error 0
Begin
--- Update statement for loging
FETCH NEXT FROM crsr_process_files INTO @file_id, @file_path, @bcp_table
continue
End
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply