datatype for long text

[Eric]

I'm importing tables from Access to SQL Server. One of the fields in the
Access table is a "memo" data type, and it holds some lengthy entries (over
300 characters).

When I import the table into SQL Server, it uses the "ntext" datatype by
default. The problem is that all of the "memo" entries from the source table
with more than [approx.] 320 characters get substituted with the following:

<Long Text>


Is there a better data type to use for long text entries? I tried using
"nvarchar" and specifying the length as 300, but then the import won't complete.
I get an error message that the length is too long for the buffer.

Anyone?
Thanks.

[Jason Rein]

Where are you seeing the "<Long Text>" value??? That usually appears in the
SQL Server MMC when you look at the information in a table with a text or
ntext field. Try running the following query in Query Anaylzer to see if
the data is actually imported into SQL Server: "Select * From <table name>"

I hope this helps.

- Jason

"Eric" <ericb@tfillc.com> wrote:
>
>I'm importing tables from Access to SQL Server. One of the fields in the
>Access table is a "memo" data type, and it holds some lengthy entries (over
>300 characters).
>
>When I import the table into SQL Server, it uses the "ntext" datatype by
>default. The problem is that all of the "memo" entries from the source
table
>with more than [approx.] 320 characters get substituted with the following:
>
><Long Text>
>
>
>Is there a better data type to use for long text entries? I tried using
>"nvarchar" and specifying the length as 300, but then the import won't complete.
> I get an error message that the length is too long for the buffer.
>
>Anyone?
>Thanks.

[Eric]

Response to Jason:
The "<Long Text>" value is in many of the records of a table that I have
imported from Access 97 to SQL Server 7. I view this table by first opening
the Enterprise Manager, then drilling down to the specific table. With the
records exposed, I can select "Show/Hide SQL Pane" and then run the query
you suggested. Once ran, I can see that numerous records had the text in
the "Note" field replaced with "<Long Text>".
How do I know that some text disappeared in the import? Because I can go
back to my original Access table and see that there are no "<Long Text>"
entries. In fact, when comparing the two tables record-to-record, ALL OF
THE ACCESS RECORDS WITH MORE THAN [APPROX.] 320 CHARACTERS IN THE "NOTE"
FIELD HAD THE ORIGINAL TEXT REPLACED WITH "<Long Text>".

MY QUESTION SPECIFICALLY: What is the best datatype to use for a field that
will hold a text entry consisting of up to 500 characters?


"Jason Rein" <JRein@Oakwoodsys.com> wrote:
>
>Where are you seeing the "<Long Text>" value??? That usually appears in
the
>SQL Server MMC when you look at the information in a table with a text or
>ntext field. Try running the following query in Query Anaylzer to see if
>the data is actually imported into SQL Server: "Select * From <table name>"
>
>I hope this helps.
>
>- Jason
>
>"Eric" <ericb@tfillc.com> wrote:
>>
>>I'm importing tables from Access to SQL Server. One of the fields in the
>>Access table is a "memo" data type, and it holds some lengthy entries (over
>>300 characters).
>>
>>When I import the table into SQL Server, it uses the "ntext" datatype by
>>default. The problem is that all of the "memo" entries from the source
>table
>>with more than [approx.] 320 characters get substituted with the following:
>>
>><Long Text>
>>
>>
>>Is there a better data type to use for long text entries? I tried using
>>"nvarchar" and specifying the length as 300, but then the import won't
complete.
>> I get an error message that the length is too long for the buffer.
>>
>>Anyone?
>>Thanks.
>