I am trying to import some product data from a xls spreadsheet (provided by an external supplier) into SQL Server 2005. Some of the product descriptions are longer than 255 characters and they are being truncated to 255 when I bring them into SQL.
I have been using:
INSERT into dbo.BCatalogue
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\blahblahblah\Export.xls;Extended Properties=Excel 8.0')
...[B_Catalogue$] WHERE SequenceKey is not null
The fields that are being truncated are defined as varchar(4000) in the BCatalogue table.
I've also tried adding a linked server and selecting from that.... same problem!
This is not a matter of the results field not displaying fully in management studio because if I use len(description) the larger rows return 255.
Has anyone got any ideas?