Post reply

SP Doesn't Return Recordset when using Temp Tables

  • March 25, 2003 at 8:15 am

    #159704

    I am executing a Stored Procedure in ASP on IIS 5.0.

    My SP uses #TempTable and returns recordset fine using ODBC DSN with NT Authentication but fails to return recordset using OLEDB ConnectionString with SQLServer Authentication even after making the SQLServer user as DBowner on TempDB.

    If anyone has any suggestions pls let me, any help is appreciated.

    Thanks in advance.

    Giri

  • March 25, 2003 at 9:06 am

    #452058

    We've noticed similar issues with stored procedures and processing the returned data. One thing that we've found helpful is to use

    SET NOCOUNT ON

    as the first statement in the stored procedure. What seems to happen is that empty RecordSets (or something similar) are generated while manipulating temporary tables, assigning variables etc. using "SET NOCOUNT ON" prevents these empty sets from being generated, and from causing you problems.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • March 25, 2003 at 9:33 am

    #452063

    Also check the scope of the #temp table. Because that table is only visible to that connection. Check changing the temp table to a global temp table creating it like CREATE TABLE ##TempTable.

    Or even use a table called tmp_table that in fact it isn't temporal.

  • March 25, 2003 at 9:40 am

    #452065

    has the SQLServer user got the same privileges / access to the main database that the NT Authenication user has?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • March 25, 2003 at 10:42 am

    #452077

    Thanks Thomas!

    Yup! You are right, that solved my problem.

    Thanks Again,

    Giri

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

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