Re: Memo field Truncated

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 06:26:24 -0400

"Unicorn" <unicorn@somewhere.com> wrote in message
news:%23K8eVVWvEHA.3624@TK2MSFTNGP09.phx.gbl...
>
> "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
> news:30c2o0p96d1f77hijejh2kvsh36iud9sk3@4ax.com...
> > On Thu, 28 Oct 2004 19:12:05 +0930, "Unicorn" <unicorn@somewhere.com>
> wrote:
> >
> > ¤ Simmy, do you have a Distinct keyword in there by any chance?
Distinct
> is
> > ¤ the usual culprit in having memo fields turn into 255 character text
> fields
> > ¤ in a query.
> > ¤
> > ¤ Matt
> >
> > I haven't had a chance to repro this yet but he's joining a table to a
> union sub query:
> >
> Then that is the problem!!!!
> There are known issues with union sub queries causing Memo Fields to
> truncate. Something to do with implicit sorting and memo fields not being
> sortable and so converted on the fly to text... Hence 255 limitation.
>
> There is some evidence that JET 4.0 SP6 and above will reduce these sorts
of
> problems, but there is nothing firm.
>
> Office 2000 SP2 increased the maxfieldlength to 50,000. But Microsoft
does
> not say how this was done, or if some registry hacking could fix it
without
> the service pack.
>
> To cure the problem, make all left joins into joins and them apply the
> necessary logic in the where clause to shed any excess records.

I believe using UNION ALL, rather than UNION, may solve the problem of Union
queries truncating. UNION eliminates duplicates, meaing that sorting is
required, and I think it's the sorting that causes the truncation. 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Relevant Pages

  • Re: Sorting Order by Table?
    ... | some PL/SQL code and opened via REF CURSOR. ... | of criteria to the WHERE clause. ... select statement with some sort of UNION and weird sorting. ...
    (comp.databases.oracle.misc)
  • Re: Sorting Order by Table?
    ... I want to add another table to the FROM clause and another set ... | of criteria to the WHERE clause. ... select statement with some sort of UNION and weird sorting. ...
    (comp.databases.oracle.misc)
  • Re: UNION and performance?
    ... I wouldn't have really thought about the sorting of result ... SQL Server MVP ... Always use UNION ALL because it doesn't ... >>> require sorting and removal of matching rows. ...
    (microsoft.public.sqlserver.programming)
  • Re: UNION and performance?
    ... Always use UNION ALL because it doesn't ... >> require sorting and removal of matching rows. ... >> David Gugick ...
    (microsoft.public.sqlserver.programming)
  • Re: why is query sorted.
    ... There are actually several algorithms that SQL Server can use to remove ... duplicates, and sorting after combining the rows is one of those algorithms, ... predict what union algorithm will be used. ... Or, as Adam suggested, if you use UNION ALL, you are telling SQL Server NOT ...
    (microsoft.public.sqlserver.programming)