Bulk Insert error checking

Bulk Insert error checking

Post by kob uk » Wed, 01 Oct 2003 00:01:05



I am trying to import perflog output files into a database and then later
analyze it.  I am using bulk insert for the import. At the 12th file I got
this error:

Importing file server_20030924.tsv
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005:  The provider did not give any information about the error.].
The statement has been terminated.

Here is the the code that does the import:


''' WITH ( ' +
'   CODEPAGE = ''OEM'', ' +
'   firstrow = 1, ' +
'   DATAFILETYPE = ''char'', ' +
'   FIELDTERMINATOR = '' '', ' +
'   ROWTERMINATOR = ''\n'' , ' +
'   KEEPNULLS ' +
') '



This is definitely not the right error checking! How to check for successful
execution of the call?? I want to skip the file and continue with the
execution of my stored proc. Now, it breaks out.
I know there are lots of SQL monitoring tools that would do this for me, but
... no money...so I better do it myself.

Any help appreciated!

kob uki

 
 
 

Bulk Insert error checking

Post by oj » Wed, 01 Oct 2003 09:08:42



terminated. Your option would be to catch it a the client side and continue
calling the stored procedure with the next file.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


> I am trying to import perflog output files into a database and then later
> analyze it.  I am using bulk insert for the import. At the 12th file I got
> this error:

> Importing file server_20030924.tsv
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005:  The provider did not give any information about the error.].
> The statement has been terminated.

> Here is the the code that does the import:


> ''' WITH ( ' +
> '   CODEPAGE = ''OEM'', ' +
> '   firstrow = 1, ' +
> '   DATAFILETYPE = ''char'', ' +
> '   FIELDTERMINATOR = '' '', ' +
> '   ROWTERMINATOR = ''\n'' , ' +
> '   KEEPNULLS ' +
> ') '



> This is definitely not the right error checking! How to check for
successful
> execution of the call?? I want to skip the file and continue with the
> execution of my stored proc. Now, it breaks out.
> I know there are lots of SQL monitoring tools that would do this for me,
but
> ... no money...so I better do it myself.

> Any help appreciated!

> kob uki


 
 
 

Bulk Insert error checking

Post by Gavin Lando » Wed, 01 Oct 2003 17:13:32


Unfortunately SQL has no way to error check outside of what your doing..

error occurs then I trigger an event, email, log to a file, what ever I want
to do..   Although there was an error, you could will continue to execute so
if you want things to stop, return out of the stored proc on error..

Some people use "begin trans", "rollback trans" for to help them here, but I
have found for large amounts of transactions this method is unacceptable.
I found when you use begin trans, every table you insert or update, gets
locked until the rollback or commit happen.   Now, if it's 2 or 3 inserts it
shouldn't be much of a problem, but no one, I mean no one can access these
tables until rollback or commit has been executed.    So if you go that
route, keep that in mind.


> I am trying to import perflog output files into a database and then later
> analyze it.  I am using bulk insert for the import. At the 12th file I got
> this error:

> Importing file server_20030924.tsv
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005:  The provider did not give any information about the error.].
> The statement has been terminated.

> Here is the the code that does the import:


> ''' WITH ( ' +
> '   CODEPAGE = ''OEM'', ' +
> '   firstrow = 1, ' +
> '   DATAFILETYPE = ''char'', ' +
> '   FIELDTERMINATOR = '' '', ' +
> '   ROWTERMINATOR = ''\n'' , ' +
> '   KEEPNULLS ' +
> ') '



> This is definitely not the right error checking! How to check for
successful
> execution of the call?? I want to skip the file and continue with the
> execution of my stored proc. Now, it breaks out.
> I know there are lots of SQL monitoring tools that would do this for me,
but
> ... no money...so I better do it myself.

> Any help appreciated!

> kob uki

 
 
 

Bulk Insert error checking

Post by kob uk » Thu, 02 Oct 2003 16:55:36



INSERT' then it doesn't matter where do you put the error checking. If bulk
insert fails, there is no continue. :)

kob uki


> Unfortunately SQL has no way to error check outside of what your doing..
> What I did is created a stored procedure that's called ErrorCheck and I
pass

an
> error occurs then I trigger an event, email, log to a file, what ever I
want
> to do..   Although there was an error, you could will continue to execute
so
> if you want things to stop, return out of the stored proc on error..

> Some people use "begin trans", "rollback trans" for to help them here, but
I
> have found for large amounts of transactions this method is unacceptable.
> I found when you use begin trans, every table you insert or update, gets
> locked until the rollback or commit happen.   Now, if it's 2 or 3 inserts
it
> shouldn't be much of a problem, but no one, I mean no one can access these
> tables until rollback or commit has been executed.    So if you go that
> route, keep that in mind.



> > I am trying to import perflog output files into a database and then
later
> > analyze it.  I am using bulk insert for the import. At the 12th file I
got
> > this error:

> > Importing file server_20030924.tsv
> > Server: Msg 4832, Level 16, State 1, Line 1
> > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'STREAM' reported an error. The provider did not give
any
> > information about the error.
> > OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned
> > 0x80004005:  The provider did not give any information about the
error.].
> > The statement has been terminated.

> > Here is the the code that does the import:


+
> > ''' WITH ( ' +
> > '   CODEPAGE = ''OEM'', ' +
> > '   firstrow = 1, ' +
> > '   DATAFILETYPE = ''char'', ' +
> > '   FIELDTERMINATOR = '' '', ' +
> > '   ROWTERMINATOR = ''\n'' , ' +
> > '   KEEPNULLS ' +
> > ') '



> > This is definitely not the right error checking! How to check for
> successful
> > execution of the call?? I want to skip the file and continue with the
> > execution of my stored proc. Now, it breaks out.
> > I know there are lots of SQL monitoring tools that would do this for me,
> but
> > ... no money...so I better do it myself.

> > Any help appreciated!

> > kob uki

 
 
 

1. Error when bulk insert follows another large bulk insert

I am running a job out of MS SQL Enterprise Manager where the first step
turns off transaction logging, the 2nd step is a large bulk insert step,
and the 3rd step is another large bulk insert step.  I receive the
following error when executing the 3rd step.  Is this error occurring
because the database is still comitting data from the previous large bulk
insert at the same time as it is performing the next bulk insert?  If so,
is there a workaround?  Thank you for any help you can provide.

Error message from Step 3 (2nd bulk insert):
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized.
Reissue the statement after the current backup, CHECKALLOC, or
file manipulation operation is completed. [SQLSTATE 42000] (Error 3023)  
The statement has been terminated. [SQLSTATE 01000] (Error 3621).  
The step failed.

--
Posted via CNET Help.com
http://www.help.com/

2. Help - intermittent problem

3. How do I error check with multiple BULK INSERTs

4. INGRES NT Server status?

5. BULK-INSERT File existance checking

6. .ODBC and Excel

7. Bulk Delete (as opposed to Bulk Insert)

8. Btrieve 5.10 and Win95

9. BULK INSERT or BULK COPY

10. Bulk Insert / Bulk copy

11. BULK INSERT under bulk copy?

12. DTS Error (BULK INSERT)

13. Bulk Insert Error with SQL 2000