MEMO datatype

i am migrating access database to sql server2005.
there is field with datatype "MEMO" in access.
can anebody tell me what is the compatible datatype for memo in sqlserver2005
I tried with varchar,varchar(max).
the field in access database conatains large comments

If by large comments you mean a lot of character data, most likely a nvarchar(max) or varchar(max) should work. What happened when you tried with varchar and varchar(max) ?

-Sue

|||

yes by large comments means lots of character data.

I am using SSIS package for importing the data.this package is created by using wizard.

To migrate the database i used upsizing wizard in access .

the column comments which is having datatype memo gets automatically converted to text_stream(DT_TEXT) datatype.

when I used varchar or varchar(max) for the corresponding column in Sql server 2005 the data got migrated but truncation occured. But when I modified the datatype from varchar to nvarchar the SSIS throws the warning that it can not convert unicode to non-unicode

|||

If you used [ varchar ] without a number indicating how many characters (maximum possibility is 8000) then the default of varchar(50) was used and your data would have been truncated.

If you used [varchar(max) ], you should not have experienced any truncation.

|||

,i used varchar(max) datatype . but problem is that the data in access contains special character end of line.

and I can see that after the first special character is encountered the data gets truncated from that point.

e.g

data in access is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.
08/11/05: JEAN HAS REVIEWED THE TREATY - FINAL CHANGES MADE. JEAN EMAILED THE TREATY TO TIM IN WORD FORMAT SO THAT HE MAY INCLUDE THE BOLI LANGUAGE. WE STILL NEED TO DRAFT THE COVER NOTE FOR SUN LIFE.
08/11/05: Tim receives email from Carmen Walter - premiums are monthly in arrears with reinsurance premium = reinsurance rate x 1/12 annual guaranteed COI x NAR. Note annual guaranteed COI has a q/(1-q/12) adjustment in it.

and data that goes to sql server 2005 is

08/04/05: Received request for a new Automatic YRT treaty covering UL plans. Assignment and Request Forms given to Rashmika to set up in the PDA & CTM. THE RETRO TREATY IS SUNL-99.

can you suggest any solution for this?

|||Instruct the data migration process to use a different 'end of record' delimiter.