SQL Server 2012: Rename a database with datafiles

SQL Server 2012: Rename a database with data files (MDF, LDF)

This post provides the scripts that will rename a database along with its data files.

Audience

DB Admins, T-SQL developers, Technical Consultants, AX Developers

Background

In so many scenarios, you may need to rename a database. One general scenario in AX implementation would be when maintaining multiple AX environments at one server like Staging, TEST, DEV and Production. This is also very likely in development and RnD scenarios where you keep restoring LIVE instances backups on a central backstage HUB for data availability.

Problem

For hosting multiple and different AX servers on a single machine for local availability, you need to have multiple AX server databases hosted with different names. But for that, you need to rename the database since each backup may result in the exact same database name. But you face the problem as you already have a database with exactly same name, 'MicrosoftDynamicsAX' attached to your local server. This can cause problems and you may start thinking how to restore both databases with different names cleanly without disturbing the existing database.

Solution(s)

Properly renaming existing database(s) would let you restore any database coming under the name of MicrosoftDynamicsAX. How I have resolved this issue at my local server is, I rename the existing MicrosoftDynamicsAX database (along with data file names, yes, renaming data files :) ) and the restoring the new database under the same MicrosoftDynamicsAX (or whatever) name. To restore more such instances, I can always rename databases as I like. This I achieve using the attached script. It lets me achieve the following in one go

Script process

• Set desired database to single user mode
• Rename database to new provided name
• Takes the database (with new name) to offline mode
• Suspends further execution for 5 minutes 
• This is done so that the user is expected to rename the files manually at physical file level.
• Renames the data files logically
• Brings the database back to online
• Sets the database again to multi user mode.

The script is at the moment hard coded since I was not able to provide variables to a few alter database commands due to command's nature. For this reason, the script is not plug and playable as I had to use symbols to specify the replacing database names. when using, carefully replace sample values with yours and test and master execution on test databases :)

This mess would be resolved by dynamically generating SQL and providing user provided database names and other variables to SQL. Therefore as improvement, I am writing a small utility that would enable user to select a database, provide its new name and click and all is done automatically. So stay tuned.

Script can be downloaded from the link: Database renaming script

AX 2012 R3 SSRS: publishreportcommand network name cannot be found

AX 2012 R3 SSRS: publishreportcommand network name cannot be found

SSRS report deployment gives error, "The network name cannot be found."

Audience

Dynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users

Background

When deploying a SSRS report, you may come across the error. This error will occur while deploying report(s) through AOT as well as using PowerShell scripts.

Experience tells us that such issues may occur if service accounts (AOS, BC proxy, Workflow) are wrongly configured. In our scenario, we had to reconfigure and put the newly provided service accounts. As a result, the reporting services crashed. We made sure all the practices related to configuring system service accounts are met. However we kept getting this error. report deployment were attempted both using AOT and PowerShell

Solution(s)

Thanks to sharing, the coolest caring. we cam to know the following link that clearly mentioned the reason of this issue.

https://blogs.msdn.microsoft.com/axsupport/2013/03/13/deploying-reports-in-ax2012-you-need-access-to-the-administrative-share-c/ 

In the post, the user uses the verbose modifier in the publish command in PowerShell to get more details. That yields that a network shared location access is attempted by the script. Now the question is, if such access is available or not. As a resolution, providing the C drive (reporting services server) as network shared resource resolved the issue.

Links

• Deploying reports in AX2012 – you need access to the administrative share C$ 
• AX 2012 Deploy report error : The network name cannot be found
• AX 2012: Report Deployment error "The network name cannot be found."

AX 2012 Workflow: Function run has been incorrectly called.

AX 2012 Workflow: Function run has been incorrectly called.

Workflow submit ends in error notification when submitting to workflow from code

AX 2012 Workflow: Function run has been incorrectly called.

Audience

Dynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users

Background

At the point of submitting a workflow, the comment dilog box appears. The class, is constructed needing a table buffer. The tupple must contain a row for the active workflow. This is usually passed by the statement

