Looking at various installations of SQL Server, sometimes the 4th column returned by xp_msver will sometimes be nvarchar and sometimes it will be varchar. This appears to have no bearing on the version of SQL Server, since I see some copies of SQL Server 2000 up to 2012 return varchar, while others return nvarchar. This also does not seem to have a bearing on Windows version or bitness.
Why does this happen? and is there a way to either configure the output or know what data type will be used beforehand?
Edit: I am using Visual FoxPro to query this information, which has a number of issues dealing with unicode. So, I need to know how to handle the data and convert it to ANSI/single byte encoding - if it isn't already. I understand the limitations of ANSI/single byte, but the loss of data is considered acceptable here.
sqlexec(connhandle, "exec xp_msver")
If ADO were in the picture, I would just use the data type properties inherit in RecordSets, but i am limited to FoxPro and its own cursor functionality. When pulled into FoxPro, the Character_Value column - the 4th column in question here - is considered a MEMO data type, which is a fancy way of saying a string (of some kind or even binary data) possibly longer than 255 characters. It is really a catchall for long strings and any data types that FoxPro cannot handle, which is extremely unhelpful in this case.
There is a Microsoft KB article that explicitly uses xp_msver from FoxPro and states that SQL Server 7.0 and greater always returns Unicode for the stored procedure, but this is not always the case. Also, since xp_msver it is a stored procedure sp_help and sp_columns aren't of any use here.
In all honesty, I would prefer using SERVERPROPERTY(), but it is not supported in SQL Server 7.0, which is a requirement. I would prefer not to overcomplicate the code by having different queries for different versions of SQL Server. Also, using @@version is not a good option, since it would require parsing the text, would be prone to bugs, and doesn't provide all the information I need.
