BULK INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

BULK INSERT

Author

Topic

ballabhoks
Starting Member

18 Posts

Posted - 2004-02-19 : 03:58:56

I am using the BULK INSERT to transfer text file which contains 1100000 records to SQL Server 7.0
I am using the following query through visual basic.
______________________________________________________________________bulk insert CmpnyTb1
from 'G:\GLU\FOR1\CMPNYNM1.txt'
with
(formatfile = 'C:\FORMATFILES\CMPNYNM.fmt', batchsize = 1000)
______________________________________________________________________There is an error in record no. 10060 in the text file. The above statement transferring 10000 records and stop.
While 1090000 records are remaining for transfer.
In VB I am not able to trap this error. Program is showing successfull execution of the query but actually it has failed.
I am also not able to trap this error with in stored procedure.

If i am using Querey Analyzer for the same then the following error is displyaing.
______________________________________________________________________Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 1 (DnsNmbr).
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.
The statement has been terminated.
______________________________________________________________________
How i can trap this error in VB or Stored Procedure? Please Help me.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-19 : 05:38:36

in an SP you should be able to check @@error.

bulk insert ##a from 'd:\data\bcptest.txt' with (formatfile = 'd:\data\bcptestfmt.txt')
select @@error

Otherwise pipe the output to a text file and the read that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources