SQL Server Performance

Problems with #temp and table vars

Discussion in 'General Developer Questions' started by jwalker, Jul 2, 2003.

  1. jwalker New Member


    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

  2. bambola New Member

    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?

  3. jwalker New Member

    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


  4. bambola New Member

    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.

  5. gaurav_bindlish New Member

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

  6. bambola New Member

    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.

  7. gaurav_bindlish New Member

    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?

  8. bambola New Member

    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.

  9. vbkenya New Member

    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.
  10. vbkenya New Member

    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.
  11. jwalker New Member

    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=':D' />]

Share This Page