Error Handling into log file -- Please Help

[krotha]

I have a procedure which I am looping BULK INSERT statement no of times with no of files available.
Now I want to handle the errors during bulk insert statement. suppose one table is not inserted due
to constraint violation, it should raise a error and write it into a log file and continue to insert
next table. If another table gets an error then the error should go same log file and continue to
insert the next tables .I will be thankful for any hint.
Is something I have to pass 'log file' as output parameter to the procedure.


CREATE PROCEDURE load_bulk_insert @XMLDOC VARCHAR(8000)
AS
DECLARE @IDOC INT
DECLARE @TMPXML VARCHAR(2000)
SET @TMPXML='<ROOT>
<Hierarchy name=&quot;OWNER&quot;>
<Levels>
<Level name=&quot;STATE&quot; filename=&quot;state.txt&quot; path=&quot;c:\temp&quot; />
<Level name=&quot;DISTRICT&quot; filename=&quot;district.txt&quot; path=&quot;c:\temp&quot; />
<Level name=&quot;REGION&quot; filename=&quot;region.txt&quot; path=&quot;c:\temp&quot; />
<Level name=&quot;SCHOOLS&quot; filename=&quot;schools.txt&quot; path=&quot;c:\temp&quot; />
<Level name=&quot;PROGRAM&quot; filename=&quot;program.txt&quot; path=&quot;c:\temp&quot; />
</Levels>
</Hierarchy>
</ROOT>'
EXEC SP_XML_PREPAREDOCUMENT @idoc OUTPUT, @TMPXML
declare @sql varchar(1024),@db_table varchar(255),@data_file varchar(255),@path varchar(255)
declare c1 cursor for
select *
from openxml( @idoc, '/ROOT/Hierarchy/Levels/Level')
with ( name varchar(255) '@name', filename varchar(255) '@filename',fpath varchar (255) '@path' )
open c1
fetch next from c1 into @db_table,@data_file,@path
WHILE (@@FETCH_STATUS <> -1)
begin
IF (@@FETCH_STATUS <> -2)
begin
set @sql= &quot;BULK INSERT &quot;+@db_table+&quot; FROM '&quot;+@path+&quot;\&quot;+@data_file+&quot;' WITH (FIELDTERMINATOR=',', ROWTERMINATOR = '\n')&quot;
Execute (@sql)
end
fetch next from c1 into @db_table,@data_file,@path
end
close c1
deallocate c1
EXEC sp_xml_removedocument @idoc
GO

 

[tlbroadbent]

I would create a LOG table instead of a log file. Insert error messages into the table. At the conclusion, select the set of records from the LOG table to display. You could send the result set to someone via xp_sendmail or write the log file to disk using one of the techniques described at the following link.

http://www.swynk.com/faq/sql/sqlfaq_development.asp#FlatFile

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.