Access managment in SQL

Hi,
I have just imported my old access database into my new
SQL database and have come over a few problems.
All memo fields have been changed to "ntext" fields. This
is no good for me as I have lots of text in the fields.
When I try and change one of the fileds I get this error:
'creek_products' table
- Unable to modify table.
ADO error: Cannot create a row of size 8129 which is
greater than the allowable maximum of 8060.
The statement has been terminated.
I'm only trying to change one field to nchar because then
it will let me have a lenght of 4000.
Can anyone help me with this?
I need to get the max lenght field type working.
Thakns
I hope you got answer to your question but if not:
In SQL Server every row has a limit in size.
Consider when converting, creating tables first and then pouring data into it.
I am still puzzled what's wrong with ntext data type?
"Luca" wrote:

> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
>
|||Luca wrote:
> Hi,
> I have just imported my old access database into my new
> SQL database and have come over a few problems.
> All memo fields have been changed to "ntext" fields. This
> is no good for me as I have lots of text in the fields.
> When I try and change one of the fileds I get this error:
> 'creek_products' table
> - Unable to modify table.
> ADO error: Cannot create a row of size 8129 which is
> greater than the allowable maximum of 8060.
> The statement has been terminated.
> I'm only trying to change one field to nchar because then
> it will let me have a lenght of 4000.
> Can anyone help me with this?
> I need to get the max lenght field type working.
> Thakns
Why is ntext no good for you? If you have lots of text, as you
mentioned, then ntext or text is what you want. ntext is unicode and
requires twice the storage of text. If you don't require unicode
support, use text.
The nchar(4000) requires SQL Server reserve 4000 bytes for each row.
That will give you terrible performance because only a couple rows will
fit on each SQL Server page (same as nvarchar(4000) would do). There's a
limit of about 8060 total bytes stored within a row (excluding the text
in text and ntext columns). Using text or ntext fixes this by only
requiring a 16 byte pointer in the table. The text is stored elsewhere,
on a different filegroup if you prefer.
Can you explain what problem you feel you are having using ntext?
David G.