如何查找导致 SQL Server 探查器跟踪中报告的错误的原因? [英] How to find what caused errors reported in a SQL Server profiler trace?

问题描述

我使用探查器在 Sql Server 2005 上运行跟踪，需要找出导致报告错误的原因.

I was running a trace on a Sql Server 2005 using the profiler and need to find out what is causing the reported errors.

我使用了空白"模板，并选择了以下事件的所有列:

I used the "blank" template, and selected all columns of the following events:

• 异常
• 交换泄漏事件
• 执行警告
• 哈希警告
• 缺少列统计信息
• 缺少连接谓词

我注意到TextData"列中有许多这样的错误:

I noticed a number of these errors in the "TextData" column:

• 错误:156，严重性:16，状态:0
• 错误:208，严重性:16，状态:0

我查找了错误(不正确的语法、无效的对象名称)，但是我怎么知道是什么存储过程或查询导致了它们?

I looked up the errors (Incorrect syntax, Invalid object name), but how can I tell what stored procedure or query is causing them?

推荐答案

不要担心 208 错误.208 是找不到对象".Profiler 会根据所谓的延迟名称解析"获取这些信息.

Don't worry about the 208 errors. 208 is "Object not found". Profiler picks up these due to what's called 'deferred name resolution'.

执行以下程序.

CREATE PROCEDURE Demo AS
  CREATE TABLE #Temp (ID int)
  INSERT INTO #Temp VALUES (1)
  SELECT ID FROM #Temp
GO

那个 proc 可以正常运行而没有任何错误，但是，如果您正在运行分析器跟踪，您将看到一两个错误 208 实例.这是因为该 proc 启动时表 #Temp 不存在，即当代码被解析和绑定时.绑定到底层对象的过程失败.

That proc will run fine without any errors however, if you have a profiler trace running, you'll see one or two instances of error 208. It's because the table #Temp doesn't exist when the proc starts, which is when the code is parsed and bound. The process of binding to the underlying objects fails.

一旦 create table 运行，其他语句就会重新编译并绑定到正确的表，并且不会出错.

Once the create table runs, the other statements get recompiled and bound to the correct table and run without error.

您会在分析器中看到延迟解析错误的唯一地方.

The only place you'll see that deferred resolution error is in profiler.

这篇关于如何查找导致 SQL Server 探查器跟踪中报告的错误的原因?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！