But if the max length in that field is 252 why it is varchar(max)?


>I'm using VFP9 and SQL pass-through to extract data from a SQL Server 2012 database. The DB is the back-end for a VFP app.
>One field in the app is an editbox which allows multi-line input with line breaks. It's backed by a SQL Server VARCHAR(MAX) column (call it "MultiCol"). ISTR there are some issues with this depending on version of SQL Server and ODBC driver. Maximum length of the contents of this column is 252.
>I've tried passing through several SQL queries:
>

SELECT * FROM dbo.MyTable

>I get some columns but nothing in MultiCol.
>

* CAST to char():
>SELECT Col1, Col2, CAST( MultiCol AS char(254) ) AS MultiCol FROM dbo.MyTable
>* CAST to varchar():
>SELECT Col1, Col2, CAST( MultiCol AS varchar(254) ) AS MultiCol FROM dbo.MyTable
>

>These give me the contents but line breaks are stripped out.
>Anyone have any suggestions for getting the content while preserving the line breaks?
>


>UPDATE: CASTing to TEXT brings the contents across into a VFP Memo field, preserving line breaks:
>

SELECT Col1, Col2, CAST( MultiCol AS text ) AS MultiCol FROM dbo.MyTable