[AccessD] Nvarchar(max)

Ryan W wrwehler at gmail.com
Thu Jul 1 08:31:47 CDT 2021


There have been some security patches for Access lately... I wonder if one
of those updates managed impact it.  Did you try to roll back any recent
Access updates just for troubleshooting purposes?

On Thu, Jul 1, 2021 at 8:29 AM Anita Smith <anita at ddisolutions.com.au>
wrote:

> I thought it was the data too. I deleted the table and recreated it
> (several times) - no cigar.
>
> What stumped me was that my 2 year old development database and Access
> front had exactly the same problem. This is the database (with data) that I
> first deployed on site and that they've been using since without a glitch.
>
> It nags me a bit that I had to install SQL Server 19 to restore the site
> db for troubleshooting. This may have contaminated my SQL server 17 that I
> was using before today and thus produced the same issue in my old
> Application/Database. I kick myself that I didn't check my current setup
> before installing the latest SQL server.
>
> I do have another computer where I can test that though. Tomorrow is
> another day.
>
> Anita
>
>
>
> Anita Smith
> M: 0402 022 462
>
> -------- Original message --------
> From: Jim Dettman via AccessD <accessd at databaseadvisors.com>
> Date: 1/7/21 23:15 (GMT+10:00)
> To: 'Access Developers discussion and problem solving' <
> accessd at databaseadvisors.com>
> Cc: Jim Dettman <jimdettman at verizon.net>
> Subject: Re: [AccessD] Nvarchar(max)
>
>
>  Keep in mind that it may be the data that was driving it.
>
>  ODBC timeout just means the operation exceeded the timeout.   With a
> default of 60 seconds, it's easy for data to grow and something that worked
> before works no longer.   Changing the data type may have just changed the
> operation, making it faster than it was, rather than there being a problem
> specifically with nvarchar(max).
>
> Jim.
>
> -----Original Message-----
> From: AccessD On Behalf Of Anita Smith
> Sent: Thursday, July 1, 2021 9:01 AM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Nvarchar(max)
>
> Hi Gustav,
>
> I'll get back to you tomorrow my time. I should be able to reproduce it.
> Maybe knock up a demo sample and db.
>
> One thing I did notice was that it only appeared if the continous form was
> filtered / a parent-child linked subform. I could open the form on it's own
> without the parent and not encounter the error. I could open the table
> directly - make data changes and not encounter the error.
>
> Also, the problem was somewhat random but would most certainly appear when
> testing for a few minutes. I'd open the parent form with the continous
> subform and randomly make changes to the data in the subform and bingo -
> error.
>
> I have tried so many various things all day that I've almost lost track of
> what I did, but I restored the site database 3 times after finding the
> issue
> - and then changed the field size - and tested the subform again. just to
> make sure I had found the problem.
>
> I do hope that that was indeed the issue and not a fluke. The last thing I
> need is to wake up to the same problem tomorrow.
>
> Anita
>
>
>
> Anita Smith
>
>
> [cid:DDi-Solutions-Email-Logo_fdcff211-839d-4fd1-865e-c500dea56b8a.png]<wwwd
> disolutions.com.au>        M: 0402 022 462
> E: anita at ddisolutions.com.au<mailto:anita at ddisolutions.com.au>
> Custom Software, Databases and Websites
> P Please consider the environment before printing this email.
> The content of this message is confidential. If you have received it by
> mistake, please inform us by an email reply and then delete the message. It
> is forbidden to copy, forward, or in any way reveal the contents of this
> message to anyone. The integrity and security of this email cannot be
> guaranteed over the Internet. Therefore, the sender will not be held liable
> for any damage caused by the message.
>
> -------- Original message --------
> From: Gustav Brock via AccessD <accessd at databaseadvisors.com>
> Date: 1/7/21 22:16 (GMT+10:00)
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Cc: Gustav Brock <gustav at cactus.dk>
> Subject: Re: [AccessD] Nvarchar(max)
>
> Hi Anita
>
> Could you please provide as much info as possible to reproduce this issue -
> Access version/build etc?
> A (link to) demo/sample would, of course, be perfect though probably
> difficult for you to provide.
>
> I've reported your findings to the team, but they can't reproduce it:
>
> ---
> I setup a table in SQL Server with a nVarChar(Max) column, linked that
> table
> to Access using the ODBC Driver 17 for SQL Server and then created a
> multiple items form for that table but I'm not seeing an issue. I've tried
> working with strings less than 8000 characters and over 8000. Please
> provide
> any further specifics to get this to reproduce or share a reproduction
> sample for us to test.
> ---
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På
> vegne af Anita Smith
> Sendt: 1. juli 2021 13:11
> Til: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Emne: [AccessD] Nvarchar(max)
>
> Hi Group,
>
> Just a short note to let you know that I have spent most of the day
> troubleshooting an application that have been in use for well over a year
> that has suddenly stopped working in one area.
>
> After a lengthy process of elimination I have worked out that using
> nvarchar(max) fields on continuous forms have stopped working (the screen
> would freeze and throw an ODBC query timeout error).
>
> There must have been an update in the last week to have caused this as the
> app on site and my old development app here both encountered the same issue
> I also found the same issue in various versions of ODBC as well as various
> versions of SQL Server- yes, I really have been through the wringer today,
> trying everything under and over the sun.
>
> Anyhow, change the field to something else and the problem is solved.
>
> Beam me up Scotty!
>
> Anita
>
>
>
> Anita Smith
>
> M: 0402 022 462
> E: anita at ddisolutions.com.au
>
> Custom Software, Databases and Websites
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list