[dba-SQLServer] Bulk Insert Error.

Well, it appears that it was a simple issue of the field size being too
small.  It was not intermittent at all, my catch was causing the entire
function to exit which caused the next file to run which had the same error
but at a different record (of course!).  Once I traced it down to stopping
at exactly the same record every time I decided to go open up the field size
for that field (and about 4 similar fields) and the import is happily
running now. 



John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, September 21, 2007 11:39 AM
To: dba-sqlserver at databaseadvisors.com
Cc: VBA
Subject: [dba-SQLServer] Bulk Insert Error.

I am getting the following error in a bulk insert.

"Received an invalid column length from the bcp client for colid 40."

The full error is below.

"System.Data.SqlClient.SqlException: Received an invalid column length from
the bcp client for colid 40.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
   at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjec
t stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32
columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at FileImport_CSV.clsCreateTable.mReadFile(String lstrFileSpec) in
C:\Documents and Settings\jwcolby\My Documents\Visual Studio
2005\Projects\FileImport-CSV\FileImport-CSV\clsCreateTable.vb:line 260"

This is intermittent and occurs after tens of thousands of completed lines
inserted by the bulk copy - 160K lines in the latest case.

Any idea what this is all about?  TryCatch is as follows.

            Try
                'csvdata.
                bulkCopy.WriteToServer(csvData)
                mlngRecordsBulkCopied = mlngRecordsRead
            Catch ex As Exception
                strStatus = "Bulk Copy FAILED: " & ex.Message
                mclsDataLogger.cLogData.mMemoWriteLine(strStatus, , True)
                RaiseEvent evStatus(strStatus, False, True)
                MessageBox.Show(ex.Message)
                MessageBox.Show("Could not open the table: " + mstrCnn + ":
table: " & mclsIOData.pTblName)
                Return -1
            End Try

I am wondering if there is a way to tell the BCP client to retry or
something like that.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com