ExchangeRateEffectiveView not returning cross rates to the Cubes in AX2012

I was asked to assist figuring out why exchange rates wouldn’t always be retrieved to the Sales Order Cube in Dynamics AX2012. The solution became a journey through various interesting topics (for me at least):

  • Views in AX
  • View methods
  • Advanced Query range
  • Reverse engineering the SQL behind the views
Starting with the view that did not return all the expected data, we have the SalesOrderCube View. 
Now the Query in itself isn’t all that fascinating, but I wasn’t aware that you could add ranges across datasources like done in this Query. That is pretty handy!
Notice how the Query uses another View as DataSource. There are plenty of examples of Views and Queries being nested, and this is a powerful way to create results from a rather complex ERP data model. 
Notice also there is a custom Range on ValidFrom and ValidTo. The Ranges compare the Dates from ExchangeRateEffectiveView with the CreatedDateTime from SalesTable.
If we look at the definition of the ExchangeRateEffectiveView we see that ValidTo is a field of type Date. Furthermore we see the field is coming from a View Method. But we know CreatedDateTime on SalesTable is a DateTime.
How can it compare a Date with a DateTime? The answer is that actual date is stored in the database as a datetime where the time part is 00:00:00.000.   
So it compares the values and that works like charm. 
The problem
What happens if you have daily exchange rates in your system? Then your ValidFrom and ValidTo becomes the exact same date and more importantly the same time. This will not work correctly since CreatedDateTime also keeps track of what time on the day a Sales Order was created. 
So let’s look at one specific example where we have rates for the 9th of December 2014.
And if we run the Sales Order Cube view, and modify the selected columns so we can see the problem, we will notice that the query is unable to collect the rates. The values from Exchange Rates is NULL.

The Solution

There are probably many ways to solve this, but the solution I went for was to make sure that the ValidTo always returns the time part at the max value, which is 23:59.59.000 (AX doesn’t operate on the milliseconds, as far as I know).

So compare the results coming from the ExchangeRateEffectiveView before I apply the change.
By doing some small changes to the validTo View method, I can give the time part a better value.
And the result is that the Sales Order Cube now has the Cross Rates as expected. 
I hope you enjoyed this post. I sure enjoyed solving this challenge.
Here is the method body (ExchangeRateEffectiveView.validTo):

private static server str validTo(int _branchNum)
{
str returnString, fieldString, fieldString2, fieldString3;
boolean generateCode = false;
DictView dictView;

// Axdata.Skaue.04.03.2015
// Fix ValidTo with proper time 00:00:00.000 -> 23.59.59.000 ->
str adFixValidToString = 'DateAdd(ss,-1,DateAdd(d,1,%1))';
// Axdata.Skaue.04.03.2015 <-

dictView = new DictView(tableNum(ExchangeRateEffectiveView));

switch (_branchNum)
{
case 1:
returnString = dictView.computedColumnString('VarToVarBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 2:
fieldString = dictView.computedColumnString('DenToVarEuroBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToVarEuroBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;

case 3:
fieldString = dictView.computedColumnString('DenToDenBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
fieldString3 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
fieldString3 = strFmt(adFixValidToString, fieldString3);
// Axdata.Skaue.04.03.2015 <-
returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 +
' and ' + fieldString + ' <= ' + fieldString3 + ' then ' + fieldString +
' when ' + fieldString2 + ' <= ' + fieldString3 + ' then ' + fieldString2 + ' - 1 ' +
' else ' + fieldString3 + ' - 1 end';
break;

case 4:
returnString = dictView.computedColumnString('SameFromTo', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 5:
returnString = '\'21541231\'';
break;

case 6:
returnString = '\'21541231\'';
break;

case 7:
returnString = dictView.computedColumnString('DenToVarAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 8:
returnString = dictView.computedColumnString('DenToVarAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 9:
returnString = dictView.computedColumnString('VarToDenAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 10:
returnString = dictView.computedColumnString('VarToDenAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;

case 11:
returnString = '\'21541231\'';
break;

case 12:
fieldString = dictView.computedColumnString('DenToDenAfterStart1', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart1', 'StartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;

case 13:
fieldString = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'StartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;

case 14:
fieldString = dictView.computedColumnString('DenToDenAfterStart2', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart2', 'StartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;

case 15:
fieldString = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'StartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;

}

if (generateCode)
{
returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 + ' then ' + fieldString +
' else ' + fieldString2 + ' - 1 end';
}

return returnString;
}

Advertisements

Delete Company in AX 2009 using SQL

One of the potential tasks when upgrading to a new version of Dynamics AX (like from AX2009 to AX2012) is getting rid of obsolete companies. Microsoft shared a SQL for this a few years back. I enhanced it a little bit and added some additional statements.

Just one important remark – DO NOT RUN THIS AGAINST AX2012!

In the interest of sharing, here it is:


/********************************************************
REMOVE COMPANYID IN DYNAMICS AX 2009

USE AT OWN RISK!

MAKE SURE YOUR TRANSACTION LOG IS PERMITTED TO GROW

Inspired by:
http://blogs.msdn.com/b/emeadaxsupport/archive/2010/12/09/how-to-delete-orphaned-data-remained-from-deleted-company.aspx

Tommy Skaue http://yetanotherdynamicsaxblog.blogspot.com/
*********************************************************/

DECLARE @_TABLENAME NVARCHAR(40)
DECLARE @_COMPANYID NVARCHAR(4)

SET @_COMPANYID = N'TST'; -- COMPANY TO DELETE

DECLARE CURSQLDICTIONARY CURSOR FOR
SELECT A.SQLNAME
FROM SQLDICTIONARY A
INNER JOIN SQLDICTIONARY X ON X.TABLEID = A.TABLEID AND X.FIELDID = 61448
WHERE A.FIELDID = 0
AND A.FLAGS = 0

OPEN CURSQLDICTIONARY

FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @_SQL NVARCHAR(4000)
SET @_SQL = N'DELETE FROM ' + QUOTENAME(@_TABLENAME) + N' WHERE DATAAREAID = @_DATAAREAID'
PRINT (CHAR(13) + 'Removing ' + @_COMPANYID + ' from ' + @_TABLENAME + '...')
EXEC SP_EXECUTESQL @_SQL, N'@_DATAAREAID NVARCHAR(4)', @_DATAAREAID = @_COMPANYID

FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME
END

PRINT (CHAR(13) + 'Finalizing...')
DELETE FROM DATAAREA WHERE DATAAREA.ID = @_COMPANYID
DELETE FROM COMPANYDOMAINLIST WHERE COMPANYDOMAINLIST.COMPANYID = @_COMPANYID
DELETE FROM VIRTUALDATAAREALIST WHERE VIRTUALDATAAREALIST.ID = @_COMPANYID
PRINT (CHAR(13) + 'Done!')
CLOSE CURSQLDICTIONARY
DEALLOCATE CURSQLDICTIONARY

Use at own risk (of course), and let me know if you find any issues with it.

Compare Modelstores using tableDiff Utility

Did you know SQL Server 2005 was shipped with a tool that makes it possible to analyze differences between two tales? This tool is called tablediff.

The reason I mention this is because I had to quickly check two Modelstores where there were some differences I didn’t expect to find. My Modelstore-import failed and the reason seemed to be some conflicting elements.

I was thinking about just writing a SQL query to look it up. It would have involved setting up a Linked Server since these Modelstores where on two different SQL Server instances. While I was considering my options, and searching the web for possible options, I came across this old tool. I remember seeing it before, but never had the use of it. It seem to fit the bill perfectly, as all I was interested in was comparing the table ModelElement for conflicting elements.

Basically, it is a command line utility that ships with SQL Server, and you can run it with parameters for source, destination and some additional options. For details visit: http://technet.microsoft.com/en-us/library/ms162843.aspx. One of the benefits is its performance, and the ModelElement can contain a million rows or more. In my example, the comparison took less than 30 seconds.

The command I used was this:

tablediff.exe -sourceserver sql2012\test -sourcedatabase ax2012_test_model -sourceschema dbo -sourcetable modelelement -destinationserver sql2012 -destinationdatabase ax2012_prod_model -destinationschema dbo -destinationtable modelelement -et diff1 -f c:\compare.sql -c

The output file contained SQL for updating the destination, but I was only interested in the Query itself. Investigating the output file, searching for the string “update ” (trailing space after the command), I found the dozen elements that was causing the conflict. I could then further list the ElementHandles in a new SQL where I would list them out to see what elements they were. Example:

SELECT ET.ELEMENTTYPENAME, ME.* FROM MODELELEMENT ME
JOIN ELEMENTTYPES ET ON ME.ELEMENTTYPE = ET.ELEMENTTYPE
WHERE ME.ELEMENTHANDLE IN (860734,860739,860746)

The utility is installed if you opt for installing the Replication Features, and will be installed normally under C:\Program Files\Microsoft SQL Server\100\COM\

I might wrap a PowerShell script around it later, but not today.

About your elements Origin, ID and Handle

This post will be about the elements Origin, their ID and finally the Handle. These are important to grasp as AX developer when creating elements in the Application Object Tree (AOT) and copying them around between environments. I hope it will help both new and old AX developers.

The Origin is made up of a Global Unique ID (GUID), and it will follow the element created throughout its lifetime, from the very first brilliant idea, through all sorts of versions and bug fixes and until is is one day obsoleted; from cradle to grave. This Origin will be the same for this element in all installations, environments, versions and variations. It is unique and special, and it so for a purpose.

Take this table as an example:

The Origin of this table will follow this table from my development environment and into any other installation. If I copy this table using XPO, Model or ModelStore, this is the Origin it will carry everywhere. This also means that if I create “MyTable” in my AX, and you create your “MyTable” in your AX, you can be certain they will have different Origin, and essentially not be the same table.

Take a look at how the Origin is part of the element description when I export it to a XPO.

The same is true if I make an axmodel-file having this table.

So why is this so important? Well, most seasoned Dynamics AX developers have at some point gotten their hands burned on element ID conflict, element cache become invalid, change of element ID causing loss of data and so on. AX 2012 attempts to solve this by letting us forget about element ID altogether and instead focus on Origin. The goal is for us developers only to worry about Origin as identification of Elements.

Great, but what is “Element ID”? Element ID is a unique number identifying elements in the AOT. This ID is used a lot in AX for historical and good reasons. It is a way for the system to quickly identify any element for things like indexing, cache, relations, usage, etc. It works like charm, and is a lot more efficient than using the actual name, path or some other non-numeric reference to the element. Surely a GUID is not a very efficient way to index and organize a long list of elements – just think about it.

Element ID comes into play as soon as en element is created in the AOT, but an important difference compared to the Origin is the fact that the Element ID may perfectly well be different between environments. Element ID is strongly tied to the business data, so if the Element ID is changed or made invalid, you risk loss of data. There are however ways around this, but they are not discussed in this post.

Let us have a look at how Origin works when Elements get their ID in AX during import of a XPO or installation of a Model (axmodel). Details can be found in this Technet Article.

The system will automatically try to reuse Element ID based on Origin, or it will create a new Element ID if necessary. There is also this LegacyID, which is a way to make some IDs backward compatible with previous versions of AX, if really necessary.

As an example, take a look at good old CustTable, number 77. 🙂

Also take notice of the Origin and compare it to the Origin in your AX environment. Is it the same? It sure is!

Ok, so what is then Element Handle? It is unique ID identifying every single element in the AOT that is meant to be uniquely identified. It is also the ID being used as key and foreign key in the tables containing the information about the AOT elements (ModelElement, ModelElementData, etc). The system assigns Element Handles just as it does RecIds.

Does that mean Origin and Element ID doesn’t cover all possible elements in identifying them? The answer is NO! Take table methods as an example. It does not have an Origin, nor does it have en Element ID. It does have en Element Handle.

Try run the SQL underneath in your AX environment and see what handles you have for CustTable and a custom table.


SELECT ELEMENTHANDLE, ROOTHANDLE, NAME, AXID, ORIGIN FROM MODELELEMENT
JOIN ELEMENTTYPES ON MODELELEMENT.ELEMENTTYPE = ELEMENTTYPES.ELEMENTTYPE
WHERE NAME IN ('CUSTTABLE', 'MYTABLE') AND ELEMENTTYPENAME IN ('TABLE')

You may want to replace “MyTable” with a custom table of your own. Notice how my ElementHandle of CustTable differs from yours.

So let’s wrap this up with some highlights:

  • Origin is your cradle to grave ID for your new Elements. It is the only ID you really need to worry about.
  • Element ID works pretty much like before, except you no longer keep it between environments. System creates this ID uniquely for you.
  • Element Handles uniquely identifies every single identifiable element in the AOT and is provided by the system.
  • Not all elements have Origin or Element ID, but all elements have a Handle.
  • Finally – Business data is tied to Element ID and not Origin. Don’t ever get tempted to mix one modelstores business data with another modelstores business data.
If you ever wonder what elements have ElementHandle but no Origin or ElementID (AxID), you could try run these queries on your modelstore database:
-- Get all element types having Origin but no Element ID
SELECT DISTINCT ELEMENTTYPES.ELEMENTTYPENAME
FROM MODELELEMENT JOIN ELEMENTTYPES ON MODELELEMENT.ELEMENTTYPE = ELEMENTTYPES.ELEMENTTYPE
WHERE AXID IN (0) AND ORIGIN NOT IN ('00000000-0000-0000-0000-000000000000')

-- Get all element types having no Origin
SELECT DISTINCT ELEMENTTYPES.ELEMENTTYPENAME
FROM MODELELEMENT JOIN ELEMENTTYPES ON MODELELEMENT.ELEMENTTYPE = ELEMENTTYPES.ELEMENTTYPE
WHERE ORIGIN IN ('00000000-0000-0000-0000-000000000000')
Thank you for reading!

Keep your AX2012 Modelstore performing

I had an interesting conversation with a very skilled and helpful Microsoft employee regarding maintenance of the Modelstore database. He sent me a SQL script prepared by the Microsoft Support Team.
First let’s have a look at the proposed script, then let’s look at why we would want to run it.

ALTER INDEX ALL ON [dbo].ModelElement REBUILD
ALTER INDEX ALL ON [dbo].ModelElementData REBUILD
ALTER INDEX ALL ON [dbo].Sources REBUILD
ALTER INDEX ALL ON [dbo].ModelElementLabel REBUILD
ALTER INDEX ALL ON [dbo].MODELSECPOLRUNTIMEEX REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityCommon REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPermission REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicy REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicyConstraint REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityPolicyTable REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityRole REBUILD
ALTER INDEX ALL ON [dbo].ModelSecurityHierarchyCache REBUILD
ALTER INDEX ALL ON [dbo].ModelSecuritySubRole REBUILD

UPDATE STATISTICS [dbo].ModelElement WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelElementData WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].Sources WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelElementLabel WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].MODELSECPOLRUNTIMEEX WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityCommon WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPermission WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicy WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicyConstraint WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityPolicyTable WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityRole WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecurityHierarchyCache WITH FULLSCAN, COLUMNS
UPDATE STATISTICS [dbo].ModelSecuritySubRole WITH FULLSCAN, COLUMNS

Before I created this post I did a quick test on one of my Modelstores and I gained a little bit on the compilation time, from 1h 53m to 1h 47m. That is just an improvement of 5 minute. This particular Modelstore only contained a vanilla R2 with DIEF, so it’s not exactly a grand example.

We know the Modelstore of R2 is between 3,5 and 4 GB. The Modelstore database will easily be double the size. It will contain large indexes to support the tables. The image above shows some of the biggest tables with their indexes. We also know there is a hotfix released that makes a small adjustment to the schema reducing the compilation time by 20-40%.

Why bother to rebuild the indexes of the Modelstore and update the statistics? Well, obviously every time you fill it with a new awesome model, it creates new records in the Model-tables for each single element in that model. You probably uninstall less awesome models once in a while, leaving gaps in your indexes. Perfectly normal in a development environment. So running the script above once a week wouldn’t hurt anyone – most likely just be an advantage.

The rebuild of the indexes will completely rebuild the index instead of just reorganizing the leafs. Since we want column statistics to be fully updated, we then run a new set of statements to make full scans and update any column statistics. Running this will make sure the model database is fit 4 fight. 🙂

Remove old modelstore from an upgraded AX2012 R2 database

One of the benefits of AX2012 R2 compared to AX2012 RTM is the fact that they decided to split the database in two parts, one part for the business data and one part for the application data. This makes it a lot easier to copy business data from production to test without overwriting the application data in test. Sure, there were ways around this in RTM, but you can’t deny it is a lot easier to do this now. Another benefit is that you no longer have the application (modelstore) residing in the business database. This means you don’t need to have a full backup with point-in-time recovery for the production application. The application in RTM would be aroud 2.5 GB exported, but inside the database you would have huge indexes and also the compiled assemblies stored. All of these data are now stored in the application database in R2. I would argue that there is no need to have the recovery mode set to “Full” on these databases containing the application. I mean, each time you import a modelstore, have a look at the database log. It will grow huge.

Inspired by a response from Kevin Kidder at the Community Site, I decided to try make a Stored Procedure that removes the modelstore elements inside the database. Getting rid of that old modelstore from RTM that now sits in the business database after successfully upgrading to R2 will reduce the size of the database with a couple of GB, so why not.

I found this nice script to remove all schema elements, and used is as a base for my script. I also initialized a new modelstore for the purpose of testing my script. Actually, the SQL scripts being used for creating a modelstore is available as resources in the AxUtilLib assembly. You can study them by using any tool that can revese engineer a .Net dll.

This procedure might not be flawless, so use it with care. Obviosuly; take a backup and test. By default the procedure will print out the SQL statements you need to run in order to remove all the modelstore elements in the database. You will have to explicitly pass “w” for it to execute the statements for you. You will have to decide if you want to evalute the output and run it manually, or let the procedure do it all.

Please report feedback and/or errors back to me. 🙂

Here it is:

CREATE PROCEDURE [DBO].[CleanUpModelStore]
(
@WORKTEST CHAR(1) = 't' -- use 'w' to work and 't' to print
)
AS
/*-----------------------------------------------------------------------------------------

Author : Tommy Skaue
Date: 06.04.2013

Description: Drops all modelstore elements.
Usage and License: Free and at own risk
Parameter: t = test/print out
w = work and execute statements

Report any errors or feedback to tommy@skaue.com.

Inspired by the script made by Ranjith Kumar S
http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/

-------------------------------------------------------------------------------------------*/
BEGIN

DECLARE @SQL VARCHAR(5000)
DECLARE @MODELSTOREELEMENTQUERY VARCHAR(2500)
DECLARE @MSG VARCHAR(500)
DECLARE @SCHEMANAME VARCHAR (50) = 'DBO'

IF OBJECT_ID('TEMPDB..#DROPMODELSTORE') IS NOT NULL DROP TABLE #DROPMODELSTORE
CREATE TABLE #DROPMODELSTORE
(
ID INT IDENTITY(1,1)
,SQLSTATEMENT VARCHAR(5000)
)

-- Common filter for ModelElements
SELECT @ModelStoreElementQuery =
'AND (T.NAME LIKE ''Model%'' OR
T.NAME IN (
''axIdAllocsTable'',
''AxIdAsk'',
''existingPaths'',
''Sources'',
''newPaths'',
''SourceMerge'',
''SYSXPPASSEMBLY'',
''ElementTypes'',
''SchemaVersion'',
''Layer'',
''LayerVersioning'',
''GlobalFieldIdPool''))'

-- removes all the foreign keys that reference a PK in the target schema
SELECT @SQL =
'SELECT
''ALTER TABLE ''+SCHEMA_NAME(FK.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.NAME
FROM SYS.FOREIGN_KEYS FK
JOIN SYS.TABLES T ON T.OBJECT_ID = FK.REFERENCED_OBJECT_ID
WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
AND FK.SCHEMA_ID T.SCHEMA_ID
' + @MODELSTOREELEMENTQUERY + '
ORDER BY FK.NAME DESC'

--IF @WORKTEST = 't' PRINT (@SQL )
INSERT INTO #DROPMODELSTORE
EXEC (@SQL)

-- drop all default constraints, check constraints and Foreign Keys
SELECT @SQL =
'SELECT
''ALTER TABLE ''+SCHEMA_NAME(T.SCHEMA_ID)+''.''+OBJECT_NAME(FK.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ FK.[NAME]
FROM SYS.OBJECTS FK
JOIN SYS.TABLES T ON T.OBJECT_ID = FK.PARENT_OBJECT_ID
WHERE T.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
AND FK.TYPE IN (''D'', ''C'', ''F'')' + @MODELSTOREELEMENTQUERY

--IF @WORKTEST = 't' PRINT (@SQL )
INSERT INTO #DROPMODELSTORE
EXEC (@SQL)

-- Common filter for ModelElements
SELECT @MODELSTOREELEMENTQUERY =
'SELECT OBJECT_ID FROM SYS.OBJECTS SO2 WHERE
SO2.PARENT_OBJECT_ID IN
(
SELECT SO3.OBJECT_ID FROM SYS.OBJECTS SO3 WHERE SO3.TYPE IN (''U'') AND
(
SO3.NAME LIKE ''Model%'' OR
SO3.NAME IN (
''axIdAllocsTable'',
''AxIdAsk'',
''existingPaths'',
''Sources'',
''newPaths'',
''SourceMerge'',
''SYSXPPASSEMBLY'',
''ElementTypes'',
''SchemaVersion'',
''Layer'',
''LayerVersioning'',
''GlobalFieldIdPool'')
)
)
OR
(
SO2.TYPE IN (''U'') AND
(
SO2.NAME LIKE ''Model%'' OR
SO2.NAME IN (
''axIdAllocsTable'',
''AxIdAsk'',
''existingPaths'',
''Sources'',
''newPaths'',
''SourceMerge'',
''SYSXPPASSEMBLY'',
''ElementTypes'',
''SchemaVersion'',
''Layer'',
''LayerVersioning'',
''GlobalFieldIdPool'')
)
)
OR
(
SO2.TYPE IN (''V'') AND
(
SO2.NAME LIKE ''SysModel%'' OR
SO2.NAME LIKE ''Util%'' OR
SO2.NAME LIKE ''Model%'' OR
SO2.NAME IN (''ConfigurationKeys'', ''LicenseCodes'', ''Origins'',''SECURABLEOBJECT'') OR
SO2.NAME IN (
''SECURITYENTRYPOINTLINK'',
''SECURITYPERMISSION'',
''SECURITYROLE'',
''SECURITYROLEEXPLODEDGRAPH'',
''SECURITYROLEPERMISSIONOVERRIDE'',
''SECURITYROLETASKGRANT'',
''SECURITYSUBROLE'',
''SECURITYSUBTASK'',
''SECURITYTASK'',
''SECURITYTASKENTRYPOINT'',
''SECURITYTASKEXPLODEDGRAPH'',
''SECURITYTASKPERMISSIONOVERRIDE''
)
)
)
OR
(
SO2.TYPE IN (''TR'') AND
SO2.NAME IN (
''ModelSecurityPermission_CreateSecurable'',
''UpdateChangedBy'',
''SetInstalledAndChangedBy'',
''UpdateModelFromManifestChangedBy'',
''IO_Trig_INS_ModelElement'',
''IO_Trig_INS_ModelElements'',
''IO_Trig_Del_ModelElements'',
''ModelSecurityCommon_Insert''
)
)
OR
(
SO2.TYPE IN (''P'') AND
(
SO2.NAME LIKE ''XI_%'' OR
SO2.NAME LIKE ''XU_%''
)
)
OR
(
SO2.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'') AND
SO2.NAME IN (
''CreateTemplateName'',
''GetLayerMask'',
''GetNextAvailableFieldOrIndexAxId'',
''GetNextAvailableAxId'',
''GetAxIdHole'',
''IsAxIdExcluded'',
''SECURITYROLE_FUNC'',
''SECURITYTASK_FUNC''
)
)'
-- drop all other objects in order
SELECT @SQL =
'SELECT
CASE
WHEN SO.TYPE=''PK'' THEN ''ALTER TABLE ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+OBJECT_NAME(SO.PARENT_OBJECT_ID)+'' DROP CONSTRAINT ''+ SO.NAME
WHEN SO.TYPE=''U'' THEN ''DROP TABLE ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
WHEN SO.TYPE=''V'' THEN ''DROP VIEW ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
WHEN SO.TYPE=''P'' THEN ''DROP PROCEDURE ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
WHEN SO.TYPE=''TR'' THEN ''DROP TRIGGER ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
WHEN SO.TYPE IN (''FN'', ''TF'',''IF'',''FS'',''FT'')
THEN ''DROP FUNCTION ''+SCHEMA_NAME(SO.SCHEMA_ID)+''.''+ SO.[NAME]
END
FROM SYS.OBJECTS SO
WHERE SO.SCHEMA_ID = SCHEMA_ID(''' + @SCHEMANAME + ''')
AND SO.TYPE IN (''PK'', ''FN'', ''TF'', ''TR'', ''V'', ''U'', ''P'')
AND SO.OBJECT_ID IN (' + @MODELSTOREELEMENTQUERY + ')
ORDER BY
CASE
WHEN TYPE = ''PK'' THEN 1
WHEN TYPE IN (''FN'', ''TF'', ''P'',''IF'',''FS'',''FT'')
THEN 2
WHEN TYPE = ''TR'' THEN 3
WHEN TYPE = ''V'' THEN 4
WHEN TYPE = ''U'' THEN 5
ELSE 6
END'

--IF @WORKTEST = 't' PRINT (@SQL )
INSERT INTO #DROPMODELSTORE
EXEC (@SQL)

DECLARE @ID INT, @STATEMENT VARCHAR(2000)
DECLARE STATEMENT_CURSOR CURSOR
FOR SELECT SQLSTATEMENT
FROM #DROPMODELSTORE
ORDER BY ID ASC

OPEN STATEMENT_CURSOR
FETCH STATEMENT_CURSOR INTO @STATEMENT

WHILE (@@FETCH_STATUS = 0) BEGIN
IF @WorkTest = 't'
PRINT (@statement)
ELSE
BEGIN
PRINT (@statement)
EXEC(@statement)
END

FETCH STATEMENT_CURSOR INTO @STATEMENT
END

CLOSE STATEMENT_CURSOR
DEALLOCATE STATEMENT_CURSOR

PRINT '------- ALL - DONE -------'
END
GO

Hope this helps someone.

Simple way to recover lost columns

Easter vacation is almost over and I felt like posting a new blog entry.

This will be something even a novice DBA would know about, but hopefully it might be helpful for a Dynamics AX developer who isn’t too familiar with SQL – but wants to learn.

I once had to help someone get back a couple of columns that were lost due to a flawed client cache (apparently). This may also easily happen if someone accepts a database synchronization which creates data loss of specific columns.

The solution is quite easy. A DBA or developer with some SQL Server experience can prepare a restored database sitting on the same SQL Server Instance, or create a Linked Server for the purpose of this restore.

When the database is ready, make sure to keep users away while you restore the data. It should be a matter of minutes. I restored 3 columns in around 800 000 rows in just a few minutes. Keeping users away is just a precaution. SQL Server will by itself make sure the entire update either fails or succeeds.

Here is an example SQL query to restore the columns:


BEGIN TRANSACTION RestoreColumnData WITH MARK N'Restoring Column Data';
GO

UPDATE SalesTableInTarget
SET SalesTableInTarget.CustomColumn1 = SalesTableInSource.CustomColumn1 ,
SalesTableInTarget.CustomColumn2 = SalesTableInSource.CustomColumn2 ,
SalesTableInTarget.CustomColumn3 = SalesTableInSource.CustomColumn3
FROM DynamicsAXTarget.dbo.SALESTABLE AS SalesTableInTarget
INNER JOIN DynamicsAXSource.dbo.SALESTABLE AS SalesTableInSource
ON SalesTableInSource.RECID = SalesTableInTarget.RECID
GO

COMMIT TRANSACTION RestoreColumnData
GO
The query simply joins the target table and the source table based on the RecId,which should be unique. I chose to mark the update in a custom transaction. 
You can find multiple examples of similar queries on the web, but sometimes it helps to put these things in our AX context.
Hope this helps someone.