Database Engine Tuning Advisor -and- Temp tables

  • Using SQL 2005 'Database Engine Tuning Advisor' is there a setting I can make to avoid getting the error:

    [Microsoft][Native SQL Clinet][SQL Server]Invalid object name '#tempTableName'

    The proc I am trying to tune includes a #temp table.  I realize I can re-code the proc to use a table variable but this is extra effort when attempting to tune dozens (hundreds) of procs using DTA.

    I tried recreating the PROC with:   SET STATISTICS PROFILE ON   -- this did not work.

    Any suggestions?

     

     

    BT
  • 1. consider running the procedure normally once. capture a profiler trace (with tunning template). Pass the trace as a worload to tune to the DTA.

    2. Replace #temp with a normal table once (or create a copy of procedure ). and tune that.

     


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

  • Thanks for the info.  I've actually done this successfully as well.  Considering the fact that I'd like to tune dozens of procs - this is cumbersome and time consuming.  Just curious to see if anyone knew of a way to override the #temp limitiation (failure) during a DTS progress. 

    BT

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

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