BUG: Constant crash in Access 2007 - URGENT!

Elad · Nov 6, 2007

Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises the
following exception:

"Attempted to read or write protected memory. This is often an indication
that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],
(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]))
AS [4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]))
AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]))
AS [6B362FBC-5EF8-42EC-9533-69646F95E2F7],
(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]))
AS [A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])
AS [4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])
AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])
AS [6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])
AS [A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY]
FROM (SELECT ([M2].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],
([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([MV_TABLE].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT (COUNT([M1].[MV_5EA2DD]))
AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED])
AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])
AS [97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])
AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]) AS [M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD] >
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED])
AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])
AS [97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])
AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]) AS
[MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Elad · Nov 6, 2007

This is the same problem with the same type of SQL executed against the
Customers table in the Northwind sample database which comes with Access
2007.

Sorry for the ugliness, this is machine generated SQL.

SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]))
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANY],
([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])
AS [CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT
([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([MV_TABLE].[CITYCITY])
AS [CITYCITY],
([MV_TABLE].[MV_C2788E])
AS [MV_C2788E],
((SELECT
(COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0])
AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[COMPANYCOMPANY] = [MV_TABLE].[COMPANYCOMPANY]
AND
[M1].[MV_C2788E] > [MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY])
AS [CITYCITY],
([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0])
AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY])
AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS
[M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]
ON ([CUSTOMERS].[COMPANYCOMPANY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY])
AND ([CUSTOMERS].[CITYCITY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [COMPANYCOMPANY] ASC,
[CITYCITY] ASC

Elad said:
Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises
the following exception:

"Attempted to read or write protected memory. This is often an indication
that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],

(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],

([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK]
= '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT ([M2].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([MV_TABLE].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT
(COUNT([M1].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]) AS
[M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD] >
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]) AS
[MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Sylvain Lafontaine · Nov 6, 2007

I would say that you might have reach the limit of JET. Try to simplify
your statement by using temporary tables or switch to SQL-Server to see if
you would get a better result.

You can also try to add a semi-coma ( ; ) at the end of your statement as
I've seen posts where this has helped in reducing the crashes for some
complexe queries. (Don't know if it's true but it doesn't hurt to try it.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Elad said:
This is the same problem with the same type of SQL executed against the
Customers table in the Northwind sample database which comes with Access
2007.

Sorry for the ugliness, this is machine generated SQL.

SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANY],
([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT
([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([MV_TABLE].[CITYCITY])
AS [CITYCITY],

([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT
(COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[COMPANYCOMPANY] = [MV_TABLE].[COMPANYCOMPANY]
AND
[M1].[MV_C2788E] > [MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS
[M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]
ON ([CUSTOMERS].[COMPANYCOMPANY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY])
AND ([CUSTOMERS].[CITYCITY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [COMPANYCOMPANY] ASC,
[CITYCITY] ASC

Elad said:

Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises
the following exception:

"Attempted to read or write protected memory. This is often an indication
that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],

(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],

([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([M2].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([MV_TABLE].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT
(COUNT([M1].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD] >
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP
BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Click to expand...

Elad · Nov 6, 2007

Sylvian,

Thanks for your reply.

Does Access 2007 still use Jet? I thought it was replaced by something
else.

The semi-comma idea didn't help.

As far as reaching the limit of the query engine - this shouldn't cause
Access to crash, it should just overflow the SQL parser. So whatever way
you look at it, it's a bug.

Regardless, I don't think that's the problem because query A (which is a
subquery from the initial query I posted) crashes also, while query B
doesn't. They are fairly similar in complexity (neither have joins and both
have a calculated column). The only difference is in the number of columns
the tables have. In query A the tables have two columns, in query B just
one column.

QUERY A
========

SELECT ([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([MV_TABLE].[CITYCITY]) AS [CITYCITY],
([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT (COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0])
AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY])
AS [CITYCITY],
(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]))
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITY])
AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],
([CUSTOMERS].[CITY])
AS [CITY],
([CUSTOMERS].[ID])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
[CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY])
AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE [M1].[COMPANYCOMPANY] =
[MV_TABLE].[COMPANYCOMPANY]
AND [M1].[MV_C2788E] >
[MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0])
AS [MV_C2788E]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],
(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]))
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITY])
AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],
([CUSTOMERS].[CITY])
AS [CITY],
([CUSTOMERS].[ID])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
[CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS
[CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]

QUERY B
=======

SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]))
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])
AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[CITYCITY])
AS [CITYCITY],
([MV_TABLE].[MV_93C96E])
AS [MV_93C96E],
((SELECT
(COUNT([M1].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]) AS [MV_93C96E]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[MV_93C96E] > [MV_TABLE].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76])
AS [MV_93C96E]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]))
AS [9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]
ON ([CUSTOMERS].[CITYCITY] =
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [CITYCITY] ASC

Sylvain Lafontaine said:
I would say that you might have reach the limit of JET. Try to simplify
your statement by using temporary tables or switch to SQL-Server to see if
you would get a better result.

You can also try to add a semi-coma ( ; ) at the end of your statement as
I've seen posts where this has helped in reducing the crashes for some
complexe queries. (Don't know if it's true but it doesn't hurt to try
it.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Elad said:

This is the same problem with the same type of SQL executed against the
Customers table in the Northwind sample database which comes with Access
2007.

Sorry for the ugliness, this is machine generated SQL.

SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANY],
([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([M2].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT
([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([MV_TABLE].[CITYCITY]) AS [CITYCITY],

([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT
(COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[COMPANYCOMPANY] = [MV_TABLE].[COMPANYCOMPANY]
AND
[M1].[MV_C2788E] > [MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS
[M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]
ON ([CUSTOMERS].[COMPANYCOMPANY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY])
AND ([CUSTOMERS].[CITYCITY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [COMPANYCOMPANY] ASC,
[CITYCITY] ASC

Elad said:

Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises
the following exception:

"Attempted to read or write protected memory. This is often an
indication that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],

(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],

([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([M2].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([MV_TABLE].[CATEGORYCATEGORY])
AS [CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT
(COUNT([M1].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD] >
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP
BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Click to expand...

Click to expand...

Elad · Nov 6, 2007

By the way,

The calculated column in this SQL statement is used to calculate the rank of
each member in the grouped table. It basically adds the numbers 1, 2, 3...
to an ordered table by calculating how many customers with sales greater
than the current customer exist in the table.

Since my experience with Microsoft leads me to believe I'm pretty much
screwed on this issue (at least until Access 2009), I'll settle for a
working alternative to achieve this, using purely SQL (I cannot modify the
underlying tables).

Thanks a lot.

Elad said:
Sylvian,

Thanks for your reply.

Does Access 2007 still use Jet? I thought it was replaced by something
else.

The semi-comma idea didn't help.

As far as reaching the limit of the query engine - this shouldn't cause
Access to crash, it should just overflow the SQL parser. So whatever way
you look at it, it's a bug.

Regardless, I don't think that's the problem because query A (which is a
subquery from the initial query I posted) crashes also, while query B
doesn't. They are fairly similar in complexity (neither have joins and
both have a calculated column). The only difference is in the number of
columns the tables have. In query A the tables have two columns, in query
B just one column.

QUERY A
========

SELECT ([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([MV_TABLE].[CITYCITY]) AS [CITYCITY],
([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT (COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
[CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE [M1].[COMPANYCOMPANY] =
[MV_TABLE].[COMPANYCOMPANY]
AND [M1].[MV_C2788E] >
[MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY])
AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS])
AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS
[CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]

QUERY B
=======

SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[CITYCITY])
AS [CITYCITY],

([MV_TABLE].[MV_93C96E]) AS [MV_93C96E],
((SELECT
(COUNT([M1].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]) AS [MV_93C96E]
FROM
(SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[MV_93C96E] > [MV_TABLE].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]) AS [MV_93C96E]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM (SELECT
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]
ON ([CUSTOMERS].[CITYCITY] =
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [CITYCITY] ASC

Sylvain Lafontaine said:

I would say that you might have reach the limit of JET. Try to simplify
your statement by using temporary tables or switch to SQL-Server to see if
you would get a better result.

You can also try to add a semi-coma ( ; ) at the end of your statement as
I've seen posts where this has helped in reducing the crashes for some
complexe queries. (Don't know if it's true but it doesn't hurt to try
it.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Elad said:

This is the same problem with the same type of SQL executed against the
Customers table in the Northwind sample database which comes with Access
2007.

Sorry for the ugliness, this is machine generated SQL.

SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANY],
([CUSTOMERS].[CITY]) AS
[CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS])
AS [CUSTOMERS]
GROUP BY [CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([M2].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT
([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([MV_TABLE].[CITYCITY]) AS [CITYCITY],

([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT
(COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]

FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[COMPANYCOMPANY] = [MV_TABLE].[COMPANYCOMPANY]
AND
[M1].[MV_C2788E] > [MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE])
AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]
ON ([CUSTOMERS].[COMPANYCOMPANY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY])
AND ([CUSTOMERS].[CITYCITY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])) AS
[CUSTOMERS]
ORDER BY [COMPANYCOMPANY] ASC,
[CITYCITY] ASC

"Elad" <nospam> wrote in message
Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises
the following exception:

"Attempted to read or write protected memory. This is often an
indication that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],

(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],

([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([M2].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([MV_TABLE].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT
(COUNT([M1].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD]
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP
BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Click to expand...

Click to expand...

Sylvain Lafontaine · Nov 6, 2007

Yes, Access is still using JET.

You could try replacing your aliases likes
[ADBF8FA9-9139-4D7B-9017-988280DED7AF] with something shorter to see if it's
help.

You are also using the same GUID for multiples aliases. Maybe you should
try using a different alias for each level of your recursive query.

All in all, instead of trying to create the ultimate query, why don't you
use temporary tables to compute your stuff^

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Elad said:
Sylvian,

Thanks for your reply.

Does Access 2007 still use Jet? I thought it was replaced by something
else.

The semi-comma idea didn't help.

As far as reaching the limit of the query engine - this shouldn't cause
Access to crash, it should just overflow the SQL parser. So whatever way
you look at it, it's a bug.

Regardless, I don't think that's the problem because query A (which is a
subquery from the initial query I posted) crashes also, while query B
doesn't. They are fairly similar in complexity (neither have joins and
both have a calculated column). The only difference is in the number of
columns the tables have. In query A the tables have two columns, in query
B just one column.

QUERY A
========

SELECT ([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([MV_TABLE].[CITYCITY]) AS [CITYCITY],
([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT (COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
[CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE [M1].[COMPANYCOMPANY] =
[MV_TABLE].[COMPANYCOMPANY]
AND [M1].[MV_C2788E] >
[MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS
[CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY])
AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT
([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS])
AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS
[CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]

QUERY B
=======

SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS]) AS
[CUSTOMERS]
GROUP BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([MV_TABLE].[CITYCITY])
AS [CITYCITY],

([MV_TABLE].[MV_93C96E]) AS [MV_93C96E],
((SELECT
(COUNT([M1].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]) AS [MV_93C96E]
FROM
(SELECT ([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP
BY [CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[MV_93C96E] > [MV_TABLE].[MV_93C96E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]) AS [MV_93C96E]
FROM (SELECT
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[9EBFC5D9-75BE-44B2-B671-8E42F138FA76]
FROM (SELECT
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]
GROUP BY
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]) AS
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898]
ON ([CUSTOMERS].[CITYCITY] =
[RANKED_1D82426C-9CF4-41D2-AA99-7CD02EBF4898].[CITYCITY])) AS [CUSTOMERS]
ORDER BY [CITYCITY] ASC

Sylvain Lafontaine said:

I would say that you might have reach the limit of JET. Try to simplify
your statement by using temporary tables or switch to SQL-Server to see if
you would get a better result.

You can also try to add a semi-coma ( ; ) at the end of your statement as
I've seen posts where this has helped in reducing the crashes for some
complexe queries. (Don't know if it's true but it doesn't hurt to try
it.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Elad said:

This is the same problem with the same type of SQL executed against the
Customers table in the Northwind sample database which comes with Access
2007.

Sorry for the ugliness, this is machine generated SQL.

SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],
([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANYCOMPANY],
([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM (SELECT ([CUSTOMERS].[COMPANY]) AS
[COMPANY],
([CUSTOMERS].[CITY]) AS
[CITY],
([CUSTOMERS].[ID]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM [CUSTOMERS]) AS [CUSTOMERS])
AS [CUSTOMERS]
GROUP BY [CUSTOMERS].[COMPANYCOMPANY],
[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]
INNER JOIN (SELECT
([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],

([RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY]) AS [CITYCITY]
FROM (SELECT ([M2].[COMPANYCOMPANY]) AS
[COMPANYCOMPANY],
([M2].[CITYCITY]) AS
[CITYCITY]
FROM (SELECT
([MV_TABLE].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([MV_TABLE].[CITYCITY]) AS [CITYCITY],

([MV_TABLE].[MV_C2788E]) AS [MV_C2788E],
((SELECT
(COUNT([M1].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM
(SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]

FROM (SELECT ([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [M1]
WHERE
[M1].[COMPANYCOMPANY] = [MV_TABLE].[COMPANYCOMPANY]
AND
[M1].[MV_C2788E] > [MV_TABLE].[MV_C2788E])) AS [RANK0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]) AS [MV_C2788E]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[5EA22D82-A414-4D12-A7EB-2EFD2A1070A0]
FROM (SELECT
([CUSTOMERS].[COMPANYCOMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITYCITY]) AS [CITYCITY],

(COUNT([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF])) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]
FROM
(SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANYCOMPANY],

([CUSTOMERS].[CITY]) AS [CITYCITY],

([CUSTOMERS].[ADBF8FA9-9139-4D7B-9017-988280DED7AF]) AS
[ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM (SELECT ([CUSTOMERS].[COMPANY]) AS [COMPANY],

([CUSTOMERS].[CITY]) AS [CITY],

([CUSTOMERS].[ID]) AS [ADBF8FA9-9139-4D7B-9017-988280DED7AF]

FROM [CUSTOMERS]) AS [CUSTOMERS]) AS [CUSTOMERS]

GROUP BY [CUSTOMERS].[COMPANYCOMPANY],

[CUSTOMERS].[CITYCITY]) AS [CUSTOMERS]) AS [CUSTOMERS]) AS [MV_TABLE])
AS [M2]
WHERE [M2].[RANK0] < 2) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]) AS
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449]
ON ([CUSTOMERS].[COMPANYCOMPANY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[COMPANYCOMPANY])
AND ([CUSTOMERS].[CITYCITY] =
[RANKED_9E6F09AF-30C9-46B7-8A96-39DC7635F449].[CITYCITY])) AS
[CUSTOMERS]
ORDER BY [COMPANYCOMPANY] ASC,
[CITYCITY] ASC

"Elad" <nospam> wrote in message
Hi,

The enclosed SQL statement causes Access 2007 to crash completely.

Exeuting this SQL statement from Ado.Net (using OldDbConnection) raises
the following exception:

"Attempted to read or write protected memory. This is often an
indication that other memory is corrupt."

PLEASE HELP!

Thanks in advance,

Elad

----

The SQL statement is:

SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY],

(SUM([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D])) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

(SUM([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7])) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

(SUM([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416])) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],
(YEAR([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],
(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],
(MONTH([RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([RAWDATA].[CATEGORY]) AS
[CATEGORYCATEGORY],

([RAWDATA].[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],

([RAWDATA].[6B362FBC-5EF8-42EC-9533-69646F95E2F7]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],

([RAWDATA].[A2DB7F35-3409-4286-AA78-2BF3733AD416]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],
([RAWDATA].[CATEGORY]) AS
[CATEGORY],
([RAWDATA].[DIT]) AS
[4BC5D3AD-DADA-479A-A01F-BC9E6E4BA24D],
([RAWDATA].[ID]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23],
([RAWDATA].[NUMBEROFLINES]) AS
[6B362FBC-5EF8-42EC-9533-69646F95E2F7],
([RAWDATA].[RUNID]) AS
[A2DB7F35-3409-4286-AA78-2BF3733AD416]
FROM [RAWDATA]) AS [RAWDATA]) AS
[RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],
[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],
[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS
[RAWDATA]
INNER JOIN (SELECT
([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AS [DATESTIME_MONTHS2DATESTIME_YEARS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AS [DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([M2].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([M2].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([M2].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],
([M2].[CATEGORYCATEGORY]) AS
[CATEGORYCATEGORY]
FROM (SELECT
([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([MV_TABLE].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],
([MV_TABLE].[MV_5EA2DD]) AS
[MV_5EA2DD],
((SELECT
(COUNT([M1].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]

WHERE ([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')

GROUP BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [M1]
WHERE
[M1].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_MONTHS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_QUARTERS]
AND
[M1].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[MV_TABLE].[DATESTIME_MONTHS2DATESTIME_YEARS]
AND [M1].[MV_5EA2DD]
[MV_TABLE].[MV_5EA2DD])) AS [RANK0]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]) AS [MV_5EA2DD]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[97E4F33A-9FBC-4B43-B701-4E01EFF246ED]
FROM (SELECT
([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS]) AS
[DATESTIME_MONTHS2DATESTIME_YEARS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS]) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS]) AS
[DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORYCATEGORY]) AS [CATEGORYCATEGORY],

(COUNT([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23])) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]
FROM
(SELECT (DATEPART("w",[RAWDATA].[DATES])) AS
[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK],

(YEAR([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_YEARS],

(DATEPART("q",[RAWDATA].[DATES])) AS
[DATESTIME_MONTHS2DATESTIME_QUARTERS],

(MONTH([RAWDATA].[DATES])) AS [DATESTIME_MONTHS2DATESTIME_MONTHS],

([RAWDATA].[CATEGORY]) AS [CATEGORYCATEGORY],

([RAWDATA].[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]) AS
[ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM (SELECT ([RAWDATA].[DATES]) AS [DATES],

([RAWDATA].[CATEGORY]) AS [CATEGORY],

([RAWDATA].[ID]) AS [ACCACC2E-9CAE-4FDD-96A2-2CBC5BD21B23]

FROM [RAWDATA]) AS [RAWDATA]) AS [RAWDATA]
WHERE
([RAWDATA].[DATESTIME_DAYSOFWEEKDATESTIME_DAYSOFWEEK] = '4')
GROUP
BY [RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS],

[RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS],

[RAWDATA].[CATEGORYCATEGORY]) AS [RAWDATA]) AS [RAWDATA]) AS [RAWDATA])
AS [MV_TABLE]) AS [M2]
WHERE [M2].[RANK0] < 3) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]) AS
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD]
ON ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_YEARS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_YEARS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_QUARTERS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_QUARTERS])
AND ([RAWDATA].[DATESTIME_MONTHS2DATESTIME_MONTHS] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[DATESTIME_MONTHS2DATESTIME_MONTHS])
AND ([RAWDATA].[CATEGORYCATEGORY] =
[RANKED_C400E568-7FAA-40D9-B70A-B07DE6008DDD].[CATEGORYCATEGORY])

Click to expand...

Click to expand...