>>>Hi,
>>>
>>>just wanted to verify, if SET ANSI_PADDING OFF when creating SQL-Server tables is the only solution for getting rid of trailing blanks in MS SQL Server varchar() fields, when updating via ODBC from VFP.
>>>Is there really no connection level setting (MSSQL SET command) which could be used? What do people do, if they have to use an existing database where tables were created with SET ANSI_PADDING ON, which is the setting MS recommends?
>
>>If varchar field gets trimmed value then it doesn't store trailing spaces whether padding is on or off.
>
>That's true, but the problem begins if VFP is updating data in a varchar column.
>
>> Also if you use memo as datatype on VFP side varchar fields wouldn't get extra spaces (provided there aren't intentional trailers).
>
>That's also true ... I forgot to write, that I'm currently using SQL-PT. So I can't assign any alternative data types for my columns in the resultset.
>
>>PADDING OFF is SQL server's default AFAIK.
>
>Some minutes ago I had a second look in the MSDN library. They explicitly warn to use SET ANSI_PADDING OFF.
>
>>When I have to use it I either use memo datatype or I accept them as fixed length fields similar to VFP and trim(),pad() on VFP side or rtrim(), convert() on SQL server side.
>
>I think I have to live with the trailing blanks ... reading the MSDN library I came across article Q296559. It says, that an ALTER COLUMN autmatically sets a column to ANSI_PADDING ON, regardless of the SET ANSI... setting, when the column is altered. Since we use xCase to manipulate the database and xCase uses ALTER COLUMN to manipulate fields, I think it would be best to live with the trailing spaces. Maybe xCase can be configured to act another way ... but if you're using the Enterprise Manager for temporarily altering a column, it will always set the column to PADDING ON.
>
>How easy would live be, if I could just tell VFP not to send those trailing spaces ...
>
>Regards,
>Armin

Armin,
You might indirectly change datatype to memo with SPT. If usememosize is less then your varchar column length then VFP would automatically use a memo column. After initial select it wouldn't do any good for you but you could set it before initial select using system cursor (0) as cursor. ie:

lnHandle = ....
cursorsetprop('UseMemoSize',1,0)
SQLExec(lnHandle,'select....
* Now any varchar field wider than 1 char is memo field.

Cetin