does count(*) ever return an empty recordset? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Development (2000)

does count(*) ever return an empty recordset?

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-19 : 05:17:15

i sometimes get this error on our production server:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

when running this code:


sSQL = "SELECT 	Count(*) as cnt 
        FROM 	table1 t1	
	        JOIN table2 t2 ON t1.id = t2.id 
        WHERE 	<some conditions>"

Set rsCount = Server.CreateObject("ADODB.Recordset")
rsCount.Open sSQL, Conn
count = rsCount("cnt").Value

Now what i can't find is when would this query return that error?
Doesn't count(*) always return 0 or number of rows and never an empty recordset?

I can't reproduce this in our dev or test enviroment.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:21:45

Yes. It happens for me sometimes when SET NOCOUNT ON is not present in the code.
Try to add SET NOCOUNT ON on the code above.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-19 : 05:27:05

ahhh.... i think i see what you're pointing at.

but why would this happen???
do messages get in front of the acctual resultset randomly or something??

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:35:07

Some things are just mysterious...

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-19 : 05:38:41

ok that's unacceptable

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:53:32

I would believe that is an ADO bug, not a SQL Server bug.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-19 : 05:56:40

likewise.
but to not have this fixed in MDAC 2.8... damn....

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 06:03:35

Do

sSQL = "SET NOCOUNT ON; SELECT Count(*) as cnt
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE <some conditions>"

Set rsCount = Server.CreateObject("ADODB.Recordset")
rsCount.Open sSQL, Conn
count = rsCount("cnt").Value

fix it?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-19 : 06:06:15

i have no idea... i'll put it on production and see if the error happens again.

this happens once every 2-3 days.
and i can't reproduce this in our dev or test enviroment.

thanx for the tip, peter.
i'll report back here if it happens again.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-20 : 08:51:32

"i'll put it on production and see if the error happens again."

Hahahaha!!!!!!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-20 : 10:19:34

what's so funny Kristen?

luckily i can afford that...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-20 : 13:12:42

"what's so funny Kristen?"

... on production ...

Saving money on the QA step ... your boss will be pleased!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-20 : 14:04:50

we don't have that step

seriously though... we can't reproduce this anywhere else.
and my boss knows all about it.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-20 : 14:27:12

Yeah, and your boss will tell you it was your idea to skip the QA step when it all goes pear-shaped.

At least ... that's what I do with my troops!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-20 : 14:36:47

well as long as right persons know about i don't give a damn.
what are they going to do? fire me?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-01-20 : 14:50:52

I would talk you into buying me lunch for giving me poor advice on which I made the management decision that you then acted on

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-20 : 15:39:22

under what pretense?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-21 : 02:49:22

quote:
Originally posted by spirit1

i'll put it on production and see if the error happens again.

that's pretty much what HAL recommended in 2001 when he couldn't diagnose the problem with the communications antenna...

www.elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-21 : 15:42:35

damn it... i was discovered

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-25 : 05:26:28

quote:
Originally posted by spirit1

i sometimes get this error on our production server:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

when running this code:

sSQL = "SELECT Count(*) as cnt
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE <some conditions>"

Set rsCount = Server.CreateObject("ADODB.Recordset")
rsCount.CursorLocation = adUseClient
rsCount.Open sSQL, Conn
if rsCount.RecorCount > 0 Then
count = rsCount("cnt").Value
End If

Now what i can't find is when would this query return that error?
Doesn't count(*) always return 0 or number of rows and never an empty recordset?

I can't reproduce this in our dev or test enviroment.

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Because, If u will not tell ur recordset, it will bydefault open on server and will return u -1. Also u can check recordcount which will tell u the count of records in recordset. So that, u can check, if ur recordset has values, then go further.

Thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 05:32:59

Not entirely true.
The message "(0) Records Affected" is treated as one record with invalid columns.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources