[AccessD] Nvarchar(max)

[Jim Dettman]

 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