October 12, 2002 at 7:48 am
Hi Folks,
I am having a problem with a sp that using dynamic SQL quite heavily.
Everything has been running fine until just recently, when I am now encountering an error about half way thru the sp...
Here is a much simplified version of what the sp is doing, for the purposes of demonstration...
DECLARE @cQuery VARCHAR(1000)
SET @cQuery = 'SELECT Branch INTO #MyTemp1 FROM MyTable'
EXEC (@cQuery)
SET @cQuery = 'SELECT #MyTemp1.Branch, MyTable2.BranchName INTO #MyTemp2 FROM #MyTemp1 ON #MyTemp1.Branch = MyTable2.Branch'
EXEC (@cQuery)
... etc.
The first 'EXEC' executes fine (generating some 447 rows), but the second 'EXEC' generates the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#MyTemp1'.
----
As part of my 'bug hunt', I output all of the generated T-SQL to a text file, and it ran perfectly -- no errors.
So I can only conclude that it has something to do with that fact that the code is running with a sp.
The curious thing is that the code that is causing me problems does nothing different to code earlier within the sp that also runs ok.
Is it a resource issue? Or scoping issue? (altho' this doesn't seem logical to me...)
This issue has lost me a whole day's development time so far, and I'm still no further forward!
ANY advice gratefully received!!
Cheers
Joe
October 12, 2002 at 8:35 am
It is a scoping issue. To prove it, just change to global temp tables using ## instead of #.
Andy
October 12, 2002 at 9:28 am
To get around this you can use a single EXEC for the entire set of dynamic strings, works like any batch and will keep the table in scope for the duration.
'SELECT Branch INTO #MyTemp1 FROM MyTable
SELECT #MyTemp1.Branch, MyTable2.BranchName INTO #MyTemp2 FROM #MyTemp1 ON #MyTemp1.Branch = MyTable2.Branch'
Also instead of using SELECT...INTO you should create the temp table using CREATE TABLE statement. Then use INSERT #MyTemp1 (ColumnList) SELECT statment, there is a known issue where locking issues can occurr when doing SELECT...INTO.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 12, 2002 at 10:41 am
Creating temporary tables and populating them in an SP can cause all sorts of problems in SQL 7 and 2000 due to recompilations that occur when the original plan for the SP is recompiled due to changes in the data it was based on.
Scope is the issue. A temporary table is only accessible to the current and child scopes i.e the SP that created it and any other SPs called from it. The code in the Execute is a child and so the #MyTemp1 cannot be accessed from the main SP or the second execute statement. To get qround this create the tables at the start of the SP and use INSERT INTO statements.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 12, 2002 at 10:46 am
Simon,
Just curious to see if it something I missed, but what types of problems are you refering to about Temp Tables. I am aware of the SELECT...INTO causing blocking, improper cleanup if not explicit on DROP TABLE, issues with storing in tempdb large temp table objects, and as for the recompile it is of note that you should use the WITH RECOMPILE statment in an SP to get around the issue with recompiles that I am aware of.
Just want to know what else there is that maybe I have missed.
Thanks.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 12, 2002 at 2:10 pm
The recompile issue is larger than just using the WITH RECOMPILE and is not just for temp tables.
You shouldn't significantly alter the contents of a table and then use that table further on in a procedure. An initial plan is produced and then you insert 10% more data into a table which you then join to in a second SQL statement. A recompile clause will likely to be caused after the insert statement, so a better plan can be produced.
This is realy of issue when the SP is complex, and the table volumes large (>10k rows). I have known an SP does stop because the a recompilation never completes, this was exceptionaly bad coding.
Therefore breakdown an SP into layers. The first layer is the SP to be called, in this temp tables are created, then data modifications are made in sub stored procedures. This results in smaller compilation times better plans, more readible code 2 pages per sp max.
I hope that answers your question?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply