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

Tags:

Technical

Comments (2) -

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

Keep up the good work!

Reply

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

Thanks, I was able to use this fix

Reply

Add comment

biuquote
  • Comment
  • Preview
Loading

Asha Zurich Chapter

Calendar

<<  April 2014  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

View posts in large calendar