Copying a Production Database into a Development/Test Environment

Disclaimer: In no way am I making claim that this is the best, correct, or accepted approach to take when bringing down a copy of Production into Development. The outlined method has worked for me and served my purposes well.. with that said, follow my steps at your own risk!


1. Stop the AOS on Development or Test
2. Backup the AX Database in Production, and Restore it into Development or Test
3. Restore all the Security permissions/mappings to the AX Database (Model them after Production)
4. Write a T-SQL script to replace Production Server & User names in the Database with that which are related to Development or Test (see below for tables/columns that need to be changed)
5. On Development or Test, delete all files in the application folder (C:\Program Files\Microsoft Dynamics AX\50\Application\Appl\[Your AX Instance]) where the file extension ends with the letter “i”
6. Start the AOS on Development or Test (this may take awhile)
7. Open the AOT, right-click the Data Dictionary, and select Synchronize


Tables in the AX Database that need to be Modified (see step #4 above)

I had written a utility that mined the database for all references to Production system names. The following is the script that I came up with to “convert” a Production Database to Development/Test. Note: not all changes may actually be necessary (such as in the log tables) but for consistency, I change them anyway.

Declare @AOS varchar(15)
Declare @SP varchar(15)
Declare @SQL varchar(15)
Declare @axbcp varchar(15)

Set @AOS = '[Your DEV AOS Server Name]'
Set @SP = '[Your DEV SharePoint/Enterprise Portal Server Name]'
Set @SQL = '[Your DEV SQL Server Name]'
Set @axbcp = '[Your DEV axbcp User Account Name]'

Declare @P_AOS varchar(15)
Declare @P_SP varchar(15)
Declare @P_SQL varchar(15)
Declare @P_axbcp varchar(15)

Set @P_AOS = '[Your PRODUCTION AOS Server Name]'
Set @P_SP = '[Your PRODUCTION SharePoint/Enterprise Portal Server Name]'
Set @P_SQL = '[Your PRODUCTION SQL Server Name]'
Set @P_axbcp = '[Your PRODUCTION axbcp User Account Name]'


----------------------------------------------------------------------------------
Update [SYSBCPROXYUSERACCOUNT] Set NETWORKALIAS = @axbcp WHERE NETWORKALIAS = @P_axbcp
----------------------------------------------------------------------------------


Update [BATCH] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHHISTORY] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
-----------------------------------------------------------------------------------


Update [BATCHSERVERGROUP] Set SERVERID = REPLACE(upper(SERVERID), upper(@P_AOS), upper(@AOS))
Where upper(SERVERID) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [EPWEBSITEPARAMETERS] Set [INTERNALURL] = REPLACE(upper([INTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([INTERNALURL]) like '%' + upper(@P_SP) + '%'
Update [EPWEBSITEPARAMETERS] Set [EXTERNALURL] = REPLACE(upper([EXTERNALURL]), upper(@P_SP), upper(@SP))
Where upper([EXTERNALURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SRSSERVERS Set [SERVERURL] = REPLACE(upper([SERVERURL]), upper(@P_SQL), upper(@SQL))
Where upper([SERVERURL]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [AXAPTAREPORTFOLDER] = REPLACE(upper([AXAPTAREPORTFOLDER]), upper(@P_SQL), upper(@SQL))
Where upper([AXAPTAREPORTFOLDER]) like '%' + upper(@P_SQL) + '%'
Update SRSSERVERS Set [REPORTMANAGERURL] = REPLACE(upper([REPORTMANAGERURL]), upper(@P_SQL), upper(@SQL))
Where upper([REPORTMANAGERURL]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_AOS), upper(@AOS))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_AOS) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SP), upper(@SP))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SP) + '%'
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SQL), upper(@SQL))
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update [SYSEMAILSMTPPASSWORD] Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_AOS), upper(@AOS))
Where upper([DESIGNNAME]) like '%' + upper(@P_AOS) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SQL), upper(@SQL))
Where upper([DESIGNNAME]) like '%' + upper(@P_SQL) + '%'
Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SP), upper(@SP))
Where upper([DESIGNNAME]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERCONFIG Set [SERVERID] = REPLACE(upper([SERVERID]), upper(@P_AOS), upper(@AOS))
Where upper([SERVERID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSSERVERSESSIONS Set [AOSID] = REPLACE(upper([AOSID]), upper(@P_AOS), upper(@AOS))
Where upper([AOSID]) like '%' + upper(@P_AOS) + '%'
------------------------------------------------------------------------------------


Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_AOS), upper(@AOS))
Where upper([COMPUTERNAME]) like '%' + upper(@P_AOS) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SP), upper(@SP))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SP) + '%'
Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SQL), upper(@SQL))
Where upper([COMPUTERNAME]) like '%' + upper(@P_SQL) + '%'
------------------------------------------------------------------------------------


