SqlBulkCopy does not copy strings > 255 characters long

Question

I am trying to copy a large Excel spreadsheet into the SQL Server database. I am opening an OldDbConnection to the Excel spreadsheet and reading everything from the [Sheet1$]. Then, I am using the OleDbCommand to get a IDataReader with the spreadsheet data.

There are several cells in the Excel sheet with text contents of more than 256 characters. But, once this gets uploaded to the database table using SqlBulkCopy, I only see first 255 characters from these excel cells in the database. The database table fields are 5000 characters long.

Does SqlBulkCopy limit the field size? Thanks!

Answer 1

Hi, in my experience with ADO data connections it may work better to retrieve and send large text strings when changing the cursor location of the recordset to client side.

here a short example:

Dim rstADO As ADODB.Recordset
Set rstADO = New ADODB.Recordset
rstADO.CursorLocation = adUseClient

but looking more closely at your post I think that you currently are not using ADO, anyways might be worth looking at if you decide to try to use ADO in your approach. Good Luck!

Answer 2

I am not aware of SqlBulkCopy and OldDbConnection.
however Excel might be giving out first 255 chars.
In Excel if you access a cells value with .value property you get first 255 chars, and if you acccess it with .text property you get full text. Hope this will give you some idea.