Need controle by import error

[Aino]

Hello,

I try to import an ASCII file by using the next source:

------
INSERT INTO [ENTITIENAME]
([all-fields])
SELECT [all-fields]
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=[DIRECTORY-NAME]
;Extended properties=Text')...[FILENAME]
-----

It works fine, unless the input file has correct data.
When a field has a bad record (like a date format) it will
stop the import with the next error:

Server: Msg 8114, Level 16, State 8, Procedure [procedure name], Line 240
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

I know why the error exitst, but i need to controle the flow.

How can I controle this error within a Stored-Procedure ?

Thanks!

 

[johnv20]

what is the format of one of the records giving a problem ?

 

[Aino]

Hey johnv20,

A selection of the data:

01-04-2006 12:12:12
05-05-2003 13:15:00
01-jan-2005 10:15:00
01-04-2006 12:12:12
05-05-2003 13:15:00
13-oct-2004 09:10:06

I know how to fix this, but I a looking or a way to keep in control importing the ASCII file.
Something like this:

-----
TRUNCATE TABLE [ENTITYNAME
INSERT INTO [ENTITYNAME]
([all-fields])
SELECT [all-fields]
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source=[DIRECTORY-NAME]
;Extended properties=Text')...[FILENAME]

IF @@ERROR <> 0
BEGIN
PRINT 'The import is not good, please rollback!'

END
--------

Unfortunately, it never approach the error.


Thanks

 

[Aino]

Nobody ?