I've got another problem with my newly upgraded SQL database from Access. When I tried to insert a large record, I got this error:
String or binary data would be truncated.
So I consulted some documentation and changed the data length of that record(varchar) from 50 to 8000(maximal). I could indeed insert that record now. However, my asp application could only retrieve a small part of that record. I checked my SQL server, and the record was there.

Again it was working fine with ACCESS before.

Any Idea? Thank you!

Have you tried SQL Server datatype "text"?
Also, post some code..if you can

Thanks for replying! I did want to try text, but with text, I couldn't change the default data length. You know what? After reading your posting, I thought I'd give it a shot anyway, and text worked!

I just don't understand how come data length 16 for text could solve the problem! I did read the documentation for data type text.

Thanks again for your help!I love this forum!

Cinderella

Hi Cindrella,

You can never change the length of a TEXT data type. It is fixed and chosen for usage only when you store huge content into a column.

text - Variable-length data with a maximum length of 2^31 - 1 characters

Also when you have decided to use text datatype, be sure that you use that COLUMN at last in all your sql statements. If you use them in between in your sql queries, it may vanish and you got to search that, wondering where it went.

Should use this way
Dont use this way
(Comment - Text columns could be the 2nd, 4th columns in your table)
OR
Just take a look at this page to know about SQL server datatypes, Choosing SQL Server 2000 Data Types

I assume you would have got this error String or binary data would be truncated when using varchar(8000) datatype. That is because one row can be of size 8060 at the maximum. I have already posted similar reply here. You may want to take a look at it.

Hope that explains.
Cheers!

_________________________
-Vijay G
Strive for Perfection