Stored procedure with Temporary Tables

Results 1 to 2 of 2

Thread: Stored procedure with Temporary Tables

  1. #1
    Trev Guest

    Default Stored procedure with Temporary Tables

    I&#039ve written a stored procedure which creates a temporary table in order to summarise results from various sources, but when I try to use this stored procedure with an ASP the procedure runs but no results appear. I&#039ve read that because it creates a temporary table, the table is dropped as soon as the stored procedure has finished executing. Does anyone know a way of using an instance such as above on an ASP?

  2. #2
    Barb Guest

    Default RE: Stored procedure with Temporary Tables

    Try adding to the top of the stored procedure:<BR>SET NOCOUNT ON<BR><BR><BR>The below came from MSDN hope it helps.<BR>CAUSE<BR>The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset. <BR><BR>The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL. <BR><BR><BR>RESOLUTION<BR>To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement. Alternatively, you could use MSDASQL. <BR><BR><BR><BR>STATUS<BR>This behavior is by design. <BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts