Though we're slowly migrating off VFP, we're still stuck with it for some legacy applications for a while. Because of security concerns, we need to support TLS 1.2; this isn't supported using the normal SQL Server drivers, so we need to change to using an updated ODBC driver. So, since we're using nvarchar(max) for all of our "text" fields we're seeing the VFP bug that returns these as empty strings. We're looking at a work-around to use a .net bridge object to fetch data for these fields, but we're also re-evaluating all of our nvarchar(max) fields to see what can be changed to regular nvarchar.

So, my question is this: Why does the wwsession table uses text fields for ip and browser? I can't imagine why either of those would use so much data.

Thanks in advance....

You're lumping a few different things into a single question here it seems. What exactly are you asking?

Text fields - simple answer the table was created a long time ago before varchar(max) existed. I ended up not changing it later because of the potential issues with older ODBC drivers not being able to read the varchar(max) fields as memo fields. I haven't looked into it recently with the current ODBC drivers, but if that works you can just change the fields yourself - Web Connection shouldn't care about whether the data comes from a text or varchar(max) field (although the SQL engine might truncate on some random length value).

If I recall correctly the problem is that VarChar(max) is that FoxPro will map to a text data field, so if the length of any result is > 255 it will fail. With text it'll map to a Memo field and that will always work no matter how wide the data. Again this was some time ago and it might very well be that the ODBC driver returns the data in a way that FoxPro can make a better guess about the result data type. You have to experiment to see what works with your data.

I know the Text field works and is reliable so I don't see much need to change it and second guess, especially since this is a log table.

+++ Rick ---

My main question is why the ip and browser fields were created as text fields rather than something smaller – basically if there would be any problems if I changed them to nvarchar(something < 4000).

Microsoft has been saying since like 2005 that text/ntext will be going away, so we don’t want to change to use that data type. We’ve been using nvarchar(max) with no problem, but with later ODBC drivers VFP returns empty strings for them.

Thanks!

Yes you can change them to anything you want.

AFAIK, IP Address is not a text field.

This is the current SQL to create the table (there have been recent changes in lengths but not type):

CREATE TABLE [dbo].[wwrequestlog](
	[time] [datetime] NOT NULL,
	[reqid] [varchar](25) NOT NULL,
	[script] [varchar](50) NOT NULL,
	[querystr] [varchar](1024) NOT NULL,
	[verb] [varchar](10) NOT null,
	[duration] [numeric](7, 3) NOT NULL,
	[remoteaddr] [varchar](16) NOT NULL,
	[memused] [varchar](15) NOT NULL,
	[error] [bit] NOT NULL,
	[reqdata] [text] NOT NULL,
	[browser] [varchar](255) NOT NULL,
	[referrer] [varchar](255) NOT NULL,
	[result] [text] NOT NULL,
	[account] [varchar](50) NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The reason as I said is that FoxPro's max char size is 255 and if the length is greater than 255 (which can happen for browser strings) there's a problem. Not sure about the IP Address - that can varchar(40) or whatever (for IP v6 IPs).

+++ Rick ---