Hi there guys,

I am trying to export some xml data stored in a table with a data type of TEXT using the following:

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ExtractTables\ExcelFiles\SQL2000\SQL2000Tables.xls',
'SELECT * FROM [ApplicationXMLConfig$]')
SELECT xmlconfig from tableName

However, because my data is longer than 8000 chars, my statement terminates with a truncation error.

How can I export text data types into a row in excel?

Ah! SQL Server 2000. It is possible to do by inserting them into cells in 8000 char chunks, using the substring command to slice up the text BLOB. Then you would have to concatenate the cells in Excel. Yes, it is not pretty, but I don't know other way in SQL 2000. TEXT data types are as friendly as a cornered rat.

something like
SELECT substring(NastyTextColumn,1,8000), substring(NastyTextColumn,8001,8000),..etc...

thanks Phil, i'll give that a bash tomorrow.

Nice one Phil, that worked a treat :-)

Hmm. I don't get it right every time but I've had a lot of struggles with SQL Server BLOBs over the years and so I'm a repository of redundant hacks with TEXT data types. I'm glad it worked!

Just need to figure out how to name the select results as one col with a name...