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).