Query failing (large result set)

Last Post 24 Jul 2007 11:39 AM by mbyrdtx. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
mbyrdtx
New Member
New Member

--
24 Jul 2007 08:51 AM
Query below fails when running in SSIS package (SQL Server 2005 (Build 3790: Service Pack 2) derived from dimension package in analysis services) and also fails in SSMS with error as referenced below:

SELECT DISTINCT pkg.PrimaryBarcode
FROM dbo.Package AS pkg (NOLOCK)
JOIN dbo.PackageCycle AS pc (NOLOCK)
ON pkg.PackageKey = pc.PackageKey
WHERE (pkg.BillCycleDateKey >= 20061201) OR (pkg.BillStatusKey = 1)

I received a partial result set (should return about 10.5m rows) followed by

An error occurred while executing batch. Error message is: Couldn't replace text

Error msg from SSIS is a timeout issue (but I really think it is an memory issue).

I suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)

I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.

Cheers,
Mike Byrd
mbyrdtx
New Member
New Member

--
24 Jul 2007 11:39 AM
If I decrease the date range and run the query in SSMS on the server (4gb) instead of 1gb of ram, the query completes without error. But the problem still occurs when I try to process the dimension in analysis server.

Mike
mbyrdtx
New Member
New Member

--
25 Jul 2007 07:42 AM
The server has 4gb ram, but the 3gb switch is not set nor is AWE, so I would guess AS has just 2gb to work in.


Acceptable Use Policy
---