Exporting common AX 2009 Environment Configuration Settings

Wouldn’t it be great not having to re-configure certain configuration settings in your non-production environment after you’ve done an application/data refresh?

Well you can by exporting your common config settings using the Microsoft Database Publishing Wizard.

What you end up with is a SQL script file to run against your AX database once you’ve completed your database restore, the only pre-requisites are you need the Database Publishing Wizard tool and also you need to export your settings before you overwrite your data (the last one is pretty obvious!!!).
So the procedure would be as follows:

  •  take a copy of the config settings in your non-production environment whose data is to be overwritten
  • refresh your non-production environment with data from another environment
  • execute the SQL script containing the config settings exported prior to the refresh into your non-production environment to replace them

Download the guide here:
Exporting-common-AX 2009-Environment-Configuration-Settings.pdf (2519 downloads)

You can add to the config/tables being exported for any other config settings you require keeping in your environment.

Reporting Services (SSRS) Report Errors after performing a Sharepoint 2010 Site Collection Restore

Issue:

After a restore of a Sharepoint 2010 Site Collection is performed you may receive the following error when attempting to run an SSRS report if you are running Reporting Services in Sharepoint Integrated Mode:

The SharePoint site with the id {GUID} could not be found. (rsSPSiteNotFound)

Resolution:

Resolving the issue is a relatively simple process:

  1. Browse to the Data Connections on the your Sharepoint Site
  2. For each Data Source that is used in any SSRS SP2010 Integrated Report perform the following:
    2.1 Click on the Data Source in the ‘Name’ column and select ‘Edit Data Source Definition’
    2.2 Click on ‘Test Connection’
    2.3 Click on ‘OK’ if the connection test is successful
  3. Re-run the SSRS Report and it should work okay now

Checking AX2009 ‘EntireTable’ Caching Setup

We are going to look at how you can review your ‘EntireTable’ Cache settings on tables within the AOT and why performance can be improved if your settings are incorrect.

When a table’s Cache Lookup in the AOT is set to ‘EntireTable’ it means a maximum of 2,000 records are loaded into the AOS servers cache for that table which saves having to go back to the SQL Server Database each time a query on that table is performed.

Each time a record is Inserted / Updated / Deleted the cache on the AOS has to be dropped and reloaded for that particular table, if you have a large table that is set to ‘EntireTable’ type caching and the table is being changed often this can cause detrimental effects on performance for your users as the table is having to be dropped from the cache and reloaded often and the AOS may have to go to back to the database anyway if the record isn’t in the cache.

We can check quite easily to see what tables are set in the AOT to ‘EntireTable’ caching and their associated row count in the database using the scripts below, I’ve provided two scripts one for real-time analysis and one which can be used with DynamicsPerf if you have this setup and configured for your environment:

Real-Time Analysis:

/* AX EntireTable Caching Row Counts (Real Time).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDax09Database>

SELECT  
AOT.TABLENAME
,APPLICATION_LAYER = CASE
                        WHEN AOT.TABID BETWEEN 1 AND 7999 THEN 'SYS'
                        WHEN AOT.TABID BETWEEN 8001 AND 15999 THEN 'GLS'
                        WHEN AOT.TABID BETWEEN 16001 AND 17999 THEN 'DIS'
                        WHEN AOT.TABID BETWEEN 18001 AND 19999 THEN 'LOS'
                        WHEN AOT.TABID BETWEEN 20001 AND 29999 THEN 'BUS'
                        WHEN AOT.TABID BETWEEN 30001 AND 39999 THEN 'VAR'
                        WHEN AOT.TABID BETWEEN 40001 AND 49999 THEN 'CUS'
                        WHEN AOT.TABID BETWEEN 50001 AND 59999 THEN 'USR'
                        WHEN AOT.TABID >= 65000 THEN 'System Table'
                        ELSE Ltrim(Str(AOT.TABID))
                     END
,ST.ROW_COUNT
FROM 
AOTTABLEPROPERTIES AOT
	INNER JOIN 
SYS.DM_DB_PARTITION_STATS ST
	ON OBJECT_NAME(ST.OBJECT_ID) = AOT.TableName
WHERE
ST.INDEX_ID < 2
AND AOT.CACHELOOKUP = 4 -- EntireTable Cache Enabled
AND ST.ROW_COUNT > 0 -- Ignore Tables with no rows
ORDER BY ST.ROW_COUNT DESC

Using DynamicsPerf DB

/* AX EntireTable Caching Row Counts (Using DynamicsPerf DB).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDynamicsPerfDB>

SELECT DISTINCT 
 TD.TABLE_NAME
,TD.APPLICATION_LAYER
,TD.CACHE_LOOKUP
,ISC.ROW_COUNT
FROM 
AX_TABLE_DETAIL_CURR_VW TD 
	INNER JOIN 
INDEX_STATS_CURR_VW ISC 
	ON TD.TABLE_NAME = ISC.TABLE_NAME
WHERE
ISC.ROW_COUNT > 0  AND -- Ignore Tables with no rows
CACHE_LOOKUP = 'EntireTable' 
ORDER BY ISC.ROW_COUNT DESC

Download the scripts here: AX EntireTable Caching Row Counts (506 downloads)

I recommend you review any tables over 2,000+ rows in your ‘EntireTable’ caching strategy regularly ensuring you test any changes prior to making the changes in your Production Environment.

For further reading on AX 2009 Caching please take a look at the following articles:

http://msdn.microsoft.com/en-us/library/bb314693(v=ax.50).aspx

http://dynamicsuser.net/forums/p/30043/157813.aspx

Team Foundation Service – free cloud source control +more for teams of up to 5 users

You can get a free Team Foundation Service account for teams of up to 5 users, all you need to do is sign up for an account and your ready to go straight away

http://tfs.visualstudio.com/en-us/

 

The benefits of using the cloud based Team Foundation Service are:

  • it’s ready to go straight away
  • it’s accessible from anywhere
  • there’s no hardware infrastructure to manage
  • can use existing tools such as Visual Studio
  • source control
  • manage your code online from anywhere
  • continuous unit testing
  • manage work items and bugs
  • plan projects
  • collaborate with your team

If you want on-site Team Foundation capability you could implement Team Foundation Server 2012 Express which is free to use for teams of upto 5 users – see my previous article on this

Another resource for Free E-Books available for Microsoft Technologies on TechNet

You can download content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats such as PDF, E-Pub or Mobi (for use on Kindle). There’s different types of books available such as References, guides, and step-by-step information.

All the e-books are free to download and new books are being posted when they become available take a look and make sure you bookmark it or add the RSS article feed to your favourite RSS Reader:

http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

AX 2009 Report Deployment Session Log On Failed

Issue:

When attempting to deploy a report library using the “Microsoft Dynamics AX 2009 Reports Deployment” Tool you may receive the following error:

“Error: Session log on for Microsoft Dynamics failed”

Report Deployment Session Logon Error

If you check the application event log you will have two source event messages called “Microsoft.Dynamics.Framework.BusinessConnector”

The first error message will show:

Dynamics Adapter Logon failed.

Connection with the Application Object Server could not be established.

Microsoft.Dynamics.BusinessConnectorNet.LogonFailedException
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(BC_PROXY_ACCOUNT_INFO* pBCProxyAccountInfo, String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)

The seconds error message will show:

Session log on for Microsoft Dynamics failed.

Connection with the Application Object Server could not be established.System.InvalidOperationException
at Microsoft.Dynamics.Framework.Design.Reports.DynamicsReportsDesignTimeSession.OnHandleException(String message, Exception exception)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.HandleException(String message, Exception exception, HandleExceptionCallback callback)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.OnLogon(IAxaptaAdapter axaptaAdapter)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.Init(ICacheContext context)

Connection with the Application Object Server could not be established.

Microsoft.Dynamics.BusinessConnectorNet.LogonFailedException
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(BC_PROXY_ACCOUNT_INFO* pBCProxyAccountInfo, String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)

 

Cause:

There could be two possible causes

1. The AOS running the application containing the report libraries is not started.

2. The Microsoft Dynamics AX 2009 Configuration tool is not pointing to the correct AOS, you must apply a configuration that points to the application containing the report libraries you want to deploy to SSRS.

Resolution:

1. Check the AOS is running.

2. Check the “Microsoft Dynamics AX 2009 Configuration” (AX Client Config Tool) is pointing to the correct AOS on the server your attempting to deploy the reports on.

WSS3 Site is read-only or permissions are not working even for site administrators

What you may be experiencing:

  • the sites permissions don’t appear to be working / broken
  • site administrators / contributors can’t make changes
  • unable to restore a copy of the site
  • no errors are being reporting in any of the servers log files

Cause:

A failed backup usually causes this issue to occur, before a backup of your site is taken it  is temporarily placed into a read-only state / locked state, if the backup fails the site is not unlocked for normal day-to-day use

Resolution:

There are two ways in which to remove the read-only sitelock

Using Central Adminstration

Goto ‘Central Administration’ on your WSS3.0 server
Select ‘Application Management’
Under ‘SharePoint Site Management > Site collection quotas and locks’
Using the drop down “Site Collection” move through each site and check the “Lock status for this site”
Any site showing locked change it “Not locked” and click ok

Using stsadm

stsadm -o setsitelock -url http://server_name -lock none

 

Connecting to SSAS 2005 from Excel 2010 Error: “XML for Analysis parser: The DbpropMsmdSubqueries property was not recognized”

Issue

You may receive this error message “XML for Analysis parser: The DbpropMsmdSubqueries property was not recognized” when connecting from Excel 2010 to SSAS 2005

Cause

In most cases the machine will have had Excel 2007 previously installed and Excel 2010 has been installed side-by-side with 2007 or has been upgraded to Excel 2010

Resolution

Install the Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2 available in the Microsoft® SQL Server® 2008 R2 Feature Pack

Here’s an overview of what the Analysis Services OLE DB Provider is:

The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.

Note: Microsoft SQL Server 2008 R2 Analysis Services OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.

The provider can be downloaded from the following links:

X86 Package (SQLServer2008_ASOLEDB10.msi)
X64 Package (SQLServer2008_ASOLEDB10.msi)

Free ebook: Introducing Windows 8: An Overview for IT Professionals

Get a free a ebook on an Introduction for IT Professionals to Windows 8 from here:

http://blogs.msdn.com/b/microsoft_press/archive/2012/11/13/free-ebook-introducing-windows-8-an-overview-for-it-professionals-final-edition.aspx

It’s available in PDF, Mobi (Kindle) and ePub formats.

Topics include:

• Performance, reliability, and security features

• Deployment options

• Windows Assessment and Deployment Kit

• Windows PowerShell™ 3.0 and Group Policy

• Managing and sideloading apps

• Internet Explorer® 10

• Virtualization, Client Hyper-V, and Microsoft Desktop Optimization Pack

• Recovery features

SSAS Login timeout expired when processing Cube

If you receive this error when attempting to process a cube:

OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00;
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001;
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ; 08001.

 

Try checking the following:

  • Remote Connections are enabled on the instance your Data Source Database resides on
  • If using the server/host name in your data source connection string ensure that the named pipes protocol is enabled within SQL Server Configuration Manager > SQL Server Network Configuration on the instance your Data Source Database resides on