Select ntext field returns truncated data

[nalamo]

When I select the Notes field in OCLG from a query inside SAP B1, the field contents are truncated for certain records. There doesn't seem to be any consistency as far as where it cuts it off. I've tried converting to nvarchar and it still truncates at the same spot. When I query the same directly in SQL I don't have this issue.

SAP B1 9.0. PL6.

What is different with the B1 query that is not returning some of the ntext content?

Alan

[Former Member · Accepted solution]

Hi Alan,

It is probably maximized for 254 characters within B1 query. If it is over the limits, it will be truncated.

Try to add another column using a formula such as by testing:

SELECT Substring(Notes,255,254) FROM OCLG

WHERE Len(Notes)>254

Thanks,

Gordon

[nalamo · Accepted solution]

Gordon,

Yep, that's it. I had to modify a little your query as ntext won't take len(), but that helped figure it out. Thanks much.

[frank_wang6 · Accepted solution]

You can cast it to nvarchar(4000) first and then do len().

[nalamo · Accepted solution]

Frank, Yes, that's what I did.