I'm encountering differences in T-SQL with SQL2008 (vs. SQL2000) that are leading me to dead-ends. I've verified that the technique of sharing #TEMP tables between a caller which CREATES the #TEMP and the child sProc which references it remain valid in SQL2008 See recent SO question.
My core problem remains a critical "child" stored procedure that works fine in SQL2000 but fails in SQL2008 (i.e. a FROM clause in the child sProc is coded as: SELECT * FROM #AREAS A) despite #AREAS being created by the calling parent. Rather than post snippets of the code now, here is another symptom that may help you suggest something.
I fired up the new debugger in SQL Mgmt Studio:
EXEC dbo.AMS1 @S1='06',@C1='037',@StartDate='01/01/2008',@EndDate='07/31/2008',@Type=1,@ACReq = 1,@Output = 0,@NumofLines = 30,@SourceTable = 'P',@LoanPurposeCatg='P'
This is a very large sProc and the key snippet that is weird is the following:
**create table #Areas
(
State char(2)
, County char(3)
, ZipCode char(5) NULL
, CityName varchar(28) NULL
, PData varchar(3) NULL
, RData varchar(3) NULL
, SMSA_CD varchar(10) NULL
, TypeCounty varchar(50)
, StateAbbr char(2)
)
EXECUTE dbo.AMS_I_GetAreasV5 -- this child populates #Areas
@SMSA = @SMSA
, @S1 = @S1
, @C1 = @C1
, @Z1 = @Z1
, @SourceTable = @SourceTable
, @CustomID = @CustomID
, @UserName = @UserName
, @CityName = @CityName
, @Debug=0
EXECUTE dbo.AMS_I_GetAreas_FixAC -- this child cannot reference #Areas
@StartDate = @StartDate
, @EndDate = @EndDate
, @SMSA_CD = @SMSA_CD
, @S1 = @S1
, @C1 = @C1
, @Z1 = @Z1
, @CityName = @CityName
, @CustomID = @CustomID
, @Debug=0
-- continuation of the parent sProc**
I can step through the execution of the parent stored procedure. When I get to the first child sproc above, I can either STEP INTO dbo.AMS_I_GetAreasV5 or STEP OVER its execution. When I arrive at the invocation of the 2nd child sProc - dbo.AMS_I_GetAreas_FixAC - I try to STEP INTO it (because that is where the problem statement is) and STEP INTO is ignored (i.e. treated like STEP OVER instead; yet I KNOW I pressed F11 not F10). It WAS executed however, because when control is returned to the statement after the EXECUTE, I click Continue to finish execution and the results windows shows the errors in the dbo.AMS_I_GetAreas_FixAC (i.e. the 2nd child) stored procedure.
Is there a way to "pre-load" an sProc with the goal of setting a breakpoint on its entry so that I can pursue execution inside it?
In summary, I wonder if the inability to step into a given child sproc might be related to the same inability of this particular child to reference a #temp created by its parent (caller).
