List content of a model to HTML using PowerShell

Building on this post on listing elements in a model and this post on putting a list in a HTML file I wanted to quickly just show an easy and quick way to create list elements in a model to HTML. The PowerShell command is easy, and the results are swift and usable.

For this example I'm going to list the contents of a hotfix.

(axmodel -model 'Hotfix-KB2909140-Foundation' -details).elements | select name, elementtype, path | `
sort path | ConvertTo-Html -CssUri http://skaue.com/ps.css | Out-File c:\KB2909140.html

The HTML-file can be viewed using your favorite browser.

Nifty, ey? :-)

List models as a HTML Table using PowerShell

I was working on a PowerShell script to list my servers and their specs the other day and one of the cool things I learned was how easy it is to push the result out as a HTML table. Why would you want to do that? Well, it can be (just) another way of presenting the models in a modelstore. Also it is a matter of copy and paste to grab the table over to Excel if that is your swag.

So let's get on with the command:

axmodel | select modelid, name, layer, version, elementcount | `
sort modelid | ConvertTo-Html -CssUri http://skaue.com/ps.css | Out-File c:\models.html

I'm not specifying the modelstore here, just grabbing whatever my local config is pointing too. I'm also making use of the default verb, so I can write "axmodel" instead of "get-axmodel". Nifty, ey?

I'm a former web developer, so I uploaded a small stylesheet to prettify my table. Feel free to grab it and reuse it if you want to, or simply reuse the URL.

body{background-color:#efefef; font-family:tahoma;font-size:90%;}
table{border: 1px solid #999;}
th{border-bottom:1px solid #999; text-align:left;}
td{padding:2px;}
tr:nth-child(odd) { background-color:#eee;}
tr:nth-child(even) { background-color:#fff;}

And here is the result:

PowerShell + AX + Web development - fun, fun, fun!

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.

Where is my Modelstore - AX2012 and AX2012 R2

This post is just a friendly reminder to myself and everyone who get tricked by an environment that has been upgraded from the initial release of Dynamics AX2012, namely RTM, with or without Feature Pack, to either Release 2 or any of the consecutive releases.  What trick am I referring to? I am thinking about the fact that unless you've removed the old Modelstore, you now have two different Modelstores for each environment.

You will have the upgraded Modelstore in the separate Modelstore Database, but you will also have the old Modelstore inside the Business Database. It does not do anything, except occupy space. Well, there is one more annoying thing it does to me. I find myself going back and forth between RTM to R2 environments and sometimes I forget to use the correct Database name when running PowerShell commands. Imagine installing a new model to a R2 environment, only to later discover that you installed it into the idle RTM Modelstore inside the Business Database and not to the Modelstore Database. It doesn't happen ALL the time, but I've done it more than once.

So in the interest of clarity, here is a drawing explaining where the Modelstores are located. Left hand side is the previous version where everything was in one database. On the right hand side you have the new setup with two databases. And for upgraded environments, you may or may not have that remnant Modelstore sitting inside the Business Database.

The experienced AXers may argue that if you use the Config parameter when operating the Modelstore through PowerShell you will always work against the Modelstore Database, but since I often run these commands from other servers than the AOS Server I mostly use the Server and Database parameter for all operations.  :-)