views:

76

answers:

1

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 ?

+1  A: 

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
Patricker
Actually, I was looking for it to make debugging convenient so that I get the sql with the exact syntax executed and do not need to go to visual studio and copy the exception message and execute it in sql server.
Puneet Dudeja
That is how I use it. When an exception is thrown (I use ASP.Net) I get the full exception plus the full query with all parameters right on the screen. Then I copy them into Toad/SQL Server Management Studio and execute it by hand (if I can't see just by looking at it why it's broke).Does my answer help or is there a way I could modify it so that it would?
Patricker