FoxPro 2.6 memo fields to SQL Server

[TonyScarpelli]

I want to import a FoxPro 2.6 table into SQL Server. The sizes of some my memo records are over 1000 characters in length. SQL Server has a varchar type which appears to be able to be set to specific sizes. If I set it to 255 it will not import completely some of the records. If I set it to 500 I get a buffer size error, but it also seems a waste of space.

What's the best way to import FoxPro 2.6 memo fields into a SQL Server 2000 table?

Should I create a special table just for memo records?

Thanks.

 

[rgbean]

Why wouldn't you just use the TEXT field type? This is essentially a Memo field.

Rick

 

[foxdev]

The latest versions of MS SQL Server support up to 8,000 character VarChar columns, although the ideal would be to have it varchar(X) where X is the largest used size in your memo field.

On the buffer size error: what are you using to import the DBF into SQL Server? Some wizard or your own program?

You could use the TEXT datatype, and its behavior is similar to VFP's, but it introduces some additional complexities that you might not want to deal with (for example, your WHERE clause must use LIKE or PATINDEX). Use VarChar unless your max size is over 8,000 bytes. --------------

http://www.art-factory-outlet.com

 

[TonyScarpelli]

I used Enterprise Manager to Import the FoxPro table into a SQL Server database, so I'm not sure why I get a 'buffer too small' error. I'm not sure yet how to set it correctly.

OTOH, what might be better, to leave three varchar type fields in a SQL table, or, because the user might type in a lot of information, create a separate table that will just hold the three fields?

I've gone both ways in FoxPro, but I have to go to SQL Server now and I have to rethink how this stuff works.

Thanks

 

[EdLeafe]

Varchar fields don't work like VFP fields - when you allocate 500 characters to a Varchar field and only insert a 20-char value, SQL Server doesn't pad the field with spaces out to 500. The "var" in varchar is short for "variable"; varchar is a variable-length character field. 500 is simply the maximun size allowed for that field.

Having said that, I agree that a Text field is probably a better choice. I prefer to use varchar for things such as addresses that can have wildly different lengths, but which are all within a reasonable range. The only other consideration is searching - SQL Server is generally much better at searching in varchar than in text.

-- Ed
-- Get great VFP support in a forum filled with Microsoft MVPs!
--

http://opentech.leafe.com