>Hello,
>
>I have a small SQL server database that stores a variety of options for a VFP client application. The table I am having problems with stores criteria for a kind of reporting engine.
>
>Each individual option is potentially a different datatype: datetime, varchar or int.
>
>When accessing a SQL_Variant field via SQLCONNECT() to an ODBC driver, the SQL_Variant results are returned within a memo field, using the client's regional settings to translate data types such as datetime.
>
>An error in the VFP client has brought to light some very odd behaviour within the memo fields returned on certain user machines.
>
>On certain machines the memo field reads perfectly well... data being displayed as a string within the memo. On the machines that have crashed however, the memo contains the same data only each individual caharcter is followed by a single invisible character. Furthermore, the invisible character appears to be null, not registering when I try to perform an ASC() check on it. This appears to effect every record pulled back.
>
>An example of what has been pulled back would be:
>
>Correct machine:
>"30/01/2005 13:00:00"
>or "2"
>
>Incorrect machine:
>"3 0 / 0 / 1 / 2 0 0 5 1 3 : 0 0 : 0 0 "
>or "2 "
>
>Both incorrect and correct machines operate on Win2K or WinXP operating systems and as far as I can tell their ODBC drivers are all identical. Even performing a LIST STATUS on each machine shows that the various VFP settings are all consistent with each other.
>
>Has anyone encountered this kind of behaviour before? Could anyone direct me to any settings either VFP or ODBC related that may be causing this behaviour?
>
>Many thanks in advance for any advice anyone can provide.
>
>Regards,
>
>Ben Sugden
Ben,
AFAIK SQL server odbc driver doesn't support sql_variant type and return the data as a BLOB. The 'incorrect' data you're seeing is unicode data. You might check the value and if it has chr(0) in it then convert with strconv(). If you check client machines probably you would see some of them have unicode support enabled and some are not.
IMHO using sql_variant in the first place is asking for trouble.
Cetin