Thank you for your reaction Raghu Bhandari...
This was not exactly what I have to know.
Suppose you have to import 16 files and there's is an error (a missing
field, or a text instead of an integer) inside the fourth file.
In that case, I want to restore my database in it's original state. I want
to undo the changes, made by the insert of file 1, file 2 and file3...
I tried a structure like this one (I dropped all the details)
BEGIN TRY
BEGIN TRANSACTION
BULK OPERATION TABLE 1 (insert file1)
BULK OPERATION TABLE 2 (insert file1)
BULK OPERATION TABLE 3 (insert file3)
BULK OPERATION TABLE 4 (insert file4) =>ERROR<=
.....
BULK OPERATION TABLE 16
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Is it not posibble to use TRY, CATCH AND TRANSACTION in combination with
BULK insert?
What am I doing wrong? What could be a resolution for this problem?
<infiraghu@gmail.com> schreef in bericht
news:1193567185.809973.111450@k79g2000hse.googlegroups.com...
[quoted text, click to view] > Hi Mobileboy,
>
>
> You can avoid such errors by making use of the udf_CheckFileStatus
> function shown below.
>
> ------------
> while 1=1
> begin
> print getdate()
> print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus('D:
> \PERFMON.CSV') )
> if dbo.udf_CheckFileStatus('D:\PERFMON.CSV') =0
> begin
> BULK INSERT [PERFMON]
> FROM 'D:\PERFMON.CSV'
> WITH
> (
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )
> BREAK
> end
>
> waitfor delay '00:01:00'
> END
> ----------------
>
> Output
>
> Mar 1 2005 8:27AM
> Status=1
> Mar 1 2005 8:28AM
> Status=1
> Mar 1 2005 8:29AM
> Status=1
> Mar 1 2005 8:30AM
> Status=1
> Mar 1 2005 8:31AM
> Status=1
> Mar 1 2005 8:32AM
> Status=0
>
> (12585 row(s) affected)
>
> Imported
>
> -------------
>
> Form this way you can know that your file is inserted or not
>
> MCP
> Raghu Bhandari
>
>