Update SYSWORKFLOWPARAMETERS Set [SITEURL] = REPLACE(upper([SITEURL]), upper(@P_SP), upper(@SP))
Where upper([SITEURL]) like '%' + upper(@P_SP) + '%'
------------------------------------------------------------------------------------



Re-Syncing Enterprise Portal

Unfortunately, by restoring a Production Database into Development/Test, it appears to break connectivity with Enterprise Portal. As of now, the only way I have figured out how to resolve this issue is by doing the following:

Verify Your SharePoint Administrative Access

1. START > Administrative Tools > SharePoint Central Administration
2. Go to Application Management > SharePoint Site Management > Site Collection Administrations
3. Click the Site Collection drop down > Change Site Collection
4. Click the "/sites/DynamicsAx" URL and click OK
5. Make sure your name is listed as either the Primary or Secondary site collection administrator. NOTE: if you are not the Primary or Secondary site collection administrator, you will get an "Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)" error when attempting to install Enterprise Portal

Reinstall Enterprise Portal

1. Load the Setup.exe (run as Administrator) from your Dynamics AX 2009 installation path
2. Select “Add or modify components”
3. Select “Role Centers and Enterprise Portal” and click Next
4. Verify that the “Domain\user name” is correct for your environment (if not make the change to the NETWORKALIAS column in the SYSBCPROXYUSERACCOUNT table in the AX database, and then restart the setup) and enter in the password for the account
5. Select the existing Dynamics AX EP website
6. Check the “Configure for Windows SharePoint Services” checkbox
7. Check the “Create Web site” checkbox
8. Verify the Site URL
9. Click Next
10. Click “Yes” to allow the setup to delete and recreate
11. Click Install

Run a command prompt (run as Administrator) and enter the following command lines:

1. cd c:\Program Files\Microsoft Dynamics AX\50\Setup
2. AxUpdatePortal –updateWebSites –verbose > “C:\EPUpdate.log”
3. iisreset

Enterprise Portal should once again be functioning. At this point you will need to go into your EP site, and add your user permissions again.

CLR Errors & ttsbegin/ttscommit Blocks

  
Recently I had modified the smmCampaignBroadcast class to call a custom class/method that I had used and modified, to send emails using CLR via the System.Net.Mail assembly (which was being used elsewhere... see my blog on Sending Emails From Dynamics AX Without Outlook). It worked great, until a certain a condition caused the Send() function to fail. The problem came in to play with despite the fact that I had try/catch blocks around core pieces code in the function (meant to alert the user of errors) in this particular scenario, the process was simply taking a dump, and the error causing the problem was not being caught. Upon stepping through the code with the debugger, immediately after hitting the problem line of code (which was the Send() function as mentioned above) the debugger stepped into a \Classes\Application\ttsNotifyAbort() method, which basically aborted the entire process, not allowing my try/catch block around the Send() call to acknowledge the error.

Upon some research, it appears that the problem is related to levels of ttsbegin/ttscommit blocks, and apparently if a CLR error occurs inside of a ttsbegin/ttscommit block, you cannot trap the error until you are outside the block (and in the case of nested ttsbegin/ttscommit blocks, you must be outside of the highest level).

Using the Stack Trace in the debugger, I analyzed the code, to identify where the first ttsbegin/ttscommit block was, and then wrapped that piece of code with a try/catch block meant to catch a CLR error. So, in the smmCampaignBroadcast::broadcast() method I made the following changes:

try
{


ttsbegin;


…….


ttscommit;


}
catch(Exception::CLRError)
{
info(CLRInterop::getLastException().ToString());
}

Finally, I was able to see what was going wrong!

Making Modifications to Report Libraries in AX and Re-Deploying the Latest Versions to SSRS

   
Creating and initially deploying a Report Library into SSRS is quite easy. However when it came time to make a modification to that Report Library, I quickly found out that my latest version was not actually deploying, because changes were not actually being made to my Report Library in the AOD, despite the fact that Visual Studio was reporting success with a message such as: “Saving project [Project Name] and all its items to AOD succeeded”. The problem appears to be that when clicking “Save to AOD” in Visual Studio, for a project that already exists in AX, the project isn’t actually overwritten. To resolve this, follow these simple steps when making a mod to a Report Library.

1. Right-Click the Report Library that you want to edit, and select “Edit in Visual Studio”
2. If you are prompted with an overwrite prompt, click “Yes to All”
3. Make your modifications
4. When ready to save back to AX, select: File > Save All
5. Go back into AX and Delete the Report Library that you are editing
6. Go back into Visual Studio, right-click the Project, and select “Save to AOD”
7. Go back into AX, right-click the Report Libraries node in the AOT, and select “Refresh” and you will see your Report has returned, now being your most recent version.

Other things to keep in mind…

