none
Error when trying to pull record set

    Question

  • Set up: SQL Server 2005 on Win 2003 server (prod machine) and same on Win XP (development machine) server

    I have a rather large table with almost 2 million record in it. When I try to

    select * from that_table

    in Managment Studio, text mode new query window, I am getting this error after approximately half of all records in the table are displayed:

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

    This happens on both prod and dev server. I think it is out memory type error but, I do not think it is normal. Or, is it? I tried to pull next record from  that error 

    select * from that_table where pk_field = 520000

    tried to pull all records after the error. 

    select * from that_table where pk_field >= 520000

    It seems to fail approximately after pulling the same amount of records, plus-minus 10,000 - 20,000.

    Tuesday, January 20, 2009 8:22 PM

All replies

  • Who want to display 1 Mio records in SSMS ? As the output and caching on the client can be very ressource intensive, is there a need to do this ?


    -Jens K. Suessmeyer
    Tuesday, January 20, 2009 11:01 PM
  • It is a good question. I dont really need but, performance of this table is a bit on a slow side so, I thought this is a good indicator... or, not?

    This is billing table and each bill run I get to insert about 20,000 new records for each company and I have a handful and, this is a real bottleneck. If I trancate the table to about 100,000 records (I can remove bills that are more than 2 years old but, this becomes more problematic as I get more records for each bill now per year).

    May be the structure of the table is the problem? I would appreciate if someone could advice.
    Here is ddl

    CREATE TABLE [dbo].[EmpBillStatements](

    [empbillstatement_id] [int] IDENTITY(1,1) NOT NULL,

    [billstatementbenefit_id] [int] NOT NULL,

    [billstatementvalue_id] [int] NOT NULL,

    [coverage_amt] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_coverage_amt] DEFAULT ((0)),

    [coverage_category] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [coverage_type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_EmpBillStatement_coverage_type] DEFAULT ('e'),

    [employer_premium] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employer_premium] DEFAULT ((0)),

    [employee_premium] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employee_premium] DEFAULT ((0)),

    [employer_tax] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employer_tax] DEFAULT ((0)),

    [employee_tax] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employee_tax] DEFAULT ((0)),

    [paymethod_id] [int] NOT NULL,

    [join_option_id] [int] NULL,

    [ee_tax_fed] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_ee_tax_fed] DEFAULT ((0)),

    [ee_tax_prov] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_ee_tax_prov] DEFAULT ((0)),

    [er_tax_fed] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_er_tax_fed] DEFAULT ((0)),

    [er_tax_prov] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_er_tax_prov] DEFAULT ((0)),

    [coverage_category_fr] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ee_age] [int] NULL,

    [sp_age] [int] NULL,

    CONSTRAINT [PK_EmpBillStatement] PRIMARY KEY CLUSTERED

    (

    [empbillstatement_id] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]


    Thanks,
    Gena

    Wednesday, January 21, 2009 1:05 PM
  • How much memory did you dedicate to SQL Server ?

    Jens K. Suessmeyer

    Saturday, January 24, 2009 2:28 PM
  • Do you mean error logs? How much do I need and if not error logs then what? Another problem I noticed, if I run a bill for 500 people company, it takes 5 min to complete but 1700 takes more than 3 hours. The structure of the bill details is similar and from what I see this table takes longest to insert.

    Thanks,

    Gena

    Saturday, January 24, 2009 2:38 PM