Wrong Count result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Wrong Count result

Author

Topic

irehman
Starting Member

22 Posts

Posted - 2005-06-26 : 21:44:23

Platform: Windows 2000
DB: SQL Server 2000

Problem: Once in a while query returns wrong count (total number of rows).

I searched the Microsoft website and found following
http://support.microsoft.com/default.aspx?scid=kb;en-us;824018

I implemented the work around, but problem still exsists.

Any ideas?

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-27 : 00:47:08

You need to give more information
Read this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

irehman
Starting Member

22 Posts

Posted - 2005-06-27 : 11:40:31

Here is the information you requested

Table create script:

CREATE TABLE [dbo].[PART_DETAIL] (
[PART_SN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JOB_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CELL_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LAST_TASK_COMPLETED] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REJECT_FLAG] [bit] NOT NULL ,
[FINAL_TEST_FLAG] [bit] NOT NULL ,
[PACK_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PARTIAL_COMPLETE_FLAG] [bit] NOT NULL ,
[REJECT_REASON] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

2. Post some sample data in the form of DML

--sn = TBR2033C132461103 -- job = '92828282' cell = '01'

INSERT INTO PART_DETAIL (PART_SN,JOB_ID,CELL_ID) VALUES (rtrim(@SN),@Job,@Cell)

3. Post whatever DML that you have attempted already

SELECT @SN = COUNT(PART_SN) FROM PART_DETAIL
Where SUBSTRING(RTRIM(PART_SN),9,5) = Rtrim(@Shift) + Rtrim(@YY) + Rtrim(@JDate) ---- ex: '15178'
AND SUBSTRING(RTRIM(PART_SN),15,1) = right(rtrim(@Cell),1) ---ex : '1'

4. Post the expected results
Above query suppose to bring exact count of rows from the table, but sometimes it brings wrong count. Table has 200,000+ rows.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources