none
Tracing System.Data.OleDb commands to MS Access RRS feed

  • Question

  • Hi all,

     

    How can I trace SQL statements that are being executed against an MS Access DB from the OleDb provider? I have a data layer in a synchronisation application that is causing various errors when writing to MS Access databases and I need to see exactly what is being written.

     

    Regards,

     

    Stephen

     

    Friday, March 28, 2008 4:34 PM

All replies

  • You can do this with the Bid tracing built into System.Data.Oledb class.

     

    Check out this article:

     

    Tracing Data Access

    http://msdn2.microsoft.com/en-us/library/ms971550.aspx

     

    It's a bit tricky to setup but once you have it setup you can get traces of System.Data.Oledb.

    You want to look for ICommandText.SetCommandText calls, this is what set's the command text.

     

    I recall from my past experiences with Access there used to be a cool way to trace calls to Jet provider by turning on a reg key but I am not sure if it still works.  Let me know which OLEDB provider you are using and I will see if I can get this working.

    Friday, March 28, 2008 11:29 PM
  • Hey!

     

    Thanks for the reply. I really appreciate your help here! I had already got this tracing (using logman) running but I couldn't figure out how to get to the SQL. The provider I am using is Microsoft.Jet.OLEDB.4.0.

     

    Regards,

     

    Stephen

     

    Monday, March 31, 2008 8:53 AM
  • OK. I've found the SetCommandText event, but this is not what I am after. The SetCommandText simply shows the SQL string as it is added in the code using comm.CommandText = "...". I don't need to see what is being set in the Command object, rather what is being sent to Access (Jet). The SQL here uses parameters and so I need to see the SQL as it is passed to Access so that I can see what values are being passed. Any ideas how I can do this?

     

    Regards,

     

    Stephen.

    Monday, March 31, 2008 1:27 PM
  • Ok, did some hacking to discover the trace flags.

    What I did was create a simple cscript like so:

     

    Code Snippet

    dim conn

    set conn = createobject("adodb.connection")
    conn.open "provider=Microsoft.Jet.OLEDB.4.0;data source=c:\tempjunk\test.mdb"
    set rs = conn.execute("select * from Table1")
    rs.close
    conn.close

     

     

    Then I ran Process Monitor from SysInternals to see what reg keys were hit by cscript.exe.


    I found this one:

     

    HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug

     

    So I added this key, ran cscript above again and I see a probe (RegQueryValue) for this key:

     

             HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug\JETSHOWPLAN

     

    This was the one I remembered.  We have a few KBs on this one ->

     

    http://support.microsoft.com/kb/240434

    http://support.microsoft.com/kb/252883

     

    Unfortunately our Jet Programmer's Guide is no longer on MSDN.

     

    You can enable it by creating this key:

     

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Debug]
    "JETSHOWPLAN"="ON"

     

    When you run your code a file named showplan.out will be generated in local folder, this might be helpful.

     

    Please note, REMOVE THIS KEY WHEN YOU ARE DONE DEBUGGING!  It impacts all Jet processes on the machine and thus can slow performance and you may forget you set it and it will mystify you or someone else later.

    Monday, March 31, 2008 8:13 PM
  • Hhhmmm, I didn't receive notification about this reply. Sorry for the delay in getting back to you.

     

    Anyways, thank you very much! This is still an issue for us and it looks like this might just be what we are after!

     

    Regards,

     

    Stephen

    Wednesday, April 23, 2008 8:48 AM