tag:blogger.com,1999:blog-74014799359602224332024-03-05T17:58:25.510-08:00Reuben Gathright's AXAPTADynamics AX and AXAPTA code discussion.Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7401479935960222433.post-45117321715293024602012-03-12T12:08:00.003-07:002012-03-12T12:21:16.049-07:00Attending Convergence 2012 In Houston, TXI will be attending the AX Convergence 2012 users conference in Houston, Texas next week.<br /><br />My goals will be:<br />1) Determine how an organization with users that perform multiple-roles for the company will be licensed... Enterprise CAL?<br />2) What type of performance increases in the communication speed between the AOS (Application Object Server) and SQL Server can we expect?<br />3) How much commitment does Microsoft have towards making the transition to SQL Reporting Services in AX 2012 a smooth one for existing AX 4.0 and AX 2009 customers?<br /><br />In short, there are a lot of changes under hood of the next model of Dynamics AX 2012 and we as a community should get prepared by asking questions and seeking answers before committing to this product.<br /><br />If you wish to contact me at the conference, send a message to my Twitter account:<br /><a href="https://twitter.com/#%21/rgathright">https://twitter.com/#!/rgathright</a><br /><br />Sources:<br /><a href="http://www.microsoft.com/dynamics/convergence/houston12/">http://www.microsoft.com/dynamics/convergence/houston12/</a>Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com0tag:blogger.com,1999:blog-7401479935960222433.post-29807192310685491892010-11-11T07:29:00.000-08:002010-11-11T07:35:21.827-08:00The AMD 1090T Processor Works Well As A Dynamic AX Server<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi00bZ-qimK-X5TCrc5TN9o27fKYkKtz3zedVRr_Hjel0CCDmqKzNnUo23_Vfc_T5gXMeEpc8cF43i7du-nK-d0u27WLmJJ7-5iFSN6Zp5YBo7BPah6mv5kZngD7UaoQIUbvp3hAregvJsR/s1600/amd_Phenom_II_x6_die_clear-cropped.jpg"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 387px; height: 270px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi00bZ-qimK-X5TCrc5TN9o27fKYkKtz3zedVRr_Hjel0CCDmqKzNnUo23_Vfc_T5gXMeEpc8cF43i7du-nK-d0u27WLmJJ7-5iFSN6Zp5YBo7BPah6mv5kZngD7UaoQIUbvp3hAregvJsR/s400/amd_Phenom_II_x6_die_clear-cropped.jpg" alt="" id="BLOGGER_PHOTO_ID_5538315689890830834" border="0" /></a><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />I have been using the <a href="http://www.epinions.com/review/3_2GHz_AMD_Phenom_II_X6_Six_Core_Processor_1090T_AM3_OEM_Black_Edition_HDT90ZFBK6DGR/content_527182171780">AMD x6 Phenom II 1090T Black Edition Processor</a> now for over six months as a Dynamic AX development server. The processor provides plenty of memory bandwidth for SQL Server and works great in 64 bit mode.<br /><br />A new AMD 1100T has been announced is due to arrive in time for the Christmas 2010 holiday season but the AMD 1090T price has been cut so it may be an even more attractive option!<br /><br />Click here to read about my experiences and to see full benchmarks:<br /><a href="http://www.epinions.com/review/3_2GHz_AMD_Phenom_II_X6_Six_Core_Processor_1090T_AM3_OEM_Black_Edition_HDT90ZFBK6DGR/content_527182171780">http://www.epinions.com/review/3_2GHz_AMD_Phenom_II_X6_Six_Core_Processor_1090T_AM3_OEM_Black_Edition_HDT90ZFBK6DGR/content_527182171780</a>Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com1tag:blogger.com,1999:blog-7401479935960222433.post-79543499585121381522010-03-01T11:31:00.000-08:002010-03-30T16:56:24.681-07:00Are SSD Hard Drives An Option For Increasing SQL Server 2005 And Dynamics AX 4.0 Performance?<a href="http://di1.shopping.com/images1/pi/6a/86/9b/49567812-177x150-0-0.jpg"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 177px; FLOAT: left; HEIGHT: 150px; CURSOR: hand" border="0" alt="" src="http://di1.shopping.com/images1/pi/6a/86/9b/49567812-177x150-0-0.jpg" /></a><br /><div>In this Dynamics AX 4.0 hard drive shooting match, I wanted to find a solid performing 2.5” form factor product that could be used on my <a href="http://www.epinions.com/review/Hewlett_Packard_StorageWorks_MSA70_41780/content_439227944580">HP MSA70 RAID Array</a>. The <a href="http://www.epinions.com/review/Hewlett_Packard_StorageWorks_MSA70_41780/content_439227944580">Hewlett Packard Storage Works MSA70</a> will accept SATA 2.5” hard drives but the question is which one to buy!<br /><br />Test Server Configuration<br /><a href="http://www.epinions.com/review/pr-ASUS_Intel_875P_Socket_604_ATX_Audio_LAN_RAID_SATA150_ncch-dl_Motherboard/content_500004064900">ASUS NCCH-DL Motherboard</a><br /><a href="http://www.epinions.com/review/Processor_Xeon_3_4_Ghz_800mhz_2mb_L2_Cache_40k2512_IBM_40K2512_0000435908469/content_499236703876">http://www.epinions.com/review/Processor_Xeon_3_4_Ghz_800mhz_2mb_L2_Cache_40k2512_IBM_40K2512_0000435908469/content_499236703876</a><br /><a href="http://www.epinions.com/review/Transcend_32GB_SSD_2_5_Inch_SATA_MLC_Hard_Drive/content_491971645060">Transcend 32GB SSD, 2.5- Inch, SATA, MLC SATA Hard Drive</a><br /><a href="http://www.epinions.com/review/PDP_Systems_Patriot_Warp_32GB_Solid_State_SATA_300_2_5_Hard_Drive/content_491289022084">PDP Systems Patriot Warp 32GB Solid State - SATA-300, 2.5 32 GB SATA Hard Drive</a><br /><a href="http://www.epinions.com/review/Western_Digital_250GB_Scorpio_Black_SATA_7200_RPM_2_5IN_16MB_Bulk_OEM_WD2500BEKT_250_GB_IDE_Hard_Drive/content_492797726340">Western Digital 250GB Scorpio Black SATA 7200 RPM 2.5IN 16MB WD2500BEKT 250 GB Hard Drive</a><br /><br />The test server does not use SAS technology but it does implement a software level raid that I find easy to work with. I was able to get full control over the stripping in my drives.<br /><br /><a href="http://www.epinions.com/review/Transcend_32GB_SSD_2_5_Inch_SATA_MLC_Hard_Drive/content_491971645060"><span style="FONT-STYLE: italic">Transcend 32Gb SSD:</span></a><br />In both my ASUS NCCH-DL server and a SATA II desktop motherboard, I could only get a sustained average read of 57.5 Mbs. The drive set me back $145.<br /><a href="http://www.epinions.com/review/PDP_Systems_Patriot_Warp_32GB_Solid_State_SATA_300_2_5_Hard_Drive/content_491289022084"><br /><span style="FONT-STYLE: italic">PDP Systems Patriot Warp 32Gb SSD:</span></a><br />The drive came to life in my SATA II desktop motherboard with an average read of 145.4Mbs and an average read of 101.3Mbs on the ASUS NCCH-DL server. The drive set me back $130.<br /><a href="http://www.epinions.com/review/Western_Digital_250GB_Scorpio_Black_SATA_7200_RPM_2_5IN_16MB_Bulk_OEM_WD2500BEKT_250_GB_IDE_Hard_Drive/content_492797726340"><span style="FONT-STYLE: italic"><br />WD2500BEKT 250Gb 2.5” SATA Hard Drive:</span></a><br />The Western Digital WD2500BEKT has 16Mb of cache and spins at 7200RPM. Many people today would consider this just a slow notebook hard drive but in the right computer or RAID Array this drive can become a powerful force. I got a sustained average read of 139.4 Mbs using this hard drive on a SATA II desktop motherboard. I placed the drive in my ASUS NCCH-DL motherboard and could never get an average read rate better than 51.4Mbs. Do not be misled by these results though. All they show is that the drive is optimized for SATA II operation. The drive set me back $70.<br /><br /><span style="FONT-WEIGHT: bold">Summary</span><br />While I have not tested the Intel SSD hard drive offerings, I still wanted to share my results with these drives to encourage a form of fiscal restraint for those fellow database administrators looking to increase the performance of their systems. SSD drives are still overpriced for the performance that they offer. You can buy two 250GB hard drives that are just 10Mb slower for the same cost as one of these SSD offerings!</div>Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com2tag:blogger.com,1999:blog-7401479935960222433.post-89170556485275642722010-02-17T09:44:00.000-08:002010-02-17T09:50:57.695-08:00Compacting A Dynamics AX 4.0 SQL Server Database Using Index Scripts<meta equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CRGATHR%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CRGATHR%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CRGATHR%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><!--[if gte mso 9]><xml> <w:worddocument> <w:view>Normal</w:View> <w:zoom>0</w:Zoom> <w:trackmoves/> <w:trackformatting/> <w:punctuationkerning/> <w:validateagainstschemas/> <w:saveifxmlinvalid>false</w:SaveIfXMLInvalid> <w:ignoremixedcontent>false</w:IgnoreMixedContent> <w:alwaysshowplaceholdertext>false</w:AlwaysShowPlaceholderText> <w:donotpromoteqf/> <w:lidthemeother>EN-US</w:LidThemeOther> <w:lidthemeasian>X-NONE</w:LidThemeAsian> <w:lidthemecomplexscript>X-NONE</w:LidThemeComplexScript> <w:compatibility> <w:breakwrappedtables/> <w:snaptogridincell/> <w:wraptextwithpunct/> <w:useasianbreakrules/> <w:dontgrowautofit/> <w:splitpgbreakandparamark/> <w:dontvertaligncellwithsp/> <w:dontbreakconstrainedforcedtables/> <w:dontvertalignintxbx/> <w:word11kerningpairs/> <w:cachedcolbalance/> <w:usefelayout/> </w:Compatibility> <w:browserlevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathpr> <m:mathfont val="Cambria Math"> <m:brkbin val="before"> <m:brkbinsub val="--"> <m:smallfrac val="off"> <m:dispdef/> <m:lmargin val="0"> <m:rmargin val="0"> <m:defjc val="centerGroup"> <m:wrapindent val="1440"> <m:intlim val="subSup"> <m:narylim val="undOvr"> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:latentstyles deflockedstate="false" defunhidewhenused="true" defsemihidden="true" defqformat="false" defpriority="99" latentstylecount="267"> <w:lsdexception locked="false" priority="0" semihidden="false" unhidewhenused="false" qformat="true" name="Normal"> <w:lsdexception locked="false" priority="9" semihidden="false" unhidewhenused="false" qformat="true" name="heading 1"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 2"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 3"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 4"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 5"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 6"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 7"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 8"> <w:lsdexception locked="false" priority="9" qformat="true" name="heading 9"> <w:lsdexception locked="false" priority="39" name="toc 1"> <w:lsdexception locked="false" priority="39" name="toc 2"> <w:lsdexception locked="false" priority="39" name="toc 3"> <w:lsdexception locked="false" priority="39" name="toc 4"> <w:lsdexception locked="false" priority="39" name="toc 5"> <w:lsdexception locked="false" priority="39" name="toc 6"> <w:lsdexception locked="false" priority="39" name="toc 7"> <w:lsdexception locked="false" priority="39" name="toc 8"> <w:lsdexception locked="false" priority="39" name="toc 9"> <w:lsdexception locked="false" priority="35" qformat="true" name="caption"> <w:lsdexception locked="false" priority="10" semihidden="false" unhidewhenused="false" qformat="true" name="Title"> <w:lsdexception locked="false" priority="1" name="Default Paragraph Font"> <w:lsdexception locked="false" priority="11" semihidden="false" unhidewhenused="false" qformat="true" name="Subtitle"> <w:lsdexception locked="false" priority="22" semihidden="false" unhidewhenused="false" qformat="true" name="Strong"> <w:lsdexception locked="false" priority="20" semihidden="false" unhidewhenused="false" qformat="true" name="Emphasis"> <w:lsdexception locked="false" priority="59" semihidden="false" unhidewhenused="false" name="Table Grid"> <w:lsdexception locked="false" unhidewhenused="false" name="Placeholder Text"> <w:lsdexception locked="false" priority="1" semihidden="false" unhidewhenused="false" qformat="true" name="No Spacing"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 1"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 1"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 1"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 1"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 1"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 1"> <w:lsdexception locked="false" unhidewhenused="false" name="Revision"> <w:lsdexception locked="false" priority="34" semihidden="false" unhidewhenused="false" qformat="true" name="List Paragraph"> <w:lsdexception locked="false" priority="29" semihidden="false" unhidewhenused="false" qformat="true" name="Quote"> <w:lsdexception locked="false" priority="30" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Quote"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 1"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 1"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 1"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 1"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 1"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 1"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 1"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 1"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 2"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 2"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 2"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 2"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 2"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 2"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 2"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 2"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 2"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 2"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 2"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 2"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 2"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 2"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 3"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 3"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 3"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 3"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 3"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 3"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 3"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 3"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 3"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 3"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 3"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 3"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 3"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 3"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 4"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 4"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 4"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 4"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 4"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 4"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 4"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 4"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 4"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 4"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 4"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 4"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 4"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 4"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 5"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 5"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 5"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 5"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 5"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 5"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 5"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 5"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 5"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 5"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 5"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 5"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 5"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 5"> <w:lsdexception locked="false" priority="60" semihidden="false" unhidewhenused="false" name="Light Shading Accent 6"> <w:lsdexception locked="false" priority="61" semihidden="false" unhidewhenused="false" name="Light List Accent 6"> <w:lsdexception locked="false" priority="62" semihidden="false" unhidewhenused="false" name="Light Grid Accent 6"> <w:lsdexception locked="false" priority="63" semihidden="false" unhidewhenused="false" name="Medium Shading 1 Accent 6"> <w:lsdexception locked="false" priority="64" semihidden="false" unhidewhenused="false" name="Medium Shading 2 Accent 6"> <w:lsdexception locked="false" priority="65" semihidden="false" unhidewhenused="false" name="Medium List 1 Accent 6"> <w:lsdexception locked="false" priority="66" semihidden="false" unhidewhenused="false" name="Medium List 2 Accent 6"> <w:lsdexception locked="false" priority="67" semihidden="false" unhidewhenused="false" name="Medium Grid 1 Accent 6"> <w:lsdexception locked="false" priority="68" semihidden="false" unhidewhenused="false" name="Medium Grid 2 Accent 6"> <w:lsdexception locked="false" priority="69" semihidden="false" unhidewhenused="false" name="Medium Grid 3 Accent 6"> <w:lsdexception locked="false" priority="70" semihidden="false" unhidewhenused="false" name="Dark List Accent 6"> <w:lsdexception locked="false" priority="71" semihidden="false" unhidewhenused="false" name="Colorful Shading Accent 6"> <w:lsdexception locked="false" priority="72" semihidden="false" unhidewhenused="false" name="Colorful List Accent 6"> <w:lsdexception locked="false" priority="73" semihidden="false" unhidewhenused="false" name="Colorful Grid Accent 6"> <w:lsdexception locked="false" priority="19" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Emphasis"> <w:lsdexception locked="false" priority="21" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Emphasis"> <w:lsdexception locked="false" priority="31" semihidden="false" unhidewhenused="false" qformat="true" name="Subtle Reference"> <w:lsdexception locked="false" priority="32" semihidden="false" unhidewhenused="false" qformat="true" name="Intense Reference"> <w:lsdexception locked="false" priority="33" semihidden="false" unhidewhenused="false" qformat="true" name="Book Title"> <w:lsdexception locked="false" priority="37" name="Bibliography"> <w:lsdexception locked="false" priority="39" qformat="true" name="TOC Heading"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]--> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><span style="font-size: 10pt; font-family: "Arial","sans-serif";">
<br /><o:p></o:p></span></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p>To implement a Microsoft Visual Source Safe source control solution for the various developers on our companies' Microsoft Dynamics AX 4.0 development environment, we had to first create three new Microsoft Virtual PC 2007 servers that would host the AOS (Application Object Server) and required SQL Server 2005 databases.<span style=""> </span>We were experiencing the growing pains that any IT department with the responsibility of writing their own code solutions faces.<span style=""> </span>Several days of research and testing helped us identify the right configuration for our new Visual Source safe solution, but we were frustrated because the size of our central Dynamics AX 4.0 development database was preventing us from going any further.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">The problem was that we soon discovered that our development AX 4.0 database had a 48Gb MDF file with 28Gb of unused space.<span style=""> </span>In order to host the three virtual servers on the <a href="http://www.epinions.com/review/Hewlett_Packard_HP_COMPAQ_DL360R05_SVR_X_3_0_E5450_4G_RPS_490666_001_Server/content_484215852676Edit">Hewlett Packard ProLiant DL360 G5 server</a> and not exceed the 130Gb of capacity on the RAID array, I will need to reduce the size of this MDF file as much as possible.<span style=""> </span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Before you execute any of my solutions below, consider getting yourself a test server first.<span style=""> </span>A test box will prevent any costly downtime for your company's live server systems and allow you to confirm your proposed solutions before deployment.<span style=""> </span>Additionally, a properly configured Dynamics AX 4.0 test server can act as a standby server if the need should ever arise.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">I created a test server with the following specifications for this exercise:<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><a href="http://www.epinions.com/review/pr-ASUS_Intel_875P_Socket_604_ATX_Audio_LAN_RAID_SATA150_ncch-dl_Motherboard/content_500004064900">ASUS NCCH-DL Motherboard</a><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Dual <a href="http://www.epinions.com/review/Processor_Xeon_3_4_Ghz_800mhz_2mb_L2_Cache_40k2512_IBM_40K2512_0000435908469/content_499236703876">Intel Xeon 3.4 Ghz 800mhz 2mb L2 Cache (40k2512) Processors</a><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Six <a href="http://www.epinions.com/review/Samsung_SpinPoint_T_HD501LJ_500_GB_SATA_II_Hard_Drive/content_493948145284">Samsung SpinPoint T HD501LJ 500 GB SATA II Hard Drives</a><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><a href="http://www.epinions.com/review/Western_Digital_My_Passport_Essential_320GB_External_USB_2_0_Portable_Black_Hard_Drive/content_491650256516">Western Digital My Passport Essential 320GB External USB 2.0 Portable - Black Hard Drive</a><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">To protect the privacy of my data, I changed the name of the called database to YOURDBHERE.<span style=""> </span>You will want to use your database name instead.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Failed Solutions:</span></u><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">#1<span style=""> </span>My first approach was to execute the shrinkfile commands on the database as follows:<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">use YOURDBHERE;<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">dbcc shrinkfile (YOURDBHERE);<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">dbcc shrinkdatabase (YOURDBHERE, TRUNCATEONLY);<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style="font-style: italic;">go</span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">The commands had no effect.<span style=""> </span>I detached and reattached the database then ran the commands again with no effect on the MDF file size.<span style=""> </span>Many database administrators have good success with these commands but with my unruly database, a more robust approach was going to be required.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">#2<span style=""> </span>When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur.<span style=""> </span>My second option is to REINDEX that database.<span style=""> </span>Dynamics AX 4.0 uses over 1300 Tables which makes this a time consuming procedure if you just use the DBCC DBREINDEX ('TableName','Index_Name',fillfactor) command on each.<span style=""> </span>Some may say that you should use the SQL Administration form within Dynamics AX 4.0 but I disagree because it is does not provide any detail about the paging in your database and as a result makes you a blind administrator.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">My first solution involved a semi-automated reporting tool that allows me to observe where my most critical problems are and a series of generated statements to fix them using two queries.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>Step 1)<span style=""> </span>Run This Query Within The Desired Database To Review Index Status:<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">SELECT<span style=""> </span>TOP (20) OBJECT_NAME(DMV.object_id) AS TABLE_NAME, SI.name AS INDEX_NAME, avg_fragmentation_in_percent AS FRAGMENTATION_Percent, <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>Fragment_Count, page_count, Record_Count<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">FROM<span style=""> </span>sys.dm_db_index_physical_stats(DB_ID(N'YOURDBHERE'), NULL, NULL, NULL, 'DETAILED') AS DMV LEFT OUTER JOIN<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>sys.indexes AS SI ON DMV.OBJECT_ID = SI.object_id AND DMV.INDEX_ID = SI.index_id<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style="font-style: italic;">WHERE (avg_fragmentation_in_percent > 80) AND (record_count > 1000)</span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>Step 2)<span style=""> </span><span style="font-style: italic;">SELECT TOP (10) 'DBCC INDEXDEFRAG (YOURDBHERE,' + CAST(OBJECT_NAME(DMV.object_id) AS NVARCHAR(50)) </span><o:p style="font-style: italic;"></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span><span style=""> </span>+ ',' + CAST(SI.name AS NVARCHAR(50)) + ')' AS SYNTAX_EXECUTE<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">FROM<span style=""> </span>sys.dm_db_index_physical_stats(DB_ID(N'YOURDBHERE'), NULL, NULL, NULL, 'DETAILED') AS DMV LEFT OUTER JOIN<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>sys.indexes AS SI ON DMV.OBJECT_ID = SI.object_id AND DMV.INDEX_ID = SI.index_id<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style="font-style: italic;">WHERE (avg_fragmentation_in_percent > 80) AND (record_count > 1000)</span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>Step 3)<span style=""> </span>Now copy all of the output text into a query window for the affected database and execute.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>Step 4)<span style=""> </span>For those tricky indexes, let's purge them with this and repeate step 2 through 3 again!<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">SELECT<span style=""> </span>TOP (20) 'DBCC DBREINDEX (' + CAST(OBJECT_NAME(DMV.object_id) AS NVARCHAR(50)) <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>+ ')' AS SYNTAX_EXECUTE<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">FROM<span style=""> </span>sys.dm_db_index_physical_stats(DB_ID(N'YOURDBHERE'), NULL, NULL, NULL, 'DETAILED') AS DMV LEFT OUTER JOIN<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal; font-style: italic;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style=""> </span>sys.indexes AS SI ON DMV.OBJECT_ID = SI.object_id AND DMV.INDEX_ID = SI.index_id<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><span style="font-style: italic;">WHERE (avg_fragmentation_in_percent > 80) AND (record_count > 1000)</span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">For now, this work on a micro-scale to help me solve my most immediate problems.<span style=""> </span>The query has its limitations on my hardware though.<span style=""> </span>I cannot restrict the where clause any more nor can I return more than 20 rows.<span style=""> </span>Returning a margin of less than 80 for fragmentation percent or record_count less than 1000 will speed this up but return fewer effective indexes to defragment.<span style=""> </span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Up to this point in the article, I have taken an entire day with no success.<span style=""> </span>The ultimate result was finding a Microsoft Support article that admits that there is a code failure or bug in SQL Server that causes this problem on BLOB data types of text, ntext and image.<span style=""> </span>If you have Microsoft SQL Server 2005, then visit this url to find their article with a solution that requires copying and deletion of problematic database tables.<span style=""> </span>http://support.microsoft.com/default.aspx?scid=kb;en-us;324432<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">I soon realized that I could recreate their complicated BLOB search by both reindexing the database and searching for blobs at the same time.<span style=""> </span>You will also notice that Microsoft is indirectly admitting that you do not need this blob data since it is only associated with the nodes in the database.<span style=""> </span>Database administrators who work with table structures that exceed 100 objects are also not given an effective means of finding the offending BLOB data discussed in the article either.<span style=""> </span>As a result, I decided to run a series of queries that repair my database structure and then allow the system to purge any unnecessary data through the use of the REPAIR_ALLOW_DATA_LOSS command.<span style=""> </span>Please note that I am running this command because I have exhausted all other forms of removing the BLOB data.<span style=""> </span>DBCC commands are very powerful and have the ability to remove critical business data that you may need.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Prerequisites:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">After running this query window statement on the database with no effect, I then applied SP3 service pack update to my SQL Server 2005 SP2 database engine.<span style=""> </span><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">The service pack along with these system queries with integrated reporting for blob candidates through a table that I created on the database helped me reach a solution to my problem.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">STEP 1:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Put your database in single user mode.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">STEP 2:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DECLARE</span><span style="font-size: 10pt; font-family: "Courier New";"> @indid <span style="color: blue;">int<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DECLARE</span><span style="font-size: 10pt; font-family: "Courier New";"> @TableName <span style="color: blue;">sysname<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DECLARE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_tblfetch <span style="color: blue;">CURSOR</span> <span style="color: blue;">FOR<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">SELECT</span><span style="font-size: 10pt; font-family: "Courier New";"> Table_Name <span style="color: blue;">FROM</span> <span style="color: green;">information_schema.tables</span> <span style="color: blue;">WHERE</span> table_type <span style="color: gray;">=</span> <span style="color: red;">'base table'<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">OPEN</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_tblfetch<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">FETCH</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> cur_tblfetch <span style="color: blue;">INTO</span> @TableName<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">WHILE</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: fuchsia;">@@FETCH_STATUS</span> <span style="color: gray;">=</span> 0<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">BEGIN<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DECLARE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_indfetch <span style="color: blue;">CURSOR</span> <span style="color: blue;">FOR<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">SELECT</span><span style="font-size: 10pt; font-family: "Courier New";"> indid <span style="color: blue;">FROM</span> sysindexes <span style="color: blue;">WHERE</span> id <span style="color: gray;">=</span> <span style="color: fuchsia;">OBJECT_ID</span> <span style="color: gray;">(</span>@TableName<span style="color: gray;">)</span> <span style="color: gray;">and</span> keycnt <span style="color: gray;">></span> 0<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">OPEN</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_indfetch<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">FETCH</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> cur_indfetch <span style="color: blue;">INTO</span> @indid<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">WHILE</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: fuchsia;">@@FETCH_STATUS</span> <span style="color: gray;">=</span> 0<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">BEGIN<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">SELECT</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: red;">'INDEXDEFRAG DBREINDEX of sysindexes.index_id = '</span> <span style="color: gray;">+</span> <span style="color: fuchsia;">convert</span><span style="color: gray;">(</span><span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>5<span style="color: gray;">),</span> @indid<span style="color: gray;">)</span> <span style="color: gray;">+<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: red;">'for table: '</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: gray;">+</span> <span style="color: fuchsia;">rtrim</span><span style="color: gray;">(</span>@TableName<span style="color: gray;">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">IF</span><span style="font-size: 10pt; font-family: "Courier New";"> @indid <span style="color: gray;"><></span> 255 <span style="color: blue;">DBCC</span> INDEXDEFRAG <span style="color: gray;">(</span>0<span style="color: gray;">,</span> @TableName<span style="color: gray;">,</span> @indid<span style="color: gray;">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">IF</span><span style="font-size: 10pt; font-family: "Courier New";"> @indid <span style="color: gray;"><></span> 255 <span style="color: blue;">DBCC</span> DBREINDEX <span style="color: gray;">(</span>@TableName<span style="color: gray;">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">IF</span><span style="font-size: 10pt; font-family: "Courier New";"> @indid <span style="color: gray;">=</span> 255 <span style="color: blue;">INSERT</span> <span style="color: blue;">INTO</span> AABlobs <span style="color: gray;">(</span>BlobTable<span style="color: gray;">)</span> <span style="color: blue;">VALUES</span><span style="color: gray;">(</span>@TableName<span style="color: gray;">);<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">FETCH</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> cur_indfetch <span style="color: blue;">INTO</span> @indid<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">END<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">CLOSE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_indfetch<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DEALLOCATE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_indfetch<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">FETCH</span><span style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> cur_tblfetch <span style="color: blue;">INTO</span> @TableName<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">END<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">CLOSE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_tblfetch<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">DEALLOCATE</span><span style="font-size: 10pt; font-family: "Courier New";"> cur_tblfetch </span><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">STEP 3:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">use</span><span style="font-size: 10pt; font-family: "Courier New";"> YOURDBHERE<span style="color: gray;">;<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New";">go</span><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: gray;">DBCC CHECKDB (YOURDBHERE, REPAIR_ALLOW_DATA_LOSS);</span><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">STEP 4:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">use</span><span style="font-size: 10pt; font-family: "Courier New";"> YOURDBHERE<span style="color: gray;">;<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New";">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New"; color: blue;">dbcc</span><span style="font-size: 10pt; font-family: "Courier New";"> shrinkfile <span style="color: gray;">(</span>YOURDBHERE<span style="color: gray;">);<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Courier New";">go<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><i><u><span style="font-size: 10pt; font-family: "Arial","sans-serif";">STEP 5:</span></u></i></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">From SQL Server 2005 Enterprise Manager, run the Shrink File program on the Data file and choose the "Reorganize pages before releasing unused space" option.<span style=""> </span>I recommend selecting at least 500 Mb greater than minimum as your goal.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><b><span style="font-size: 10pt; font-family: "Arial","sans-serif";">Conclusion</span></b><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";">My database shrunk from 48Gb to 14.5Gb using these five steps.<span style=""> </span>I am providing this discussion as a way of helping other Dynamics AX 4.0 system administrators who not getting the proper answers to their problems.<span style=""> </span>You can do this without the help of costly consultants who just call Microsoft Technical Support anyway.<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p> </o:p></span></p><p class="MsoNormal" style="margin-bottom: 0.0001pt; line-height: normal;"><span style="font-size: 10pt; font-family: "Arial","sans-serif";"><o:p>
<br /></o:p></span></p> <span style="font-size: 10pt; line-height: 115%; font-family: "Arial","sans-serif";">I take no responsibility for the validity of my comments and solutions.<span style=""> </span>Use the solutions in this article on your data at your own risk!<span style=""> </span></span>Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com1tag:blogger.com,1999:blog-7401479935960222433.post-39730832905653032382008-12-03T08:45:00.000-08:002008-12-03T08:46:14.530-08:00Creating a new Axapta 4.0 document in X++ code.To satisfy the reporting style of a legacy system, I needed my new Dynamics AX 4.0 system to create a new document note that contains the customer's item code. Yes, Dynamics AX 4.0 does have External Item descriptions and they are populated with the values I want to display but to avoid rebuilding the layout of reports I decided to use external document notes to display these values for the customer's benefit on their sales documentation. The external notes are functioning properly and appearing beneath the respective sales lines in reports.<br /><br />Here is the method I found to create a new DocuRef: <br />public static void create(recId docuRefRecId, tableId refTableId, recId refRecId)<br /><br />I found some help in MSDN, specifically a mapping of the the 3 arguments needed for the create method in relation to the SalesTable:<br />DocuRef.RefCompanyId==SalesTable.dataAreaId<br />DocuRef.RefTableId==SalesTable.TableId<br />DocuRef.RefRecId==SalesTable.RecId<br />However, I am doing this for the SalesQuotationLine table. My first instinct was to try and use the create method to do this. Yet, experience kicks in and you realize that declaring a variable of type DocuRef and setting the values of the RefTableID and RefRecID then using the insert() method of the new variable is the best course of action. I attached the following code to the modified method of SalesQuotationLine_DS.ItemID.<br /><br /> DocuRef tblDocuRef;<br /> ;<br /> super();<br /> ttsbegin;<br /> tblDocuref.RefTableId = salesquotationline.TableId;<br /> tbldocuref.RefRecId = salesquotationline.RecId;<br /> tbldocuref.RefCompanyId = salesquotationline.dataAreaId;<br /> tbldocuref.TypeId = "Notes";<br /> tbldocuref.Restriction = DocuRestriction::External;<br /> tblDocuref.Name = "CUSTOMER ITEM CODE";<br /> tblDocuref.Notes = "CUST ITEM CODE: 00000034343";<br /> tbldocuref.insert();<br /> ttscommit;<br /><br />The code worked, but since the new record had not been committed to the database yet, my RefRecID had a value of 0. My solution then turned into a post update or in other words writing the DocuRef record after the SalesQuotationLine has been committed to the database. I placed my code in the write method of the SalesQuotationLine datasource right after the salesQuotationLine and SalesQuotationTable reinitialization calls (reread(), refresh()) and removed it from the modified method. The following is the code that works flawlessly for me:<br /><br /> //[rg] 12/3/2008 first make sure the item has an external item id.<br /> if (CustVendExternalItem::find(ModuleInventPurchSalesVendCustGroup::Cust,salesQuotationline.ItemId,"AllBlank",SalesquotationTable.CustAccount).ExternalItemId)<br /> { //[rg] 12/3/2008 now make sure we did not already write this record.<br /> if (DocuRef::find(salesquotationline.dataAreaId,salesquotationline.TableId,salesquotationline.RecId,salesquotationline.createdDate).Name)<br /> {<br /> tbldocuref.RecId = 3; //[rg] remove this code.<br /> }<br /> else<br /> {<br /> ttsbegin;<br /> tblDocuref.RefTableId = salesquotationline.TableId;<br /> tbldocuref.RefRecId = salesquotationline.RecId;<br /> tbldocuref.RefCompanyId = salesquotationline.dataAreaId;<br /> tbldocuref.TypeId = "Note";<br /> tbldocuref.Restriction = DocuRestriction::External;<br /> tblDocuref.Name = "CUSTOMER ITEM CODE";<br /> tblDocuref.Notes = "CUSTOMER ITEM CODE: " + CustVendExternalItem::find(ModuleInventPurchSalesVendCustGroup::Cust,salesQuotationline.ItemId,"AllBlank",SalesquotationTable.CustAccount).ExternalItemId;<br /> tbldocuref.insert();<br /> ttscommit;<br /> }<br /> }<br /><br />As you can see I am pulling my note from the CustVendExternalItem.ExternalItemID field. I am happy with the results because I can now see the external note clearly on my Sales Confirmation report. I suggest using the Docu::Copy method to transfer these notes, upon confirmation of quote, to the sales order.Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com2tag:blogger.com,1999:blog-7401479935960222433.post-61940581172774356632008-12-02T14:45:00.000-08:002008-12-02T14:49:32.589-08:00How to transfer Sales Quotation Document to Sales Orders upon confirmation in AX 4.0If you need to move Sales Quotation Document reference notes automatically upon confirmation in AX 4.0 to your new Sales Order follow these steps:<br /><br />1) Open the class: SalesQuotationEditLinesForm_Sales_Confir<br />2) In method: createSalesLines paste this code above second to last }<br /> <br />Docu::copy(SalesQuotationLineUpdate,SalesLine);<br /><br />3) In method: createSalesTable past this code above second to last }<br /><br />Docu::copy(SalesQuotationTable,SalesTable);Reuben Gathrighthttp://www.blogger.com/profile/12755927730966927387noreply@blogger.com0