Problems with #temp and table vars

Hiya,

I am having issues with temp tables and table variables that i beleive is an accounts issue but i cannot track it down. When i use temp tables or table vars within sprocs the sproc works fine from QA but when accessed using ADODB from VB the sproc fails to operate but does not raise an error.

I have tried the same sproc using a simple statement to test the connection and it works fine but any reference to the temp tables or table vars cuases it to fail.

Has anyone come accross this before and if so how did they get around it

cheers
John

What made you believe it is a temp table/var table issue? What does the error message say?
could you post a call to this procedure?

Bambola.

I can successfully perform the sproc from QA but when it comes to executeing it from VB it will only execute if i remove any reference to temp tables or table vars. I have tested this and found that the only bit failing from the VB perspective is the temp table sections.

As for a error message, it doesnt return one.

here is a quick example i just whipped up:


a sproc having this line works fine in vb:


--*********************************************
select code from tbldepot
--*********************************************

but if i change it to this:

--*********************************************
select code
into #tmpCode
from tbldepot

select code from #tmpCode

drop table #tmpCode
--*********************************************

it works fine in QA but when i set it to a recordset var in VB (as done in example 1) the recordset is not opened, no error is returned, just a unopened recordset, not empty but unopened.

if it helps i can write a small app to demonstrate this

thanks

John

Do you have a SET NOCOUN ON at the beginning of your stored procedure? If not than you are returning more than one recordset and it could be the problem.

Bambola.

But my guess is even if SET NOCOUNT ON hasn't been set, the recordset should be generated.

Gaurav

Setting NOCOUNT ON/OFF will not impact the generated recordset. It will the number of recordset returned by the sproc.
When set nocount is off (default), in addition to what the sproc returns you will have the number of rows affected. Usually this is something that you do not need and causes extra traffic.

In this case, from VB you will have to move to the next recordset to read the results:
set rs = rs.NextRecordset
(if you are returning more than one recordset, or you have other statments that generate number of rows affected, you will have to skip it each time)

Or use SET NOCOUNT ON at the beginning of the statement.

Bambola.

Hey Bambola, correct me if I am wrong. The output (XXX Rows Affected) is not counted as recordset. Only the select statements in any query are counted as recordsets. This is the behaviour that I saw in VC++. Is there some change in VB?

Gaurav

I can tell you that many ASP programmers spend days trying to figure out why their queries "returns nothing" when the problem is exactly this. Try it and see for yourself.
This is why I many times ask what does the sproc return when run in QA, where you can see all result sets.

Bambola.

The 'X rows affected' message that you see in QA is not returned as a true 'recordset' when working with ADO. Yes, it is returned to the client, but not as a recordset object accesible through the NextRecordset method.

Nathan H.O.

As for the problem with temp tables and Stored procedures - you are right about the ADO recordset not opening!

As Bambola said, use SET NOCOUNT ON in your stored procedure.

make sure you read this:

http://www.sql-server-performance.com/rd_temp_tables.asp and note the list of bugs at the bottom of the page.

Nathan H.O.

Thanks peoples. it was the "set nocount on" that has fixed the problem. Wrapping my sproc in "set nocount on" - "set nocount off" has the app running very nicely again...and saved my sanity in trying to write this particular sproc without temps<br /><br />thanks again<br />John<br />[<img src='/community/emoticons/emotion-2.gif' alt='' />]