DB Error on SQLSelect - Error retrieving large column values

  1. 5 years ago

    Taylor B

    29 Sep 2014 New York, NY

    Hi,

    Has anyone seen this error before when running a SQL Select?
    -image-

    I went to the webpage suggested by the error, but essentially just found a bunch of people complaining to Microsoft to "fix the bug." All I'm doing is the following:

    1. Storing this SQL in a constant
    2. Connecting to a MS SQL Server
    3. After connection, running a SQLSelect on the database with this query
    4. Attempting to receive a RecordSet back to parse.

    I run about 25 other queries to the same database on the same server in the same fashion via threads and timers, but this particular one is causing an issue. One would assume then that it's the SQL query itself, however when running it in SQL Management Studio I have no issues.

    Thanks!

    I have switched to using the ODBC plugin to avoid this problem. See Feedback Case #30780

  2. Wayne G

    29 Sep 2014 Pre-Release Testers, Xojo Pro Answer New Zealand axisdirect.nz

    I have switched to using the ODBC plugin to avoid this problem. See Feedback Case #30780

  3. Norman P

    29 Sep 2014 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...
    Edited 5 years ago

    Makes me feel not so bad that a big company like MS can't address this one in something less than a decade :P
    https://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client
    And thats the cause of the "bug" in Xojo - we use MS native client

  4. Taylor B

    29 Sep 2014 New York, NY

    Haha, Wayne, thanks for your help. That worked perfectly!

  5. Wayne G

    29 Sep 2014 Pre-Release Testers, Xojo Pro New Zealand axisdirect.nz

    @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.

  6. 3 years ago

    Eduardo G

    2 Mar 2017 Pre-Release Testers Europe (Madrid, Spain)

    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 :-|

  7. 2 years ago

    Frank M

    31 Jul 2017 Pre-Release Testers, Xojo Pro Asheville, NC

    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.

  8. Jim B

    24 Aug 2017 Pre-Release Testers, Xojo Pro Uckfield, England

    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.

  9. Kevin C

    29 Sep 2017 Pre-Release Testers Georgia, USA

    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.

  10. Joost R

    29 Sep 2017 Pre-Release Testers, Xojo Pro The Netherlands

    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.

  11. 8 months ago

    Aaron S

    30 Dec 2018 Pre-Release Testers, Xojo Pro San Diego, CA

    @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.

or Sign Up to reply!