String or binary data would be truncated. insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;...

by Rajesh 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)

hih

Comments (2) -

Rahu
Rahu
6/19/2010 11:19:39 PM #

Keep up the good work!

Rob Spellman
Rob Spellman
4/26/2011 11:46:01 PM #

Thanks, I was able to use this fix

Comments are closed

Asha Zurich Chapter

Calendar

<<  November 2014  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

View posts in large calendar