[Windbg Script] Retrieving queries/stored procedures from .NET application


Ok, you are debugging a .NET application. You need to find out the queries and stored procedures being executed from the threads accessing the database, but you don’t know how. . . Good news! It’s not a problem anymore!


This script shows you all the queries or stored procedures associated with a SQLCommand or OracleCommand object. Moreover, you can click on it to get more details and the threads which use it.


 


If you compare this script with the last one, from my previous post, you’ll understand why I prefer the DML approach and why you need more work than the previous approach.


 


These are the screenshots:


 


 


 



 


Source code for GET_SQLCOMMAND.TXT:


 


$


$$


$$ =============================================================================


$$ It shows the SQL commands from a .NET application. It gives you detailed information


$$ and the threads using the query/stored procedure you selected.


$$ Useful for Oracle and SQL Server.


$$


$$ Compatibility: Win32.


$$


$$ Usage: Use $$>< to run the program.


$$


$$ Requirements: Public symbols.


$$


$$ If necessary change the filename below to include your path and filename.


$$ By default it uses the WinDbg path and the default file name is GET_SQLCOMMAND.TXT


$$


$$ Roberto Alexis Farah


$$ Blog: http://blogs.msdn.com/debuggingtoolbox/


$$


$$ All my scripts are provided “AS IS” with no warranties, and confer no rights.


$$ =============================================================================


$$


ad /q *


r @$t0 = 0


r @$t1 = 0


.printf /D “<b>\nClick on the queries/stored procedures below to get more details and to find out the threads using it.\n\n</b>”


.block


{


    .block


    {


        as ${/v:ScriptName} MYSCRIPTS\\GET_SQLCOMMAND.TXT


    }


    .block


    {


        as SQLCommand .block


        {


            !DumpObj poi(@$t0+0x10)


            !DumpObj @$t0


            !GCRoot @$t0


        }


    }


    .block


    {


        as OracleCommand .block


        {


            !DumpObj poi(@$t0+0x14)


            !DumpObj @$t0


            !GCRoot @$t0


        }


    }


 


}


.foreach(obj {!dumpheap -short -type System.Data.SqlClient.SqlCommand } )


{


    r @$t1 = 1


    .printf /D “<link cmd=\”r @$t0 = ${obj}; ${SQLCommand} ;ad ${/v:ScriptName}; $$><${ScriptName}\”><b>%mu</b></link>\n\n”, poi(${obj}+0x10)+0xc


}


.foreach(obj {!dumpheap -short -type System.Data.OracleClient.OracleCommand } )


{


    r @$t1 = 1


    .printf /D “<link cmd=\”r @$t0 = ${obj}; ${OracleCommand} ;ad ${/v:ScriptName}; $$><${ScriptName}\”><b>%mu</b></link>\n\n”, poi(${obj}+0x14)+0xc


}


.if(0 = @$t1)


{


    .printf /D “<b>\nNo SQL commands found.\n</b>”


}


 


Read me.

Comments (9)

  1. nativecpp says:

    Thanks for another useful script. One question on script command:

    Is there a way to check if poi(@$t0+0x10)is 0 ?

    I tried to use

    .if (0 != poi(@$t0+0x10) but it doesn’t like it.

  2. Hi,

    Your command should work, but just if you use the {} like:

    .if(0 != poi(@$t0+0x10)) {.echo Hey Mom! I know pointers!}

    It works too, but does nothing:

    .if(0 != poi(@$t0+0x10)) {}

    It gives a syntax error:

    .if(0 != poi(@$t0+0x10))

    Or yet, if you want to be hard core:

    j(0 != poi(@$t0+0x10)) ‘.echo Hey, it works too’ ; ‘.echo Not working…’

    A little tip: If you are using brackets and still getting an error it means the pointer is not being solved and Windbg is returning an error. To make sure it is not the case just type something like: dd poi(@$t0+0x10) to see if there is an error message.

    Note: I’m assuming $t0 is valid. Again, use dd @$t0 to double check it.

    I hope it helps you.

  3. nativecpp says:

    Thanks for the help. I solved the issue. Can’t wait for your next script.

  4. nativecpp says:

    BTW, one comment about the dumpheap -type. It is too bad it does *NOT* have an exact match option. In my case, I have System.Data.SqlClient.SqlCommand & System.Data.SqlClient.SqlCommand+CachedAsyncState.

  5. The script was updated. Now it works with Oracle too. 🙂

  6. Rene Pally says:

    I have the following stack:

    0399ee70 07bf7460 MyFunction(System.String)

       PARAMETERS:

           this = 0x01bf6da0

           UIRL = 0x01bae768

    is there any way to extract the pointer of this?

    ? poi(UIRL)

    THanks

  7. Hi Rene,

    Yes, you can try it:

    dd this L1

    or the approach I prefer:

    dt this

    dt is going to give you the type, members, etc. The address is the first information and it’s the same address you get using dd this L1.

    Try the same approach with the UIRL variable. (just after re-reading your question I’ve noticed the this you mentioned is not actually the ‘this’ pointer 🙂 )

    Thanks

  8. There’s another script that gives you all queries/stored procedures from SQL Server or Oracle that are