March 14, 2007 at 2:48 pm
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?
March 15, 2007 at 4:13 am
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.
March 15, 2007 at 6:33 am
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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply