Monday, July 30, 2012

SQL Server CTEs and Recursive Parent Grouping in Advanced Mode Group Properties in SSRS

Hello!

Few weeks ago, I was developing an interesting report in SSRS which pulls out data from AX - Projects module.

It was interesting for 2 reasons:

1. I learned to use more effectively SQL Server CTE's (Common table expressions) while developing this report.

From a layman's perspective, CTEs were introduced since SQL Server 2005 and it’s more useful to do a recursive search (display hierarchical data e.g. employee-manager, BOM etc). In my scenario, I had to do a recursive search for Project-Parent Id in my Stored proc)

Below sharing some links, which will explain further more in detail about CTE's and how to implement them.



2.  The second concept was about recursive parent Grouping (in SSRS)

In SSRS, we can group the child field based on the recursive parent key (There’s an option in the Edit Group > Advanced Section to key in the Recursive parent field) – explained here in this blog - http://sql-bi-dev.blogspot.com.au/2010/08/recursive-hierarchy-group-in-ssrs-2008.html

Wednesday, July 25, 2012

Quick Tip - Saving Macro enabled worksheet (Excel 2010)

Have been playing around with VBA coding these days (with Excel 2010) as we are building a Budget Template to capture Budgets for various projects and then consolidate it into one excel workbook so that it can be imported into AX.

VB and VBA has been one of my favorite languages since I started programming back in 2000. I still love to work in VB editor if i get a chance and one of my first project was building a Review Toolbar using VBA (Word Macros), Very interesting then :)

Now after 10 years, there has been quite some changes with the way macros are handled especially with Excel based macros. In Excel 2010, if you want to save a sheet/workbook then while saving you have to use the "Excel Macro Enabled Worksheet" rather than the default Excel option.

Below Link helped me To save macros http://www.dummies.com/how-to/content/saving-and-using-an-excel-2010-macroenabled-workbo.html

Wednesday, July 18, 2012

Review - Dynamics AX 2012 Development Cookbook

I didn't get much of  a chance to do a deep dive into Dynamics AX 2012 Development Cookbook http://www.packtpub.com/microsoft-dynamics-ax-2012-development-cookbook/book

In my opinion the book comes handy for a developer to directly jump to the topic which they are interested in rather than scanning chapter by chapter.

However I think some more areas the book could have covered is:

a. Enterprise Portal
b. AX Administration (Models administration etc)
c. Security
d. Reporting and Analytics

Maybe I'm asking bit more from a development cookbook, Just my 2 cents - with AX 2012, there's a lot of integration happening with other Microsoft technology stack like VS.Net, SQL, SSRS, SSAS, etc so maybe the future versions of the book should cover more on these areas as well.

However, I would recommend this book for a developer as it's quite useful when it comes to day-to-day development tasks. Again Good work by the author and keep it up!

Friday, July 13, 2012

Troubleshooting AX AOS Peformance on VM

Hello!
Thought to share one odd issue regarding AX performance

Issue:

We have one of our DEV AOS's running on a separate VM and suddenly we were experiencing performance degradation

Solution:

Upon troubleshooting with help of our networking guys, we figured out that the culprit was cqmgserv.exe.
We no longer needed this HP Monitoring service so disabled it and that solved the issue.

Tuesday, July 3, 2012

CU3 Update for Dynamics AX 2012 Released


Microsoft has released Cumulative Update 3 (CU3) for Dynamics AX 2012 which contains hotfixes that were fixed since the release of Microsoft Dynamics AX 2012. This article contains information about how to obtain the cumulative update and about the issues that are fixed by the cumulative update.


Note The build number of this cumulative update package is 6.0.1108.670.

Read more about the update by going through the below link (You might need to Sign in with your Live Id for Partnersource/CustomerSource)
https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2709934

Sunday, July 1, 2012

Troubleshooting Sharepoint Search Errors - Part 2

A while ago, I had to put my Sharepoint hat to troubleshoot the below issues. If you have worked on Sharepoint as an Admin, you will agree with me that Sharepoint errors are more complex to troubleshoot compared to other tools in MS Stack. The error messages sometimes don't convey the right meaning. 

Ok, let's jump into the various errors which was logged in Event Viewer and the solution for them

Error #2
Event Type:        Error
Event Source:    Office Server Search
Event Category:  Search service
Event ID:              10038
Description:
Query machine <xyz> has been taken out of rotation due to this error: The object is not initialzed.   0x8004180b. It will be retried in 15 seconds. Component: 3a87986b-8ffe-41db-ae22-5e3fd1b9f4d9

Error #3
Event Type:        Warning
Event Source:    Office Server Search
Event Category:  Search service
Event ID:              10039
Description:
Retry of query machine <xyz> has failed with error: The object is not initialzed.   0x8004180b.  It will be retried again in 60 seconds. Component: 3a87986b-8ffe-41db-ae22-5e3fd1b9f4d9

Error #4
Event Type:        Error
Event Source:    Office Server Search
Event Category:  Content index server
Event ID:              4127
Description:
Content index on Portal_Content could not be initialized. Error The system cannot find the file specified.   0x80070002.Component: 3a87986b-8ffe-41db-ae22-5e3fd1b9f4d9

Error# 5 (While Searching from Portal, you get the below errors)
Your search cannot be completed because of a service error”
"The search request was unable to connect to the Search Service"

Error #6

Event Type:       Error
Event Source:    Office SharePoint Server
Event Category: Office Server Shared Services
Event ID:          6482
Description:
Application Server Administration job failed for service instance Microsoft.Office.Server.Search.Administration.SearchServiceInstance (8fd1e03c-96df-4b7a-bd33-8da3fa924f03).

Reason: Object 3a87986b-8ffe-41db-ae22-5e3fd1b9f4d9 not found.

Techinal Support Details:
System.Collections.Generic.KeyNotFoundException: Object 3a87986b-8ffe-41db-ae22-5e3fd1b9f4d9 not found.
   at Microsoft.Office.Server.Search.Administration.SearchApi.get_App()
   at Microsoft.Office.Server.Search.Administration.SearchApi.SetUsersPermittedToQuery(String[] userNames, Boolean force)
   at Microsoft.Office.Server.Search.Administration.SearchServiceInstance.Synchronize()
   at Microsoft.Office.Server.Administration.ApplicationServerJob.ProvisionLocalSharedServiceInstances(Boolean isAdministrationServiceJob)


Solution for Errors 2, 3, 4, 5 and 6

Sometime back, I moved our Sharepoint Databases to a different DB Server and since then the Search was broken; maybe the index file got corrupted during the move.

I had to do the following inorder to bring back the search:

1.       Go to CA > Operations > Services on Server > Click on Office Sharepoint Server Search > Configure Sharepoint Server Search Service and checked Use this server for serving search queries












          Then go to CA > Application Management > Manage this Farms Shared Services > New Shared Services Provider to fix the indexer as <<prod-servername>> and change the index location here toD:\SharepointIndex\Config as C:\ was getting full.










3.       Go to CA > Application Management > Create or configure this farm’s shared services.
4.       Go to Manage this Farms Shared Services page, click the SSP
5.       On the Shared Services Administration Home page, click Search settings in the Search section.
6.       On the Configure Search Settings page, click Reset all crawled content in the Crawl Settings section.
7.       By default, the Deactivate search alerts during reset check box is selected on the Reset Crawled Content page. This option prevents users who have search alerts configured from being sent unnecessary e-mail messages when the crawled content is reset. Click to clear this check box if you do not want to disable alerts during the reset operation.
8.       Click Reset Now.
9.       In the message box, click OK to confirm that you want to reset the search content index.
1 
Go back to the Shared Services Administration Home page, click Search settings in the Search section.
11.   Click Content sources and crawl schedules.
12.   Drop the menu down for the Source you want to crawl, click Start Full Crawl.


Below is a list of references/links which i found useful during my troubleshooting exercise: (Maybe someone might find useful out there)