If your Report Library is the defined Object of a Menu Item, and there is a chance that you may have modified the Name of the Report while editing it, make sure that you check the Object value of the Menu Item to verify that it is valid (otherwise, the Menu Item will point to the previous version of the report in SSRS).

If you Import the edited Report Library into another environment, make sure that you Delete the existing Report Library first. I have found that the Import does not always overwrite the existing Report Library, even during an import.
   

Sending Emails From Dynamics AX without Outlook

  
Recently we had a need to have Dynamics AX send emails without the use of Outlook. I was presented with the following blog by Mohammed Rasheed titled Sending Emails From Dynamics AX without Outlook, which proved to be extremely useful. There was however an issue with the code (perhaps simply related to our environment) which was puzzling. The problem was that the email would send successfully, but afterward, an error was thrown stating:

ClrObject static method invocation error

The line of code that appeared to be the culprit was basically the last line in the function:

CodeAccessPermission::revertAssert();

However, after analyzing things more closely the problematic line was actually the line just above:

winApi::deleteFile(fileNameforEmail); // delete temp file

As it turns out, the deleteFile() function was failing because the file had not been released by a previous process. Though it was a bit painful to figure out what was going on, the solution itself was rather simple.

Immediately after sending the email, I modified the code to dispose the objects that may have been clinging to the file

mymail.Send(mailmessage);


mailmessage.Dispose();
attachment.Dispose();

After this, the file was released, and was able to be deleted, and the error went away!
  

Retrieving Record Values in an Enterprise Portal Wizard/Tunnel

    
Let’s say you have a scenario in which you need to pull a field from a newly created record that was inserted into AX via an Enterprise Portal Wizard/Tunnel. This need could be useful in the event that after a record is created, you need to obtain the record’s Id, so that you can do something else which relates to the new record. In this example we will extend the code example found in the book “Microsoft Dynamics AX 2009 Programming: Getting Started” (page 304)

protected void WizardTunnel_FinishButtonClick(object sender,
WizardNavigationEventArgs e)
{
AxDataSourceView rentalTableView =
this.RentalDataSet.GetDataSourceView("RentalTable");


if (rentalTableView != null)
rentalTableView.EndEdit();


AxUrlMenuItem carRentalsMenuItem =
new AxUrlMenuItem("CarRentalList");
Response.Redirect(carRentalsMenuItem.Url.ToString());
}

To pull the value from the CarRentalId in the following modified event handler…

protected void WizardTunnel_FinishButtonClick(object sender,
WizardNavigationEventArgs e)
{
AxDataSourceView rentalTableView =
this.RentalDataSet.GetDataSourceView("RentalTable");


if (rentalTableView != null)
{


DataSetViewRow dvr = rentalTableView.DataSetView.GetCurrent();
string CarRentalId = dvr.GetFieldValue("CarRentalId").ToString();


rentalTableView.EndEdit();
}


AxUrlMenuItem carRentalsMenuItem =
new AxUrlMenuItem("CarRentalList");
Response.Redirect(carRentalsMenuItem.Url.ToString());
}

    

Resolving one cause of the "Invalid datasource name" in Enterprise Portal

  
If you have developed a Web Control for Enterprise Portal, which contains an AxDataSource, and an AxForm, and then you attempt to include an AxLookup control you may receive the following error:

Invalid datasource name ‘[Your Data Source Name Here]’

However, you have verified that the data source name is most definitely correct and is indeed part of your Data Set. The problem may be that you have put your AxLookup inside the bounds of the AxForm control. Place your AxLookup control outside of the AxForm, and your problem will probably go away (unless of course it is being caused by another issue)
  

Importing AX Projects (.XPO) & Deploying Web Controls into Enterprise Portal: Resolution for Controls not showing in the “Managed content item” drop down.

       
Recently I seriously struggled for some time in exporting Web Controls in the AOT in my development environment, and importing them into the staging environment. I created a project to include all of my web controls, by adding the related web control nodes from [AOT > Web > Web Files > Web Controls]. I then imported this project into staging, and went into Enterprise Portal to setup my Web Part Page with my custom web controls in the AOT. The problem however was that when modifying my shared web part (a Dynamics User Control Web Part), the “Managed content item” drop down did not contain my custom web controls. Oddly enough, I could create a new project in Visual Studio, and select the “Add user control from AOT” option, and my custom controls were in the list. So what exactly was going on??

A veteran Axapta developer, whom I have the privilege of occasionally tapping for information if all else fails, revealed to me that Enterprise Portal does not pull its list of Web Controls from the [AOT > Web > Web Files > Web Controls] node, like Visual Studio does, but rather, in order to see your controls in the “Managed content item” drop down, you must include the references to the controls that are found in the [AOT > Web > Web Content > Managed] node. So when I added these references to my project in the development environment, exported the project, and then imported the project into staging, and then restarting both the AOS and IIS for good measure… My custom web controls finally showed up and could be used.