为什么 SQL2008 调试器不会单步执行某个子存储过程

发布于 2024-09-02 10:23:44 字数 2038 浏览 3 评论 0原文

我遇到了 T-SQL 与 SQL2008(与 SQL2000)的差异,这些差异导致我陷入了死胡同。我已经验证了在创建 #TEMP 的调用者和引用它的子 sProc 之间共享 #TEMP 表的技术在 SQL2008 中仍然有效 查看最近的 SO 问题

我的核心问题仍然是一个关键的“子”存储过程,它在 SQL2000 中工作正常,但在 SQL2008 中失败(即子 sProc 中的 FROM 子句编码为:SELECT * FROM #AREAS A),尽管 #AREAS 是由调用父级创建的。现在不发布代码片段,这里有另一个症状可以帮助您提出建议。

我在 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'

这是一个非常大的 sProc,奇怪的关键片段如下:

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 (in 2008 only!)
  @StartDate = @StartDate      -- secondarily, I cannot STEP INTO this sProc 
, @EndDate = @EndDate
, @SMSA_CD = @SMSA_CD   
, @S1 = @S1
, @C1 = @C1
, @Z1 = @Z1
, @CityName = @CityName 
, @CustomID = @CustomID
, @Debug=0
-- continuation of the parent sProc**

我可以单步执行父存储过程。当我到达上面的第一个子存储过程时,我可以STEP INTO dbo.AMS_I_GetAreasV5 或STEP OVER 其执行。当我到达第二个子 sProc 的调用时 - dbo.AMS_I_GetAreas_FixAC - 我尝试进入它(因为那是问题陈述所在的位置)并且进入被忽略(即被视为跳过;但我知道我按下了F11 不是 F10)。但是它已执行,因为当控制权返回到 EXECUTE 之后的语句时,我单击“继续”完成执行,结果窗口显示 dbo.AMS_I_GetAreas_FixAC(即第二个子级)存储过程中的错误。

有没有办法“预加载”sProc,目的是在其入口处设置断点,以便我可以在其中继续执行?

总之,我想知道无法进入给定的子存储过程是否可能与该特定子存储过程无法引用其父存储过程(调用者)创建的#temp有关。

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 (in 2008 only!)
  @StartDate = @StartDate      -- secondarily, I cannot STEP INTO this sProc 
, @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).

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

梦毁影碎の 2024-09-09 10:23:44

我发现其中一个被调用的存储过程(即子进程)显然

SET NOCOUNT OFF

出于某种早已被遗忘的原因。

显然,当它生效时,SQL 2008 中的行为有很大不同。

我最终在所有 sProc 中识别出与上述类似的所有行,将它们更改为

SET NOCOUNT ON

.. 而且,我在实例级别检查了 NOCOUNT。这解决了这些非常奇怪的问题。

I found that one of the called stored procedures (i.e. the children) apparently had

SET NOCOUNT OFF

for some long-forgotten reason.

Apparently, the behavior of things in SQL 2008 is quite different when this is in effect.

I ended up identifying all lines like the above in all sProcs, changing them to

SET NOCOUNT ON

..and also, I checked NOCOUNT at the instance level. This fixed these very strange problems.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文