none
Error msg while running relatively simple query (large result set)

    Question

  • While running query below on SQL Server 2005 (Build 3790: Service Pack 2):

     

    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 followed by

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

     

    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

    Tuesday, July 17, 2007 6:42 PM

All replies

  • I have the same problem. Security table contains 8 Million+ rows Groups table about 200 rows.  All columns are defined as integer.

    This is ridiculous - I'll think I'll try it in Oracle.

     

    SELECT GROUPS.GROUP_ID, SECURITY.THING, SECURITY.PERSONORGROUP, SECURITY.ACCESSRIGHTS

    FROM GROUPS RIGHT OUTER JOIN

    SECURITY ON GROUPS.SYSTEM_ID = SECURITY.PERSONORGROUP

    WHERE (GROUPS.GROUP_ID IS NULL)

     

    Regards

    DB

    Thursday, July 19, 2007 12:09 AM
  • Actually query is coming from analysis service package (building a dimension) and errored out there.  I ran it in SSMS and received the error as indicated.  In the past when I worked with large result sets usually they were placed in tempdb and/or paged.  Is this a new M$ 'feature'.

     

    Mike Byrd

     

    PS:  You know after thinking about it, I'm more disgrunted about the query failing in SSMS than getting the error in analysis services -- guess it is kind of dumb to have a dimension with 10.5m discrete values in a cube. 

    Friday, July 20, 2007 6:09 PM