{"id":141,"date":"2012-04-20T15:27:53","date_gmt":"2012-04-20T14:27:53","guid":{"rendered":"http:\/\/www.darrencoupland.com\/blog\/?p=141"},"modified":"2012-08-13T22:29:16","modified_gmt":"2012-08-13T21:29:16","slug":"run-a-dynamics-ax-sql-statement-as-an-api-cursor-in-ssms","status":"publish","type":"post","link":"https:\/\/www.darrencoupland.com\/blog\/2012\/04\/run-a-dynamics-ax-sql-statement-as-an-api-cursor-in-ssms\/","title":{"rendered":"Run a Dynamics AX SQL statement as an API Cursor in SSMS"},"content":{"rendered":"

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.<\/p>\n

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.<\/p>\n

Simply replace the red<\/span> text below with all the query variables and replace the blue<\/span> text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.<\/p>\n

TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.<\/p>\n

———————————————-
\n— Normal FFO Cursor
\n———————————————-<\/p>\n

— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen\u00a0(http:\/\/code.msdn.com\/dynamicsperf<\/a>)<\/p>\n

declare @p1 int
\nset @p1=NULL
\ndeclare @p2 int
\nset @p2=0
\ndeclare @p5 int
\n—
\n— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
\n—
\nset @p5=16+4096+8192+16384
\ndeclare @p6 int
\nset @p6=8193
\ndeclare @p7 int
\n—
\n— Number of Rows for AutoFetch.
\n— This is calculated by Maximum Buffer Size (24K default) \/ Row Length
\n—
\nset @p7=4
\nexec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)<\/span>‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′<\/span>
\n— @p2 contains cursor handle for fetch call
\nexec sp_cursorfetch @p2,2,1,@p7<\/p>\n","protected":false},"excerpt":{"rendered":"

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.<\/p>\n

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query in this post.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[26,59,22,25,60],"class_list":["post-141","post","type-post","status-publish","format-standard","hentry","category-dynamics-ax-2009","tag-api-cursor","tag-dynamics-ax-2009","tag-performance","tag-scripts","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/comments?post=141"}],"version-history":[{"count":0,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/141\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/media?parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/categories?post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/tags?post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}