args.caller().getActiveWorkflowConfiguration()

The following  listing provides a detailed context

    workflowSubmitDialog = WorkflowSubmitDialog::construct    (args.caller().getActiveWorkflowConfiguration());

    workflowSubmitDialog.run();

Problem

Our working form stopped to resolve a particular configuration for the workflow. This was pretty strange behavior as a lot of records completed different scenarios of the workflow in success. we refreshed AOT items, generated incremental and FULL CIL, restarted the service, cleared the cache, but nothing worked.

Solution(s)

This must be a configuration or framework issue as a working workflow stopped being not able to find a particular version row (which exists in the table). Several investigatory steps were carried as mentioned in the problems section but none resolved the issue.

Alternatives

The solution should be a framework refresh. The only quick alternative for us was to write an alternative i.e fetch the existing version row our self and provide it to the WorkflowSubmitDialog[C] object. We did as follows;

• Write a custom method in [T]WorkFlowVersion that returns the buffer. The following query does the job

select firstOnly1 *
   from
workflowVersionTblBfr
    join workflowTblBfr
    where
workflowVersionTblBfr.WorkflowTable     == workflowTblBfr.RecId &&
workflowVersionTblBfr.Enabled           == NoYes::Yes           &&
workflowTblBfr.TemplateName             == _wfTemplateName    
        ;

    return workflowVersionTblBfr;

• Call the new method in the submit method of the WorkFlow SubmitManager class once it is verified that the workflow version is not being fetched by the framework
  
  Method navigation: Workflow submit manager class > Submit method > add call after workFlowSubmitDialog object is constructed
  

//
    if (!workflowSubmitDialog || !workflowSubmitDialog.parmWorkflowConfiguration() )
workflowSubmitDialog = WorkflowSubmitDialog::construct(WorkflowVersionTable::T4ED_GetWFConfig
(workFlowTypeStr(T4EDPurchPORecWFType), tableName2id(tableStr(T4EDPOProdRecConfExtWF)), recId
));
//

Note: Since you are submitting a workflow, you don't need to look for the record specific workflow version. This would be mandatory if we are taking any action on an already submitted record. In such scenario, we will require to search for configurations that exists in WorkflowWorkItem table for specific table and record.

AX 2012: Invalid application object name error while creating child table fields

AX 2012: Invalid application object name error while creating child table fields

Error message: 

Invalid name. The application object already exists. 

Audience

Dynamics AX 2012 (FP, R2, R3) X++ Developers, Functional and Technical consultants and advanced users

Background

Using inheritance in tables, you may receive the subject mentioned error

"Invalid name. The application object already exists."

Problem

This error comes due to the fact that two tables inheriting from the same base table cannot have fields with same name. To avoid such error, you need to put distinct name among each table extending the same base table.

Reason

The reason is not any of the object oriented myths :) The simple reason is that, no matter how many levels of abstraction the AOT layer add to schema items like tables, a table among its extending tables exists only once at the database layer (SQL) . This is like that since child tables actually only exists at AOT layer. Physically there is one single base table. The data resides in one table physically which is abstracted at AOT as child tables.

That means, if you have a base table with 100 child tables, there would be only one physical table for all of them at the database layer. For this reason, you cannot have same name field(s) for different child tables extending same base table.

Conclusion

Keep each child table's field names distinct among all sibling tables.

AX 2012: You are not authorized to access table ‘’ (). Contact your system administrator.

AX 2012: You are not authorized to access table ‘’ (). Contact your system administrator.

Audience

Dynamics AX 2012 (FP, R2 R3) X++ Developers, Functional and Technical consultants and advanced users

Background

You may experience the subject mentioned error while opening a table / view

Problem

I experienced this error while creating a new view. When I accessed it to see if it is working as expected, I received this error.

I carefully examined and found that actually no fields are added into the view.

Solution(s)

At this point of time, every one knows the solution, add some field dude. Adding fields to the view solves the error. However this error is quite distracting as it can take you to an American Wild Goose chase i.e. towards security and authorization implementations. so keep googling before diving straight into the deep AX sea :P

AX 2012 - Understanding AOT Maps with an example

Understanding AOT Maps with an example

Subject: AOT Maps

Audience: Dynamics AX Developers

Background

Maps are not new concept in software development actually. They are table wrappers to achieve general behavior. However for lame dude(s) like me, these definition words have not been enough.

Problem

The term allotted to Maps, "Map" itself is a bit confusing. But an example can really help understanding. And Maps are used everywhere, but in comparison to tables and classes ( see these building blocks are essential for any kind of most basic development), use of maps are quite rare.

Example

I have been browsing PurchTableHistory table. A method came into my consideration where I see the use of Map.  

The method, PurchTableHistory[T] >> initFromPurchTable() declares several maps based on localized buffers. Look at the following code

// Code example

PurchTableMap purchTableMap;

purchTableMap.data(_purchTable.data());

this.data(purchTableMap.data());

First, the row from PurchTable is copied to the map variable, 'purchTableMap', and then, the history buffer gets the same row but form map, and not from the original source, purchTable buffer

WHY ? Their must be a genuine need ? Why don't we copy from the original PurchTable buffer  ? Becuase we cannot :) becuase of schema change. See if most of the fields are same but not with exact field names, types and filed list count, we cannot copy a row from one table to another using xRecord[C].data() simply because of schema mismatch.

Here is where map can work, since the different name same type and purpose fields are MAPPED in the MAP :). Fields with different names from different tables are mapped to consistent names in map, so that you can use them to map one row ( from one table buffer) to another table.

Updated: Rephrasing the above in other words, map can be used to manipulate common schema among different (and having entirely different schema ) tables. It is like not knowing a buffer at runtime, expecting from it to be implementing a consistent schema with labelling issu

One more thing, when using maps,
field count doesn't matter since the map will only care and execute on the schema defined in the map. It simply would not care for schema defined in the mapped tables, it would only care for the schema defined in the MAP

Happy MAPPING

Links

• https://msdn.microsoft.com/en-us/library/ee330226.aspx?f=255&MSPPError=-2147217396 
• https://msdn.microsoft.com/EN-US/library/aa624363.aspx 
• https://msdn.microsoft.com/EN-US/library/bb278211.aspx 

AX 2012 - Define items of a combo box at design time

Combo box in AX 2012

Background

There are numerous ways to load values into a combo box in AX forms. The most common is to use base enums. One way is to use sysTableLookup to load master table data. Another way is to load another form at lookup event and interchange values using args. However sometimes its convenient to define custom items at design time. And such a thing is not very obvious in AX IDE. I recently discovered while answering an AX community post that HcmWorkerLookup form has an other way to do the same.

We all as ASP.Net developers know that we use to define the members of a ComboBox control. There is a similar way to do the same in AX as well. This is useful if your combo box has small no of items in it which are static, unchangeable values and are not suppose to update

Problem

When you have few static options to provide to user and for some reason, you want to show the user control as a combo box, you better define those few options at design time.

Solution(s)

I love AX due to the rapid development engine and reusable frameworks. AX provides the solution to the above problem definition by providing enough properties to define all the members / items under a combo box control. The ComboBoxControl has three properties which will be used.

You will have to focus on 3 properties on the combo box control. 

1. Items - It is the number of items (count) to appear on the combo at run time.
2. Item  -  It is the currently selected item at design time, the following property works on the currently selected item
3. Text -  It is the name or caption to appear for the currently selected item (number entered in Item property)

So how will you design it  ? E.g. you want three items to appear in your combo with values defined at design time and without involving any BaseEnum or table values or EDT relation. For three items, you will set the Items property of combo to 3. Then, to define the text values for each item, you will have to repeatedly update the Item property ( current item ) to 1,2, 3 and provide text value for each in the Text property.

Try it, its not cool may be, but knowing this IS COOL for sure ;)

Links

• https://community.dynamics.com/ax/f/33/t/179483