Re: Should I convert Memo to TEXT?

[David Satz]

a text column is SQL Server is nothing like a text column in Access. In SQL
Server 6.5 char and varchar columns could only be as large as 255, but as of
SQL 7 that limit is now 8000. as for text and ntext column, they have
basically unlimited size.

check out
http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/created
b/cm_8_des_04_7qd0.asp

"John" <sqljohn@yahoo.com> wrote in message news:3cf778e3@10.1.10.29...
>
> Hello David
> Thank you for your advise. I still have another question about datatype
that
> is what I rememberred that text field only allows up to limitation of 255
> characters (that is 10 ^8 - Please correct me if I am wrong), I am
wondering
> if this rule in MS Access memo field will also apply in SQL server?
> Now come back to my problem, Should I changed the format of Access memo
field
> to text before I convert to SQL? I do not know if MS Access will allow me
> to do this!. This is important memo data infor, so I want to have some
advise
> prior to do the job (There are more than 57000 records in the table, that
> panic me to try!!!). Thanks again for your advise
>
> John
>
> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
> >text is the correct datatype, but DTS must be doing something wrong
moving
> >the data. text can hold up to 2 gig of data.
> >
> >"John" <sqlJohn@yahoo.com> wrote in message news:3cf659b6$1@10.1.10.29...
> >>
> >> Thank you for quick respond.
> >> Is there any Other type that better to use than Text? because I use DTS
> to
> >> transfer table from MS Access to SQL server and all the Memo field from
> >Access
> >> table lost data. (I created memo field in Access table to store the
> >casenotes,
> >> so there are large text in this memo field). So if anyone has seen this
> >problem
> >> please give me some direction. Thanks again David for answer to my
> >message.
> >>
> >> John
> >>
> >>
> >> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
> >> >text or ntext
> >> >--
> >> >HTH,
> >> >David Satz
> >>
> >> >Principal Web Engineer
> >> >Hyperion Solutions
> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >> >(Please reply to group only - emails answered rarely)
> >> >-----------------------------------------------------------------
> >> >"John" <sqlJohn@yahoo.com> wrote in message
news:3cf645d4$2@10.1.10.29...
> >> >>
> >> >> Hello
> >> >> I am new to sql and have question about datatype. Is anyone know
what
> >> >datatype
> >> >> in sql is equivalent to MS Access memo datatype. any help is a big
> >> >appreciated.
> >> >> Thanks again for reading my message
> >> >
> >> >
> >>
> >
> >
>

[John]

Hello David
Thank you for your advise. I still have another question about datatype that
is what I rememberred that text field only allows up to limitation of 255
characters (that is 10 ^8 - Please correct me if I am wrong), I am wondering
if this rule in MS Access memo field will also apply in SQL server?
Now come back to my problem, Should I changed the format of Access memo field
to text before I convert to SQL? I do not know if MS Access will allow me
to do this!. This is important memo data infor, so I want to have some advise
prior to do the job (There are more than 57000 records in the table, that
panic me to try!!!). Thanks again for your advise

John

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>text is the correct datatype, but DTS must be doing something wrong moving
>the data. text can hold up to 2 gig of data.
>
>"John" <sqlJohn@yahoo.com> wrote in message news:3cf659b6$1@10.1.10.29...
>>
>> Thank you for quick respond.
>> Is there any Other type that better to use than Text? because I use DTS
to
>> transfer table from MS Access to SQL server and all the Memo field from
>Access
>> table lost data. (I created memo field in Access table to store the
>casenotes,
>> so there are large text in this memo field). So if anyone has seen this
>problem
>> please give me some direction. Thanks again David for answer to my
>message.
>>
>> John
>>
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>> >text or ntext
>> >--
>> >HTH,
>> >David Satz
>>
>> >Principal Web Engineer
>> >Hyperion Solutions
>> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >(Please reply to group only - emails answered rarely)
>> >-----------------------------------------------------------------
>> >"John" <sqlJohn@yahoo.com> wrote in message news:3cf645d4$2@10.1.10.29...
>> >>
>> >> Hello
>> >> I am new to sql and have question about datatype. Is anyone know what
>> >datatype
>> >> in sql is equivalent to MS Access memo datatype. any help is a big
>> >appreciated.
>> >> Thanks again for reading my message
>> >
>> >
>>
>
>

[John]

David
Thank you so much for all of you advise. I tried to do it again and still
get the same error message. That is "Error at destination for Row number
6322. Errors encountered so far in this task: 1. Invalid character value
for cast specification." I think that you was right on the field size and
data type. The table was created in SQL server after I click OK to exit the
error message but without any data. I think that the problem here is a DTS
Tool. Question: Is there any way to get around to import all data from Access
memo field without using DTS tool? Again thanks very much for all of your
advise

John

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>a text column is SQL Server is nothing like a text column in Access. In
SQL
>Server 6.5 char and varchar columns could only be as large as 255, but as
of
>SQL 7 that limit is now 8000. as for text and ntext column, they have
>basically unlimited size.
>
>check out
>http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/created
>b/cm_8_des_04_7qd0.asp
>
>"John" <sqljohn@yahoo.com> wrote in message news:3cf778e3@10.1.10.29...
>>
>> Hello David
>> Thank you for your advise. I still have another question about datatype
>that
>> is what I rememberred that text field only allows up to limitation of
255
>> characters (that is 10 ^8 - Please correct me if I am wrong), I am
>wondering
>> if this rule in MS Access memo field will also apply in SQL server?
>> Now come back to my problem, Should I changed the format of Access memo
>field
>> to text before I convert to SQL? I do not know if MS Access will allow
me
>> to do this!. This is important memo data infor, so I want to have some
>advise
>> prior to do the job (There are more than 57000 records in the table, that
>> panic me to try!!!). Thanks again for your advise
>>
>> John
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>> >text is the correct datatype, but DTS must be doing something wrong
>moving
>> >the data. text can hold up to 2 gig of data.
>> >
>> >"John" <sqlJohn@yahoo.com> wrote in message news:3cf659b6$1@10.1.10.29...
>> >>
>> >> Thank you for quick respond.
>> >> Is there any Other type that better to use than Text? because I use
DTS
>> to
>> >> transfer table from MS Access to SQL server and all the Memo field
from
>> >Access
>> >> table lost data. (I created memo field in Access table to store the
>> >casenotes,
>> >> so there are large text in this memo field). So if anyone has seen
this
>> >problem
>> >> please give me some direction. Thanks again David for answer to my
>> >message.
>> >>
>> >> John
>> >>
>> >>
>> >> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>> >> >text or ntext
>> >> >--
>> >> >HTH,
>> >> >David Satz
>> >>
>> >> >Principal Web Engineer
>> >> >Hyperion Solutions
>> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >> >(Please reply to group only - emails answered rarely)
>> >> >-----------------------------------------------------------------
>> >> >"John" <sqlJohn@yahoo.com> wrote in message
>news:3cf645d4$2@10.1.10.29...
>> >> >>
>> >> >> Hello
>> >> >> I am new to sql and have question about datatype. Is anyone know
>what
>> >> >datatype
>> >> >> in sql is equivalent to MS Access memo datatype. any help is a big
>> >> >appreciated.
>> >> >> Thanks again for reading my message
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>