Intercepting Bulk Insert errors
-
Hi,
I'm trying to handle Bulk Insert errors, but with no success so far.
I created a table like the following
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
I'm going to populate the table with data stored in a flat file which only
contains data for the field 'Code', using a format file like this one:
8.0
2
1 SQLCHAR 0 0 "" 0 PKCheck ""
2 SQLCHAR 0 0 "\r\n" 1 Code ""
When I run:
Bulk Insert Target from 'Target.dat' with (FORMATFILE='Target.fmt')
everything works fine.
But if I use a bad format file like this one:
8.0
1
1 SQLCHAR 0 0 "" 0 PKCheck ""
2 SQLCHAR 0 0 "\r\n" 1 Code ""
I get the following error
Incorrect syntax near the keyword 'from'
Also with:
8.0
2
1 SQLCHAR 0 0 "" 0 PKCheck ""
2 SQLCHAR 0 0 "\r\n" 3 Code ""
I get:
Could not bulk insert. Error reading destination etc...
Great. That's what supposed to happen.
The problem is that these kind of errors cannot be intercepted even by using
exec or sp_executesql, or by enclosing the statement into its own stored
proc. Not only they cause the termination of the containing batch or stored
proc, but also that of the calling batch.
What I'd like to do is, for instance:
exec(N'Bulk Insert Target from ''Target.dat'' with
(FORMATFILE=''Target.fmt'')')
if @@Error<>
print 'Bulk Insert error'
Instead the execution never reaches the if statement.
Any ideas?
TIA
Salvor
Salvor
Salvor -
I do not know what your "flat file" looks like, but if it contains values for your PKCheck identity column, try
SET IDENTITY_INSERT TableName ON
If PKCheck data is NOT in your flat file, Create a VIEW as
SELECT Code FROM TARGET
then Bulk Insert into the view, then drop the view.
Once you understand the BITs, all the pieces come together
-
As you can see from my example format file, the field PKCheck is not supposed to be loaded from the flat file. In this case tha flat file would only contain one column of data that will be placed in the Code field. I made up this example just not to use something too trivial.
My problem is not how to manage things for a successful loading (or living ;), but how to intercept a possible error. As the time being, every bulk insert error makes the sp that executed it and every sp up in the calling chain bomb out.
Anyway thanks for replying.
quote:
I do not know what your "flat file" looks like, but if it contains values for your PKCheck identity column, trySET IDENTITY_INSERT TableName ON
If PKCheck data is NOT in your flat file, Create a VIEW as
SELECT Code FROM TARGET
then Bulk Insert into the view, then drop the view.
Salvor
Salvor -
Error handling has always been IMHO the weakest point in SQL Server. I would use BulkInsert DTS Taks to handle the errors in an easier way!
You could increase the MAX_ERROR number in the statement but that does not help much.
* Noel -
Unfortunately increasing MAXERRORS doesn't help at all, because when the format file is wrong the error occurs before loading.
Also, I'm strictly bound to using sp's, no DTS.
Well, I think we'll have to wait for Yukon to see some improvement on the subject.
Salvor
Salvor -
Have you try just to insert to a table with 1 column of identity and 1 column of data.
Once you got it into the database you could parse and look for space and set it as your 1st column of data and the rest to be set as your 2nd data.
If this is not what you want chances are you could still get what you want because you can write sql to manipulate the data until you get what you want.
mom
-
Maybe I haven't made myself clear enough.
The problem is not about the data: using the first format file example in my initial post I can load the flat file without any error. Troubles arise whene the format file is wrong (like in the other two examples of mine), that is BEFORE the loading can start. Could be either the syntax, or referencing a non existing target column, or even an empty format file.
Salvor
Salvor -
Check out
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17942
Once you understand the BITs, all the pieces come together -
ThomasH,
I had already thought of using DMO or a JOB, but I decided to use them as a last resort, mainly becouse I think they would be an inelegant solution to my problem (don't get me wrong, I would use your or GRN's suggestions in other situations, I simply don't like the idea of using an external 'tool' to execute a SQL statement from inside SQL Server).
Actually, my question has now turned more into a philosophical one, since at the moment I'm dynamically creating my format files in the same sp executing the bulk insert, so once it works, it'll work forever unless someone change the structure of the target table, and that shouldn't happen without control.
Still I keep wondering why SQL Server programmers at MS decided to make it work like this. It should be just like any other SQL statement. I mean, when you execute this batch:
declare @Res int
exec('select * from NonExistingTable')
select @Res=@@Error
if @Res<>0 print @Res
you get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'NonExistingTable'.
208
Doing the same with bulk insert doesn't get past the exec:
declare @Res int
exec('Bulk Insert Target from ''Target.dat'' with
(FORMATFILE=''Target.fmt'')')
select @Res=@@Error
if @Res<>0 print @Res
You get:
Server: Msg 208, Level 16, State 82, Line 1
Invalid object name 'NonExistingTable'.
Only the state is different. Maybe it has something to do with that.
Maybe they just forgot it (like other things).
Salvor
Salvor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply