Exporting an ntext field to excel

[mwheads]

Hi All (this is a small excerpt of what i'm trying to do)

rs = oData.OpenRecordset("select convert(nvarchar(4000),substring(FREE_TEXT,1,15000)) from tblTable")
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").CopyFromRecordset(rs)


I have a FREE_TEXT which is ntext in the table, when I export this field standalone as ntext to excel, nothing is exported.

If I change the table field type to nvarchar(4000) is exports perfectly up to 4000 but the FREE_TEXT field could be longer than 4000.

If I substring the FREE_TEXT field as ntext in the rs then it only exports a truncated 255 result.

Does anybody know how to get this field successfully to excel?

 

[markros]

What is your SQL version? In SQL Server 2005 and up you may try nvarchar(max) for your field.