Hi all,

We’ve been slowly moving some of our local SQL Server data to an Azure database by re-creating the tables on the Azure server, loading them with data, and then changing our VFP and WebConnection applications’ data access (via business objects or views) to access the Azure data. This has worked great, and we’ve moved several tables over to the Azure database, with no issues, but I recently moved the first table with a VARCHAR(max) field and I’ve run into a problem. I think it’s a driver issue, but was hoping someone could verify.

We loaded this new Azure table with data, including the Varchar(max) field, with no issues, but when we queried the table (using select *) in VFP 9, the varchar(max) field in the result cursor was empty, and a DISPLAY STRUCTURE revealed the field had been converted to a CHAR(0) field. We then created a remote view accessing the same table, and when we tried to save it, we received an invalid field type error for that varchar(max) field. Using “View SQL” in the view designer, the varchar(max) field was again CHAR(0). We manually changed the field to M(4), memo, in the view designer’s sql window, and then saved the view successfully and were able to access the data as expected.

Thinking it was a driver issue, I removed the driver name from the ODBC connect string, and the query worked and the varchar(max) field was correctly mapped to a memo field in the result cursor.

Our original ODBC connect string was:

Driver={ODBC Driver 13 for SQL Server};Server=tcp:xxxxx.database.windows.net,1433;Database=ourdatabase;Uid=xxxxx;Pwd=xxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Which didn’t work. I removed the driver name and modified it to

Server=tcp:xxxxx.database.windows.net,1433;Database=ourdatabase;Uid=xxxxx;Pwd=xxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

And it works. I’m just wondering if anyone has any more info on this problem, and if it should be ok to use this modified connect string.

Any help would be appreciated! Thanks, Kirk

Kirk,

My guess is that without the SQL driver name it is reverting to the 'SQL Server' driver which is, as far as I know, the only driver that works with VFP and varchar(max). But, this driver will short you on other SQL Datatypes (big int, datetime2 and date).

Please see this thread: http://support.west-wind.com/Thread5S30FNXWI.wwt and look near the bottom for where I tested 3 different SQL Server drivers and various datatypes and VFP. Basically, you'll have to pick your poison on which driver to use. I always opted to not use the simple and old 'SQL Server' driver because it does not support the simple Date (no time) datatype.

Instead of varchar(max) you might need to use TEXT which MS says they will deprecate one day.

Here's a discussion about this topic a few weeks ago: https://support.west-wind.com/Thread5S30FNXWI.wwt

Thanks all. That was very helpful.

I guess I'll use the "old" driver for now....

Kirk