7. November 2009 03:07
When exporting data from SQLServer to Excel, when a field size is large e.g. nvarchar(4000) or nvarchar(max) might cause the error below:
Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\DataExport.xls;', 'SELECT * FROM [Sheet1$]') select * from DataExport_Table
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
Solution worked for me:
Inserted dummy data into the first 8 rows of the Excel speadsheet for the large field columns , which fixed the problem.
Obviously these records should be handled in business logic, hope this issue is resolved with SQL2008 ( yet to try out)