{"id":301,"date":"2016-06-02T22:45:28","date_gmt":"2016-06-02T22:45:28","guid":{"rendered":"http:\/\/ricardomoinhos.com\/?p=301"},"modified":"2016-09-01T14:04:55","modified_gmt":"2016-09-01T14:04:55","slug":"sql-profiler-commits-nav","status":"publish","type":"post","link":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/","title":{"rendered":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code)"},"content":{"rendered":"

Everyone of us has already experienced an unexpected COMMIT in the middle of a posting routine, for example, that prevents\u00a0the full rollback from happening whenever there’s an error.<\/p>\n

You can always use the debugger to track\u00a0the code and step into it, line after line, trying\u00a0to find where the commit is. This will work but will consume a lot of time.<\/p>\n

Ok, let’s do this the easy way then. Let’s use a good (new) friend of us… the SQL Profiler tool!<\/p>\n

Here’s a simple codeunit that I’ve created which\u00a0updates\u00a0the address in the first 10 customers to “TEST” and commits each change every time.<\/p>\n

\"\"<\/p>\n

Next, open SQL Profiler and prepare to run it.<\/p>\n

Create a new trace and select the following events and columns. You may choose others but these are the ones\u00a0you need for this example.<\/p>\n

\"\"<\/p>\n

UPDATE: SQLTransaction event shouldn’t be used because some commits transactions that didn’t occured may appear. Instead, you should use the following events:
\n-> TM: Begin Tran completed
\n-> TM: Commit Tran completed
\n-> TM: Rollback Tran completed
\n-> TM: Save Tran completed<\/p>\n

In Dynamics NAV 2013 (and higher)\u00a0you have a nice feature called SQL Tracing that can be enabled in the NAV Debugger window. Activate it by\u00a0pressing\u00a0the “Start Full SQL Tracing” button:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

The SQL Tracing field is activated in the selected session.<\/p>\n

Start the trace by pressing Run in the SQL Profiler:<\/p>\n

\"\"<\/p>\n

Afterwards,\u00a0run the codeunit in Microsoft Dynamics NAV Development Environment.<\/p>\n

\"\"<\/p>\n

Next, stop the trace:<\/p>\n

\"\"<\/p>\n

And now the analysis part:<\/p>\n

    \n
  1. In the EventClass type, SQLTransaction shows\u00a0every transaction start (0 – Begin) and end (1 – Commit) – in red, in the image below.<\/li>\n
  2. In the EventClass type, in the\u00a0SQLBatchCompleted lines you can see\u00a0the command being run in Dynamics NAV as well as the object and line. This happens because we’ve\u00a0activated the SQL Tracing option in NAV Debugger session (great, ah?!) – in blue, in the image below.<\/li>\n<\/ol>\n

    \"\"<\/p>\n

    As we are looping through each customer and commiting the transaction every MODIFY, you have a few Begin\/Commit lines, which matches\u00a0each transaction.<\/p>\n

    Next,\u00a0comment the COMMIT line in the\u00a0codeunit and run the trace again to see the differences.<\/p>\n

    \"\"<\/p>\n

    In this case,\u00a0you have\u00a0the beginning of the transaction, followed by all the MODIFYs in the customer’s record and the commit in the end.<\/p>\n

    \"\"<\/p>\n

    Finally, all you have to do is to use this with your posting routine and identify the beginning and the end of the transaction(s), looking for unexpected commits during the routine. As you have the SQL:BatchCompleted event activated, commands being run in Dynamics\u00a0NAV are shown so you can easily find the\u00a0object and line where the COMMIT is.<\/p>\n

    Please feel free to ask me questions or to comment this article.<\/p>\n

    Note: I guess you’re familiar with SQL Profiler, so\u00a0I don’t have too much detail regarding how to choose the events or the columns, etc.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Everyone of us has already experienced an unexpected COMMIT in the middle of a posting routine, for example, that prevents\u00a0the full rollback from happening whenever there’s an error. You can always use the debugger to track\u00a0the code and step into it, line after line, trying\u00a0to find where the commit is. This will work but will […]<\/p>\n","protected":false},"author":1,"featured_media":138,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[4],"tags":[],"class_list":{"0":"post-301","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","6":"hentry","7":"category-dynamics365bc","9":"post-with-thumbnail","10":"post-with-thumbnail-icon"},"yoast_head":"\nUsing SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/\" \/>\n<meta property=\"og:locale\" content=\"pt_PT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos\" \/>\n<meta property=\"og:description\" content=\"Everyone of us has already experienced an unexpected COMMIT in the middle of a posting routine, for example, that prevents\u00a0the full rollback from happening whenever there’s an error. You can always use the debugger to track\u00a0the code and step into it, line after line, trying\u00a0to find where the commit is. This will work but will […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/\" \/>\n<meta property=\"og:site_name\" content=\"Ricardo Paiva Moinhos\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-02T22:45:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-09-01T14:04:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png\" \/>\n\t<meta property=\"og:image:width\" content=\"375\" \/>\n\t<meta property=\"og:image:height\" content=\"375\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Ricardo Paiva Moinhos\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ricardo Paiva Moinhos\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tempo estimado de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/\"},\"author\":{\"name\":\"Ricardo Paiva Moinhos\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/#\\\/schema\\\/person\\\/16dcfdd54ec1c46bd1941659739de4cc\"},\"headline\":\"Using SQL Profiler to find COMMITs (and other Dynamics NAV code)\",\"datePublished\":\"2016-06-02T22:45:28+00:00\",\"dateModified\":\"2016-09-01T14:04:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/\"},\"wordCount\":501,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ricardomoinhos.com\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PT_c_Dyn-NAV-2013.png\",\"articleSection\":[\"Dynamics NAV\\\/365 BC\"],\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/\",\"url\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/\",\"name\":\"Using SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/ricardomoinhos.com\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PT_c_Dyn-NAV-2013.png\",\"datePublished\":\"2016-06-02T22:45:28+00:00\",\"dateModified\":\"2016-09-01T14:04:55+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/#\\\/schema\\\/person\\\/16dcfdd54ec1c46bd1941659739de4cc\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#breadcrumb\"},\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#primaryimage\",\"url\":\"https:\\\/\\\/ricardomoinhos.com\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PT_c_Dyn-NAV-2013.png\",\"contentUrl\":\"https:\\\/\\\/ricardomoinhos.com\\\/wp-content\\\/uploads\\\/2014\\\/06\\\/PT_c_Dyn-NAV-2013.png\",\"width\":375,\"height\":375},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/sql-profiler-commits-nav\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/ricardomoinhos.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using SQL Profiler to find COMMITs (and other Dynamics NAV code)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/#website\",\"url\":\"https:\\\/\\\/ricardomoinhos.com\\\/\",\"name\":\"Ricardo Paiva Moinhos\",\"description\":\"Welcome\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ricardomoinhos.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-PT\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/ricardomoinhos.com\\\/#\\\/schema\\\/person\\\/16dcfdd54ec1c46bd1941659739de4cc\",\"name\":\"Ricardo Paiva Moinhos\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g\",\"caption\":\"Ricardo Paiva Moinhos\"},\"url\":\"https:\\\/\\\/ricardomoinhos.com\\\/pt\\\/author\\\/ricardopaiva\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/","og_locale":"pt_PT","og_type":"article","og_title":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos","og_description":"Everyone of us has already experienced an unexpected COMMIT in the middle of a posting routine, for example, that prevents\u00a0the full rollback from happening whenever there’s an error. You can always use the debugger to track\u00a0the code and step into it, line after line, trying\u00a0to find where the commit is. This will work but will […]","og_url":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/","og_site_name":"Ricardo Paiva Moinhos","article_published_time":"2016-06-02T22:45:28+00:00","article_modified_time":"2016-09-01T14:04:55+00:00","og_image":[{"width":375,"height":375,"url":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","type":"image\/png"}],"author":"Ricardo Paiva Moinhos","twitter_card":"summary_large_image","twitter_misc":{"Escrito por":"Ricardo Paiva Moinhos","Tempo estimado de leitura":"2 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#article","isPartOf":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/"},"author":{"name":"Ricardo Paiva Moinhos","@id":"https:\/\/ricardomoinhos.com\/#\/schema\/person\/16dcfdd54ec1c46bd1941659739de4cc"},"headline":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code)","datePublished":"2016-06-02T22:45:28+00:00","dateModified":"2016-09-01T14:04:55+00:00","mainEntityOfPage":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/"},"wordCount":501,"commentCount":0,"image":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#primaryimage"},"thumbnailUrl":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","articleSection":["Dynamics NAV\/365 BC"],"inLanguage":"pt-PT","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/","url":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/","name":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code) - Ricardo Paiva Moinhos","isPartOf":{"@id":"https:\/\/ricardomoinhos.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#primaryimage"},"image":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#primaryimage"},"thumbnailUrl":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","datePublished":"2016-06-02T22:45:28+00:00","dateModified":"2016-09-01T14:04:55+00:00","author":{"@id":"https:\/\/ricardomoinhos.com\/#\/schema\/person\/16dcfdd54ec1c46bd1941659739de4cc"},"breadcrumb":{"@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#breadcrumb"},"inLanguage":"pt-PT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/"]}]},{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#primaryimage","url":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","contentUrl":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","width":375,"height":375},{"@type":"BreadcrumbList","@id":"https:\/\/ricardomoinhos.com\/pt\/sql-profiler-commits-nav\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ricardomoinhos.com\/"},{"@type":"ListItem","position":2,"name":"Using SQL Profiler to find COMMITs (and other Dynamics NAV code)"}]},{"@type":"WebSite","@id":"https:\/\/ricardomoinhos.com\/#website","url":"https:\/\/ricardomoinhos.com\/","name":"Ricardo Paiva Moinhos","description":"Welcome","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ricardomoinhos.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-PT"},{"@type":"Person","@id":"https:\/\/ricardomoinhos.com\/#\/schema\/person\/16dcfdd54ec1c46bd1941659739de4cc","name":"Ricardo Paiva Moinhos","image":{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/secure.gravatar.com\/avatar\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/248366f4e615e182964f85f799c6e33cbd541a6f4ca7ee948fc16d1c14030c76?s=96&d=mm&r=g","caption":"Ricardo Paiva Moinhos"},"url":"https:\/\/ricardomoinhos.com\/pt\/author\/ricardopaiva\/"}]}},"jetpack_featured_media_url":"https:\/\/ricardomoinhos.com\/wp-content\/uploads\/2014\/06\/PT_c_Dyn-NAV-2013.png","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/posts\/301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/comments?post=301"}],"version-history":[{"count":21,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/posts\/301\/revisions"}],"predecessor-version":[{"id":339,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/posts\/301\/revisions\/339"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/media\/138"}],"wp:attachment":[{"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/media?parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/categories?post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ricardomoinhos.com\/pt\/wp-json\/wp\/v2\/tags?post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}