Reading data from nvarchar(max)

afarr

Hi,

We have a Navision table that is linked to a table in another database (DBase2, which is a SQL database, but not Navision).
We use this linked table to get data from DBase2.

A new field has now been introduced in DBase2:
NewTextField nvarchar(max)

We can store the data in NewTextField 250 characters at a time, but how can we read the data in the first place?
Note that Navision doesn't have a datatype that matches the nvarchar(max) type in SQL.

Any answers are appreciated.
Thanks.

AlexWiley

I don't know if there is a development work around of some kind, but from my understanding of databases from an integration perspective, I don't think this can be done- directly at least. The metadata has to be able to be read from the client, so maybe you can copy the information from the nvarchar field into one or multiple other fields that have properties Navision can read. If there is some way of integrating the nvarchar field type into Navision that goes waaaay beyond my area of expertise. Good luck!

mandyk

Hi afarr,

Just an idea hope can help,

First option:

What if you create another Navision linked table and this table would be the sub table from the first linked table.
Create a trigger in SQL server to split the long text into multiple 250 string length and insert it into the sub table.
I can sense that the script trigger would be a bit tricky here. :-k

Second option:

you could use image datatype in SQL server and that would be blob datatype in Navision. Store your long text or data in this image field. In Navision C/AL you could then use stream to read or BigText to contain your data.

Note:
nvarchar datatype could contain UTF-8 encoding of text thus you should set your expectation correctly if you wish to display this text in Navision client. You would not be happy if you see many of this ?????????? in Navision. #-o
Good Luck!

afarr

Thanks for your replies.

The Blob and BigText (or stream) suggestion is what I was looking for. I'll try it out later as I'm busy with other stuff now.

And once the text is broken down into smaller strings, I'll store it in some associated table as you suggested.

kriki

[Topic moved from 'NAV Tips & Tricks' forum to 'NAV/Navision' forum]