Microsoft SQL Server Native Client 11.0

[Nigel Gomm]

can anyone point me to the redistributable .msi for this?

the only SQLncli.msi i can find is 10.0.

tia

nigel

 

[atlopes]

Check

https://www.microsoft.com/en-us/download/details.aspx?id=36434.

 

[Nigel Gomm]

yes... i downloaded this ODBC variant and am testing it now; it seems i can distribute that one easily enough.

Are there any practical differences between the ODBC and Native drivers (using SPT)? Everything seems to be working the same so far...

n

 

[Olaf Doschke]

I don't know about all versions of native client (you will use the ODBC variant anyway), but some have problems with retrieving Varchar(MAX) as C(0), really C(0) - though not an allowed VFP field type, if you try to create a table or cursor with a length 0 char field, the VFPmanaged to do this with native client drivers. And that's a showstopper, as you often need that field type for longer texts to map to Memo.

But the download you are pointed to is a new series of drivers simply named ODBC 10,11,12,13... They are fine, I think. This is not native client driver series.

If you absolutely need the native client (I doubt that), see here:

https://www.connectionstrings.com/download-sql-server-native-client/

It's in the SQL2012 Feature Pack near the bottom of that page.

Bye, Olaf.

 

[Nigel Gomm]

Olaf, that link downloads version 10 of the native client alas.

I had switched to the native version 11 because it was better with date columns. But this version 11 of the ODBC driver does the same so i think this will do me.

n

 

[atlopes]

Native Client is how Microsoft is distributing ODBC connectors to SQL, currently (for a few years already, actually). New versions of the ODBC drivers are required to access new features of Microsoft's SQL Server, but if you don't require such access, you can operate even with the old SQL Server ODBC driver pre-Native Client series.

Your decision will have an impact on how your data goes between VFP and MSSQL server. I posted this simple checker on another forum (UT, now LevelExtreme) but don't know how to point it directly, so I repost it here:

LOCAL MSSQLStmt AS String
CLEAR

SET VARCHARMAPPING ON

* set your server and credentials (as strings)
#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.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 nvarchar(max)) AS t_nvarcharmax
ENDTEXT

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)
* edited
MSSQL2VFP_Types("ODBC Driver 11 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)

IF m.ODBC != -1
	?
	? m.Driver FONT "Arial",14 STYLE "B"
	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"))
		?? " = [" + TRANSFORM(EVALUATE(FIELD(m.Cols, "curCols"))) + "]"
	ENDFOR
	SQLDISCONNECT(m.ODBC)
	
	WAIT WINDOW "Click to continue..."
ENDIF

ENDPROC

Edited: so it looks that Microsoft singled out the ODBC component of its SQL Native Client. The connector behavior, nevertheless, seems to be on par with the one that comes inside the NC package (that is, the one that integrates OLEDB API, also).

 

[Olaf Doschke]

Nigel, are you sure you read what I said?
I made the download and it installs NC 11.0.2100.60, which is not the newest, but 11. Windows Updates would upgrade from there, I assume.

Bye, Olaf.

 

[Olaf Doschke]

I recently used MS ODBC Driver 11and 13 for SQL Server and it turns out this is yet another series of ODBC drivers, in which a Varchar(MAX) column is received as C(0) field instead of Memo in VFP. I don't think VFP will ever be regarded as a client of MSSQL data anymore. The only series of ODBC drivers working is the classic series (driver={SQL Server}) - SQLSRV32.DLL

I look for a download of this or even a newer version, just notice all the other drivers have the Varchar(MAX)/memo issue.

Bye, Olaf.

 

[atlopes]

Devart ODBC Driver for SQL Server" works with no problems reading MSSQL data types for VFP, including DATE (D), DATETIME2 (T), TIME (T), DATETIMEOFFSET (T), that "SQL Server" and even some of the new drivers can't handle, and VARCHAR(MAX) (M), that the most recent return as C(0).

CURSORSETPROP("MapVarchar", .F., 0)

TEXT

Devart ODBC Driver for SQL Server                      

DATE................D  = [05/15/17]
DATETIME............T = [05/15/17 03:25:57 PM]
DATETIME2...........T = [05/15/17 03:25:57 PM]
SMALLDATETIME.......   T = [05/15/17 03:26:00 PM]
TIME................T = [12/30/99 03:25:57 PM]
DATETIMEOFFSET......   T = [05/15/17 03:25:57 PM]
FLOAT...............N  = [1.10]
REAL................N  = [1.10]
NUMERIC10_2......... N  = [1.10]
BIT.................L = [.T.]
CHAR10..............C  = [Žižek     ]
VARCHAR10...........C  = [Žižek     ]
TEXT................M  = [Žižek]
CHAR256.............M  = [Žižek
                      
                      ]
VARCHAR256.......... M  = [Žižek]
VARCHARMAX..........  M  = [Žižek]
NCHAR10.............C  = [Žižek     ]
NVARCHAR10.......... C  = [Žižek     ]
NTEXT...............M  = [Žižek]
NCHAR256............M  = [Žižek
                      
                      ]
NVARCHAR256.........  M  = [7D0169007E0165006B00]
NVARCHARMAX.........   M  = [Žižek]

ENDTEXT

CURSORSETPROP("MapVarchar", .T., 0)

TEXT

Devart ODBC Driver for SQL Server                     

DATE................D  = [05/15/17]
DATETIME............T = [05/15/17 03:26:17 PM]
DATETIME2...........T = [05/15/17 03:26:17 PM]
SMALLDATETIME.......   T = [05/15/17 03:26:00 PM]
TIME................T = [12/30/99 03:26:17 PM]
DATETIMEOFFSET......   T = [05/15/17 03:26:17 PM]
FLOAT...............N  = [1.10]
REAL................N  = [1.10]
NUMERIC10_2......... N  = [1.10]
BIT.................L = [.T.]
CHAR10..............C  = [Žižek     ]
VARCHAR10...........C  = [Žižek]
TEXT................M  = [Žižek]
CHAR256.............M  = [Žižek
                      
                      ]
VARCHAR256.......... M  = [Žižek]
VARCHARMAX..........  M  = [Žižek]
NCHAR10.............C  = [Žižek     ]
NVARCHAR10.......... C  = [Žižek]
NTEXT...............M  = [Žižek]
NCHAR256............M  = [Žižek
                      
                      ]
NVARCHAR256.........  M  = [7D0169007E0165006B00]
NVARCHARMAX.........   M  = [Žižek]

ENDTEXT