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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy