Category Archives: SQL

[AX2012] Moving Project to a diferent Model


 

Moving a project to a different model causes Ax to move all the objects inside it to that model as well.

You could instead create a new project in the Model you want it and then drag all the objects into that. You will lose all the metadata of the project including timestamps.

Another way is to move just the object related to the Project to the right model.

For that use the following steps

    1. Copy the Name of the Project
    2. Find the ModelId you want to move it into. You can use Get-AxModel command for this. (this should be within the same Layer)
    3. Open SQL and find the Element handle of the Project. Use the following command to do that
      -- Find the Element Handle
      -- ElementType 37 = SharedProject
      select top 10 * from ModelElement
      where Name = 'PUT_YOUR_PROJECT_NAME_HERE'
      and ElementType = 37
    4. Once you get just one record from the above code, then run the following command to make sure that the element is the correct one (It should have the modelId which is the current model it is in)
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
    5. If you get just one record then we should be good to move the element to the right model
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
      -- Move the Model
      -- Change 20 to the ModelId you want the element to be in
      Update ModelElementData set ModelId = 20 where ElementHandle = @ELEMENT_HANDLE
      

 

Advertisements

[AX2012] Get list of security roles with name


To get export the security roles along with the AOT name of the security will need you to either write something in X++ or go into SQL

I went the SQL route and this is my code:

select
S.AOTNAME, L.Text as AOTNAME, LD.Text as DESCRIPTION
from SECURITYROLE S
Left outer join ModelElementLabel L
on L.Module = SUBSTRING(S.Name, 2, 3)
and L.LabelId = SUBSTRING(S.Name, 5, 7)
and L.Language = 'en_us'
Left outer join ModelElementLabel LD
on LD.Module = SUBSTRING(S.DESCRIPTION, 2, 3)
and LD.LabelId = SUBSTRING(S.DESCRIPTION, 5, 10)
and LD.Language = 'en_us'

This will give a list of the AOT names and the Label name and Description

[AX2012] Compare objects between models


SQL Makes everything better

When ever adding a new hot-fix or a new model from another source we need to compare the code touch points between that and existing code.

Ax makes that easy using the compare layers tool, however that only compares layers

So the other way is to go code upgrade and use those tools which may or may not work depending on your case (for example you put a 3rd part model and want to compare what var or cus or usr layer code you need to change)

This brings to writing x++ code or so in yummy SQL

Requirements:

  1. Know what models have changed

  2. Know what models will be affected / need to be compared

Know what models have changed

You know what hotfixes have been put in and there may be more than one. so note the numbers of the hotfixes or models that were added. Use the Get-AxModel command to get the models Ids for them

Know what models will be affected / need to be compared

You will probably need to know all the Models Id’s from the higher layers. So if you have a hotfix that you added then take a note of all model id’s that are a part of isv, isp, var, vap, cus, cup, usr, usp …. you know what i mean

The Fun begins with SQL

select et.ElementTypeName, et.TreeNodeName, med1.ParentHandle, med1.ElementHandle, me.* from ModelElementData med1
join ModelElement me
on ((me.ElementHandle = med1.ParentHandle and med1.ParentHandle <> 0)
or (med1.ParentHandle = 0 and me.ElementHandle = med1.ElementHandle))
join ElementTypes et
on et.ElementType = me.ElementType

where med1.ModelId in (<model ids changed in csv>)
and med1.ElementHandle in (select medisv.ElementHandle from ModelElementData medisv
where medisv.ModelId in (<model ids affected in csv>))
order by me.ElementType

That gives us a result set with main element type and the path to it.

Hopefully this gives an easier way to figure out what has changed and reduces your time. If this post does help you let me know, I am planning to invest in a query to further give more details about each element.

AX2012 What models have been updated ?


While working with Ax 2012 and deploying models, comes a challenge of sending models out.
Probably a challenge only when there tends to be multiple models in the same layer and not all need to be sent out.

Version control knows what I changed

One of the easier ways is to look at Version control and check what has been updated.
That sounds easy? Well what if objects were never checked into version control?
What if you don’t have TFS as a version control (set up on a per model basis)

Model store to the rescue

We know what code has changed -> The Model store holds the data of all the objects in the AOT.
We know when it was changed -> It also holds the Modified date time (And it also holds ModifiedBy, so you can go pursuing the person you are after)
We know what model / layer it is in -> The model jungle holds that data

Ok, How do I get this data now? -> SQL statements.
I have 2 statements which gives the elements that have changed since a certain date, and the other one which groups it all up to say what models have been updated as a high level statement.

SQL Variables (this is where you set the parameters)

DECLARE @DATEMODIFIED nvarchar(100)
DECLARE @ELEMENTTYPESUNWANTED as TABLE (Id int not null)

---Set values here
--Date since when objects have been modified
SET @DATEMODIFIED = '2013-01-24'
--Unwanted Element types
INSERT INTO @ELEMENTTYPESUNWANTED (Id)
values (23), (24) --23: ClassHeader, 24: TableHeader

What elements have been updated?

--List Of elements that have changed--
select L.Name, SYSMM.DISPLAYNAME as 'Model', MED.ModelId,MED.MODIFIEDDATETIME, MED.CREATEDDATETIME, MED.MODIFIEDBY, ME.Name, ET.ElementTypeName, ME.ElementType
from ModelElement ME
join ModelElementData MED on MED.ElementHandle = ME.ElementHandle
join ElementTypes ET on ET.ElementType = ME.ElementType
join SYSMODELMANIFEST SYSMM on SYSMM.MODEL = MED.ModelId
join Layer L on L.Id = MED.LayerId
where MED.MODIFIEDDATETIME >= CONVERT(dateTime, @DATEMODIFIED)
and ME.ElementType not in (select Id from @ELEMENTTYPESUNWANTED)
order by MED.MODIFIEDDATETIME desc

What Models have been updated?

--List of Models That has been updated--
select L.Name, SYSMM.DISPLAYNAME, MED.ModelId
from ModelElement ME
join ModelElementData MED on MED.ElementHandle = ME.ElementHandle
join ElementTypes ET on ET.ElementType = ME.ElementType
join SYSMODELMANIFEST SYSMM on SYSMM.MODEL = MED.ModelId
join Layer L on L.Id = MED.LayerId
where MED.MODIFIEDDATETIME >= CONVERT(dateTime, @DATEMODIFIED)
and ME.ElementType not in (select Id from @ELEMENTTYPESUNWANTED)
group by SYSMM.DISPLAYNAME, MED.ModelId, L.Name

Alter gear SQL index management


Update 06 April 2010: A new version has been released which saves the connection strings

After 3 hours, and a few other gruelling hours of trying to the get UI working Alter gear Sql index manager is now Alive.

Here is how to run it:

Step 1: Start the application:

1

Step 2 (optional) : select the Filter (Average fragmentation %)

2

Step 3 : Select database to tune

4

Step 4: Choose indexes to Reorganise / Defrag

5

Click “Defragment selected”

image

Defrag calls Reorganise, and Rebuild the Rebuild method on the index.

Happy re-indexing

Database index defragmenter


I recently came across a best practise which said – do not defragment all indexes in an SQL database. A few reasons apart fro the obvious of it being a costly transaction. Are there any tools that will let me analyse the indexes and i can choose which ones to defragment? I have started creating an application to do this which will soon be hosted on Codeplex, and yet again under the “Alter gear” project name.

MySql Connection Basics


Hi,
I was working with MySql, untill i figured out to keep some of the basic necessary code aside and include it in my classes…and reduce my code.
I have created a dll of the class…. of that…which is available at my homepage
Will get some documentation with it soon
Will also mail the code if needed.
The DLL file is uploaded here :
http://shashi.sadasivan.googlepages.com/MySqlDB.dll

Please feel free to send in any comments or suggestions

Can’t connect to remote sql server/express?


Got a few links that can help.
Make sure that you check the log file in the server’s directory, to find the nature of the error.

this blog gives a brief description of the protocol errors:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

if you are not able to connect to the server, follow the steps given in
http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

And if your client is Vista, turn off the firewall.. that will solve the issue, Im trying to find how to enable the firewall and still connect to the DB.

Will update accordingly,
Goood luck

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records


{“Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.”}

Terrible exception.
Well what was happening is i had a datatable, and i was editing it.
At one of the points i update it, then I call in a different class’s method, pass only the unique key of one of the rows and make certain claculations, change the row, and update it.
Now when I go back to my original table, and update it….it threw this error.
Lets take this example.
The table “myTable” has the following fields: ID,name,address,country,credit
where ID is the primary key.
lets take a row ID=1,name=Shashi,address=Brisbane,country=Australia,credit = 20
i have 2 classes MyClass and MyCalcClass
MyClass is instantiated and it acquires a row from myTable lets call it myRow
now i instantiate an instance of MyCalcClass called objCalc and send it myRow.ID.
now objCalc changes the value of credit (lets say multiplies by 10) to 200 from 20 and saves it.
now back in MyClass if i save myRow.
The adapter throws back with the error…. why?
well….for ID=1 the credit value shud have originally been 20 but it dosent find it..and hence it throws a concurrency error
to overcome this…..either send the entire row so that the row is update while objCalc does its changes ,OR: repopulate myRow for that ID so that you have the latest updated version of the row.

Eureka, it worked for me