>>Hi everybody,
>>
>>We're using SQL Server driver with our ODBC connection because of some issues.
>>
>>This driver seems to being deprecated and does't work for TLS 1.2.
>>
>>I forgot exact problems which led us to using this driver, can someone please summarize what are the problems with each of the driver and is there a workaround?
>>
>>We're using varchar(max) / varbinary(max) and text data types, but we're not using dates.
>>
>>Thanks in advance.
>
>Naomi,
>
>You can use this tester to see the effect of different drivers while fetching SQL Server data. It's a revised version of another one that I already posted here.
>
>As far as I can tell, and so far, the most comprehensive driver seems to be the Devart one. Although the "original" SQL Server driver fails to correctly map some types, at least it can fetch the data - whatever type they may have - as character.
>
>

>LOCAL MSSQLStmt AS String
>CLEAR
>
>CURSORSETPROP("MapBinary",.T.,0)
>CURSORSETPROP("MapVarchar",.T.,0)
>
>* set your server and credentials
>#DEFINE SERVER_NAME	""
>#DEFINE USER_LOGIN	""
>#DEFINE USER_PWD		""
>
>TEXT TO m.MSSQLStmt NOSHOW
>SELECT CAST(GETDATE() AS date) as t_date, 
>	CAST(GETDATE() AS datetime) as t_datetime,
>	CAST(GETDATE() AS datetime2) as t_datetime2,
>	CAST(GETDATE() AS smalldatetime) AS t_smalldatetime,
>	CAST(GETDATE() AS time) AS t_time,
>	CAST(GETDATE() AS datetimeoffset) AS t_datetimeoffset,
>	CAST(1 AS bigint) AS t_bigint,
>	CAST(1.1 AS float) AS t_float, 
>	CAST(1.1 AS real) AS t_real,
>	CAST(1.1 AS numeric(10,2)) AS t_numeric10_2,
>	CAST(1 AS bit) AS t_bit,
>	CAST('Žižek' AS char(10)) AS t_char10,
>	CAST('Žižek' AS varchar(10)) AS t_varchar10,
>	CAST('Žižek' AS text) AS t_text,
>	CAST('Žižek' AS char(256)) AS t_char256,
>	CAST('Žižek' AS varchar(256)) AS t_varchar256,
>	CAST('Žižek' AS nchar(10)) AS t_nchar10,
>	CAST('Žižek' AS nvarchar(10)) AS t_nvarchar10,
>	CAST('Žižek' AS ntext) AS t_ntext,
>	CAST('Žižek' AS nchar(256)) AS t_nchar256,
>	CAST('Žižek' AS nvarchar(256)) AS t_nvarchar256,
>	CAST('Žižek' AS varchar(max)) AS t_varcharmax,
>	CAST('Žižek' AS nvarchar(max)) AS t_nvarcharmax,
>	CAST('Žižek' AS varbinary(max)) AS t_varbinary,
>	NEWID() AS t_uniqueidentifier
>ENDTEXT
>
>* uncomment where needed, add new drivers if installed
>MSSQL2VFP_Types("SQL Server", m.MSSQLStmt)
>MSSQL2VFP_Types("SQL Server Native Client 10.0", m.MSSQLStmt)
>MSSQL2VFP_Types("SQL Server Native Client 11.0", m.MSSQLStmt)
>MSSQL2VFP_Types("ODBC Driver 13 for SQL Server", m.MSSQLStmt)
>MSSQL2VFP_Types("ODBC Driver 17 for SQL Server", m.MSSQLStmt)
>MSSQL2VFP_Types("Devart ODBC Driver for SQL Server", m.MSSQLStmt)
>
>PROCEDURE MSSQL2VFP_Types (Driver AS String, SQLStmt AS String)
>LOCAL ODBC AS Integer
>LOCAL Cols AS Integer
>
>m.ODBC = SQLSTRINGCONNECT("Driver={" + m.Driver + "};Server=" + SERVER_NAME + ";" + ;
>									"Uid=" + USER_LOGIN + ";Pwd=" + USER_PWD)
>
>?
>? m.Driver FONT "Arial",14 STYLE "B"
>
>IF m.ODBC != -1
>	SQLEXEC(m.ODBC, m.SQLStmt, "curCols")
>	FOR m.Cols = 1 TO FCOUNT("curCols")
>		? PADR(SUBSTR(FIELD(m.Cols, "curCols", 0), 3), 20, ".")
>		?? TYPE(FIELD(m.Cols, "curCols"))
>		?? " = [" + IIF(TYPE(FIELD(m.Cols, "curCols"))=="G","general",TRANSFORM(EVALUATE(FIELD(m.Cols, "curCols")))) + "] / " + ;
>			TRANSFORM(FSIZE(FIELD(m.Cols, "curCols"), "curCols"))
>	ENDFOR
>	SQLDISCONNECT(m.ODBC)
>	
>	WAIT WINDOW "Click to continue..."
>ELSE
>	? "Not available..."
>	?
>ENDIF
>
>ENDPROC
>

Hi Antonio,

PMFJI. I have a couple of questions, please. Currently on my computer I have only the legacy (original) SQL Server ODBC driver. Which works with varchar(max) (which is very important for me) and does not work with the new Date type (which is not important to me). So far, my customers use the correct ODBC driver, so I have no problem.

If a customer upgrades their system or changes their ODBC driver to the, for example, SQL Server Native Client 10.0, which with VFP 9 does not support varchar(max), my app will fails (big problem!!!). In this case, what are my options:
1. Can a customer install the legacy ODBC driver even though they have a new driver?
or
2. Will Devart driver work with the VFP 9 application allowing the app continue using the varchar(max) columns?

Thank you.