@Norman P And thats the cause of the "bug" in Xojo - we use MS native client

How about you update the fb case changing the status to "Scheduled" with a comment "When Microsoft get around to it". Better than letting it sit there looking bad for Xojo.

And thanks for the update.

I was looking at this issue and it seems this problem can be avoided by setting the bind as DBTYPE_IUNKNOWN and the rowset property as DBPROP_ACCESSORDER
https://msdn.microsoft.com/en-us/library/ms130896.aspx

Problem is these seem to be settable in code in C# but I don't think they're settable in Xojo when using the MS SQL Plugin. I'm trying to avoid requiring ODBC in an app but it doesn't seem possible with this bug here :-|

Today, received the following error from Xojo when performing a SQL SELECT on a table containing a varchar(max) column. The error from Xojo: Error retrieving large column values due to a bug in the SQL Native Client (https://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client ).

With the release of newer SQL Server and Xojo, is there any XOJO native solution using the XOJO SQL plugin? After coding for many months towards the goal of Full Text Searches, any suggestions would be helpful. Thanks.

We use Monkeybread's SQL Plugin using ODBC to connect to MSSQL databases as this is the only solution that does everything we want. This is what we found with each of the options that we looked at:

MSSQLDatabase Xojo Plugin
This plugin does not deal with Large Data Objects. We cannot read and write successfully to these fields using this plugin.

ODBCDatabase Xojo Plugin
This plugin does not handle Lock Timeouts correctly. When a lock timeout occurs, no error is raised and the recordset contains whatever data has successfully been read up to the timeout. It does read and write BLOBs correctly.

Monkeybread SQL Plugin using DBLibrary
This plugin does not handle lock timeout errors from the recordsets correctly. To be clear, we think this is a problem with Microsoft's SQL client, not Monkeybread's plugin.

Monkeybread SQL Plugin using ODBC
Lock timeouts are raised correctly. It does handle BLOBs but you must set the dtb.Option("ODBCAddLongTextBufferSpace") = "false"

Both Monkeybread plugins require that all recordsets are closed before disconnecting from the database. It's also important not to open multiple recordsets at the same time against one database connection. This causes "unpredictable" behaviour. Again this is a Microsoft issue, not a Monkeybread problem.

These findings are from about a year ago so may have been updated.

Hope this is useful.

I've found a work-around for this issue while still using the MSSQLDatabase connector. The key is to use the CONVERT() function inside of SQL Server statement. CAST() does not work.

 Dim sCommand As String = _
  "SELECT wo.wonumber, wo.vehicleid " + _
  ", CONVERT( VARCHAR(2000), woli.ItemCode) AS ItemCode " + _
  ", CONVERT( VARCHAR(2000), woli.ItemType) AS ItemType " + _
  "FROM WorkOrders wo " + _
  " LEFT JOIN WOLineItems woli ON wo.WorkOrderId = woli.WorkOrderId " + _
  "WHERE wo.CreatedOn > '2017-01-01' " + _
  "ORDER BY wo.woNumber, woli.ItemCode"

In my case, I don't have any control over the table structures as it is vertical market product that we've purchased. I'm just trying to create better reporting options using Xojo Web. I hope this helps someone out. It'll probably be me, sometime in the future when I go looking for a solution to this problem that I've already solved way back in 2017. Take care.

As we all know, there is a good alternative for MS SQL server. And it's for free.
Think MS SQL server is loosing popularity.

@Kevin JCully I've found a work-around for this issue while still using the MSSQLDatabase connector. The key is to use the CONVERT() function inside of SQL Server statement. CAST() does not work.

Kevin - thank you! This is the solution that I was looking for. I appreciate this "out of the box" solution vs. just throwing up your hands and going to a plugin or ODBC.