Memo Data Type

Hi
"doodle" wrote:

> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John
"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegrou ps.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi
"doodle" wrote:

> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John
|||Hi
"doodle" wrote:

> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John
sql