如何查看 Sql Server 2000 上 SqlClient 数据提供程序最后运行的 SQL 查询？

发布于 2024-09-11 22:20:07 字数 298 浏览 4 评论 0原文

虽然我已经能够看到最后运行的查询（这是执行的存储过程），但我没有获得调用 SP 的参数值。相反，我得到了以下内容：

StoredProcedureName;1

从以下命令：

DBCC INPUTBUFFER(SPID)

我在哪里通过在 ObjectExplorer->Management->ActivityMonitor 中查看 SPID 来获取 SPID

有什么方法可以获取完整的文本，包括执行 SP 所用的参数？

原文

Although I have been able to see the last ran query which is a Stored Procedure executed but I didn't get the parameters values with which the SP was invoked. Rather I got the following:

StoredProcedureName;1

from the following command:

DBCC INPUTBUFFER(SPID)

Where I got the SPID by viewing it in the ObjectExplorer->Management->ActivityMonitor

Is there any way I can get the complete text including the parameters with which the SP was executed ?

分享到QQ

分享到微博

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

发布评论

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

顾忌 2024-09-18 22:20:07

我知道这个答案可能不是您正在寻找的，因为它并没有真正回答您的问题，我想了想，最终认为这可能会有所帮助。

我不知道您有多少查询以及您的程序有多大......但出于调试目的，我想对我的所有查询（纯文本和存储过程）执行类似的操作。因此，我编写了一个简单的包装类，它允许我执行带参数和不带参数的纯文本查询/存储过程。然后，如果发生异常，我会捕获它，使用原始异常加上已执行的查询和所有参数构建新的自定义异常，并在自定义消息中返回所有异常。我在包装器中使用 Oracle，但它几乎完全相同：

Public Function ExecuteCommandQuery(ByRef oCMD As OracleClient.OracleCommand) As DataTable
    oCMD.Connection = _oConn

    Dim dt As New DataTable

    'exception if one occured'
    Dim DBException As Exception = Nothing

    Try
        'get an adapter'
        Dim cmd As New OracleDataAdapter(oCMD)
        'Fill the data table and ket a count of records returned'
        cmd.Fill(dt)

    Catch ex As Exception
        'capture exception, and rethrow after properly closing the Oracle Connection'
        DBException = ex
    Finally
        _oConn.Close()
    End Try

    'if exception occured, rethrow'
    If DBException IsNot Nothing Then
        Throw New Exception( _
            String.Format("A database error occured: {0} " + _
                          Environment.NewLine + Environment.NewLine + " --- " + _
                          Environment.NewLine + Environment.NewLine + _
                          " Your query: {1}" + _
                          Environment.NewLine + Environment.NewLine + " --- " + _
                          Environment.NewLine + Environment.NewLine + _
                          " Your Parameters: " + Environment.NewLine + "{2}" _
                          , DBException.ToString(), oCMD.CommandText, GenerateParameterErrorInfo(oCMD)))
    End If

    Return dt
End Function

I know this answer may not be what you are looking for, as it doesn't really answer your question, I took a leap of thought and ended up thinking this might help.

I don't know how many queries you have and how big your program is... but for debugging purposes I wanted to do something similar for all of my queries, both plain text and stored procedures. So I wrote a simple wrapper class that lets me execute plain text queries/stored procs with and without parameters. Then, if an execption occurs, I trap it, build a new custom exception with the original exception plus the query that was executed and all parameters, and return it all in a custom message. I'm using Oracle in my wrapper but it's almost exactly the same:

Public Function ExecuteCommandQuery(ByRef oCMD As OracleClient.OracleCommand) As DataTable
    oCMD.Connection = _oConn

    Dim dt As New DataTable

    'exception if one occured'
    Dim DBException As Exception = Nothing

    Try
        'get an adapter'
        Dim cmd As New OracleDataAdapter(oCMD)
        'Fill the data table and ket a count of records returned'
        cmd.Fill(dt)

    Catch ex As Exception
        'capture exception, and rethrow after properly closing the Oracle Connection'
        DBException = ex
    Finally
        _oConn.Close()
    End Try

    'if exception occured, rethrow'
    If DBException IsNot Nothing Then
        Throw New Exception( _
            String.Format("A database error occured: {0} " + _
                          Environment.NewLine + Environment.NewLine + " --- " + _
                          Environment.NewLine + Environment.NewLine + _
                          " Your query: {1}" + _
                          Environment.NewLine + Environment.NewLine + " --- " + _
                          Environment.NewLine + Environment.NewLine + _
                          " Your Parameters: " + Environment.NewLine + "{2}" _
                          , DBException.ToString(), oCMD.CommandText, GenerateParameterErrorInfo(oCMD)))
    End If

    Return dt
End Function

回复收藏 0 原文

~没有更多了~