Uncorrect count(*)

Question

Post reply

Uncorrect count(*)

Frans Tromp

SSC Journeyman

Points: 80
More actions
November 21, 2002 at 12:18 pm

#79789

A job that runs every nigth sometimes produces incorrect record counts.
The following type of TSQL code produces the incorrect count (mostly zero):
set @message='Number of records to delete: ' + convert(varchar(10),(Select count(*) from taValidationUser)) + '. '
set @LogMessage=@LogMessage + @message
print @message
TRUNCATE TABLE taValidationUser
Could it be possible that the truncate executes before the count(*) statement?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

That isn't what I would expect.

Can you put a "GO" prior to the truncate table so it is two different transaction?

If so I'm thing then for sure the truncate would be after the record count selection.

Code would look something like this.

set @message='Number of records to delete: ' + convert(varchar(10),(Select count(*) from taValidationUser)) + '. '

set @LogMessage=@LogMessage + @message

print @message

go

TRUNCATE TABLE taValidationUser

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

Not likely, what makes you think it is not correct.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Frans Tromp SSC Journeyman Points: 80 More actions · Answer 3

Thanks for your replies!

The jobs run on a server I cannot access directly from my office. I can only access a table with log messages the stored procedures creates.

If the count(*) functions correctly the problem could be in the in the stored procedure that inserts records in the table that's truncated every day.

What I will do first is call the remote site to have a look at the "job history" to see if any errors are reported in the jobs.

I will let you know the results.

Greetings, Frans

Frans Tromp SSC Journeyman Points: 80 More actions · Answer 4

OK. Count(*) is correct. The problem is in the job that failes sometimes (approximately in 20% of the cases).

I got the following message from the job history by phone:

"...generated fatal exception c0000005 exception_access_violation. SQL Server is terminating this process. ...the step failed"

Probably my first fatal exception with SQL Server! We are using SQL Server 2000 SP2. The error occurs in a stored procedure that inserts records in a table using complex views. The folowing statement is executed approx. 20 times using a different value for viewXX

EXEC("insert into taValidationUser (...) select ... from viewXX")

It seems that one of these statements fail in a random fashion due to a SQL Server bug.

Cheers, Frans