Truncated Data

[andyc209]

I am posting data from a table to excel using openrowset however i keep getting 'String or binary data would be truncated.' errors which i assume means the data i am passing is too big. My code is below:

DECLARE @SQL3 as varchar(max)
SET @SQL3=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@xlFileName3+''',''Select * from [DATA$]'')SELECT REF,CASETYPE,NAME1,NAME2,NAME3,ADDRESS1,ADDRESS2,ADDRESS3,CITY,COUNTY,POSTCODE,el_notes FROM V_EL'
EXEC(@SQL3)

i have declared everyting in the rest of my code - i was thinking the el_notes field which contains case notes is is too many words. Is there anything to change the varchar(max) or is the problem with excel.

this code works fine when the el_notes field contains less text.

 

[sabinUE]

Hi
try to insert the SET cmd:

SET ANSI_WARNINGS OFF


S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[sabinUE]

And the second point
try to insert one column at the time , then add another , and so one

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[SQLBill]

I don't really work with moving data from SQL Server to Excel, but when you see the error about data being truncated it normally means it is happening at the destination...in this case that is Excel. So the destination column can't hold the amount of data in the el_notes column. Are you saying the SQL Server column el_notes is VARCHAR(MAX)? What is the column set for in Excel?

Run this in SQL Server:

SELECT MAX(LEN(el_notes))
FROM v_el
GO

That will show you how many characters are in the longest row for column el_notes. Then look at your Excel column to see what is the max amount of characters that can be entered.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TysonLPrice]

What version of Excel are you on? That makes a differenece on how many characters a column can hold.

 

[andyc209]

its posting to 2003. Found a sort of fix - added a hidden row below the headings and in the columns where i am expecting loads of data put in lots of random characters so that it sets the column to be 500+ characters - a bit of a fudge but kind of works.