Saturday, 6 September 2014

Copy AX and model database permission (stored proc & view)

Sometimes during development or testing, we might need to create/copy environment. The easiest method is to copy the AX and model database from a source environment, then restore it to the destination environment. If each of the environment (AOS) has different service account, then you might hit permission error during start up of the AOS (if permission haven't been granted yet).

The AOS service account needs certain permission on both of the database, creating it manually will be a lot of work, Instead of doing that, scripting it out would be much easier.

Steps:
#1. Execute the following script from the source environment

set nocount on

Print 'Column Level Privileges to the User:'
select 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' ('+column_name+') to ['+grantee+']'+
case IS_GRANTABLE when 'YES' then ' With GRANT OPTION'
else '' end from INFORMATION_SCHEMA.COLUMN_PRIVILEGES
Print 'Table Level Privileges to the User:'select 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' to ['+grantee+']' +
case IS_GRANTABLE when 'YES' then ' With GRANT OPTION'else ''
end from INFORMATION_SCHEMA.TABLE_PRIVILEGES
Print 'Privileges for Procedures/Functions to the User:'
select 'grant execute on '+c.name+'.'+a.name+' to ['+user_name(b.grantee_principal_id)++']'+
case state when 'W'
 then ' with grant option'else ''
  end from sys.all_objects a, sys.database_permissions b,
  sys.schemas c
  where a.object_id = b.major_id and a.type in ('P','FN') and b.grantee_principal_id <> 0 and b.grantee_principal_id <>2

  and a.schema_id=c.schema_id



Ref: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/758e2485-1def-4549-a4d4-4568b028009d/copy-user-permission

#2. Replace the service account name
Eg. Replace all "AOSService_STD" with "AOSService_DEV"

#3. Execute the result of the script to the corresponding destination database

Thursday, 4 September 2014

AX 2012 R3 email report not working

One of the common requirement for report is emailing (Eg. nightly batch run and email the report).

Standard AX does provides this feature, but unfortunately AX 2012 R3 RTM seems to have a bug over here.

Given the Print destination settings below, it looks OK on the first look, but it wasn't. The problem with it is the Cc field is empty.

It uses the SrsReportRunMailer class, emailReport method to send the email.
The mailer.quickSend() method call has all the parameters passed in (including the Cc).

Here's where the problem comes from, the SysMailerNet class, quickSend method. is checking the Cc value using prmisDefault() method.

Sequence:
1. Print destination settings doesn't provides value in Cc field
2. Empty string is passed in as parameter value in quickSend method
3. Due to empty string is passed in, the Cc parameter is no longer default value
4. Validating using the prmisDefault method is incorrect due to Cc is no longer default value (it should just check whether the Cc has value or not - Eg. if(cc){...})

The error occur when it reach the line - ccs.add(cc).
If cc is empty, it error and stop there.


There're two quick solutions here:
1. Enter a value in Cc field
2. Modify the code, use if(cc){...} instead of if(!prmisDefault(cc)){...}


Wednesday, 20 August 2014

Retail sync job failed due to table field definition changed

A quick intro of the environments/installation:
1. Developed version of AX 2012 R3
2. Standard retail components (Eg. Channel database, Async server, Async client, Real-time service etc)

When running the sync job from Distribution Schedule, we came across this error - "System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.".

A quick guess would be the retail channel database is standard but pointing to a developed version of AX, some table field(s) might have been changed (Eg. Field length increased).

To find out which field length has changed/different between the two environment, the script below is used:

select S.tableName, S.name, S.max_length, D.max_length
  from                                                
      (select ST.name as tableName, SC.*              
         from ChannelDatabaseName.sys.tables ST       
        inner join ChannelDatabaseName.sys.columns SC 
           on ST.object_id = SC.object_id             
      ) AS S                                          
  join                                                
      (select DT.name as tableName, DC.*              
         from AxDatabaseName.sys.tables DT            
        inner join AxDatabaseName.sys.columns DC      
           on DT.object_id = DC.object_id             
      ) as D                                          
    on S.tableName   = D.tableName                    
   and S.name        = D.name                         
 where S.max_length != D.max_length                   

*NOTE: You might want to narrow down to just the tables in the failing sync scheduler jobs/subjobs by adding the table name into the query above.

This will give a list of the table and field names where the field length is different.

Once the list of table and field name is generated, then the field length is altered on the channel database.

Eg. ALTER TABLE [ax].[LOGISTICSPOSTALADDRESS] ALTER COLUMN COUNTY nvarchar(60)

Rerun the job and the error goes away.


Full stack trace

ApplySessionFileToClientDatabase encountered ProcessSourceRequestHeaderExceptionMicrosoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=ServerName;Initial Catalog=RetailDatabaseName;Integrated Security=True;Connect Timeout=60;Application Name="Commerce Data Exchange Async Client" ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.BulkCopyData(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
   --- End of inner exception stack trace ---
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
   at Microsoft.Dynamics.Retail.SynchClient.Core.DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)

Friday, 11 April 2014

New field added to Dynamics AX retail table not sync during pull job

Just a quick note, if you made changes to retail table which is used on pull job, the related temporary table needs to be updated as well. The temporary table should be updated using the "Create staging table" button instead of updating it manually from AOT.

Wednesday, 26 March 2014

Retail SDK project unavailable

If you're getting errors similar to the below when open up the Retail SDK solution file, that's probably due to the Visual Studio 2012 Office Developer Tools is not installed yet.
(Ref: http://blogs.msdn.com/b/somasegar/archive/2013/03/04/now-available-office-developer-tools-for-visual-studio-2012.aspx)

Download the web installer and get it installed, then reopen the solution file, it should be fixed now.

Sample error message:
Unsupported
This version of Visual Studio does not have the following project types installed or does not support them. You can still open these projects in the version of Visual Studio in which they were originally created.
 - SharePoint.Web.WebParts, "C:\Retail SDK\Online Channel\StoreFront\WebParts\SharePoint.Web.WebParts.csproj"
 - SharePoint.Web.Storefront, "C:\Retail SDK\Online Channel\StoreFront\Storefront\SharePoint.Web.Storefront.csproj"
 - SharePoint.Web.Scripts, "C:\Retail SDK\Online Channel\StoreFront\Scripts\SharePoint.Web.Scripts.csproj"
 - SharePoint.Web.Services, "C:\Retail SDK\Online Channel\StoreFront\Services\SharePoint.Web.Services.csproj"
 - CommonGlobalModules, "C:\Retail SDK\Online Channel\StoreFront\CommonGlobalModules\CommonGlobalModules.csproj"

Friday, 14 March 2014

Debug AX 2012 Retail Online Storefront & common global modules

The Retail SDK contains the whole solution code for the Online store, you’ll need to sign it with your own strong name key before starting any development.

To debug the Online store code, the DLL used by the Online store has to match the solution.

But the standard code/DLL used at the initial deployment has a different version & public key token than your solution even though the code within it is identical (before any changes is made), this will prevent the debugging.

In order to debug it, you’ll need to deploy your solution to the Online store so that it will use the updated DLL (with your own version & public key token that match the solution file).

This post will walk through the steps required to:
1. Sign the Retail SDK with your strong name key
2. Update the storefront solution file
3. Generate the package (*.wsp) for deployment
4. Deploy the solution package
5. Debugging


Pre-requisites:
• Install Retail Online Channel
• Install Retail SDK

NOTE:
For shorter path in the example below, I’ll copy the “Retail Online Channel” folder to “C:\Retail\” and rename it to “OnlineChannel”.
So, we’ll be working on:
• C:\Retail\OnlineChannel
   instead of 
• C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail Online Channel

So does the Retail SDK, we’ll copy it from:
• C:\Users\admin\Documents\Retail SDK
   to
• C:\Retail\SDK

All the steps/changes below will be using the files in the new path:
• C:\Retail\OnlineChannel
• C:\Retail\SDK

Preparing for debugging:
#1. Update the Retail SDK with your own strong name key
> Update the file “C:\Retail\SDK\UpdateAssemblyIdentities.ps1”, Line 98 with your public key token
 

> Execute this PowerShell script (UpdateAssemblyIdentities.ps1) with administrator right
(This will takes several minutes to run)

*Due to we changed the “Retail Online Channel” path from “C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail Online Channel” to “C:\Retail\OnlineChannel”, we’ll need to do some manual work later to change the value in a file. This is due to the “UpdateAssemblyIdentities.ps1” used a fixed path to update the “RetailPublishingJob.App.config” file, so it only update the file in the original directory (C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail Online Channel) but not the new directory (C:\Retail\OnlineChannel). In addition to that, the Commerce Runtime is not updated to 1.0.0.0 as well. The steps in this article only allows you to debug the storefront solution, if you need to debug the CRT, you may follows the steps in “Debug AX 2012 Retail Online Store commerce runtime services”.

#2. Open up the StoreFront solution from “C:\Retail\Retail SDK\Online Channel\StoreFront\StoreFront.sln”

#3. Find solution’s code that’s not updated with the correct version & public key token, update them manually
Eg. Find with “6.3.0.0” is easier

*Workflow assembly should left intact due to you can’t customize it
(Ref: http://technet.microsoft.com/en-us/library/jj916618.aspx)

Example of files that need change.
Eg. \Storefront\MiscSettingsAssets\safeControls.config

Eg. \Storefront\Assets2\commerceRuntime.config

#4. Update the project’s Site URL property (5 projects in total)

#5. Build the solution

#6. Right click on the “CommonGlobalModules” project and click “Publish”

#7. Select “Publish to File System”

#8. Browse to “C:\Retail\OnlineChannel\CommonGlobalModules” and create a new folder called “Original_WSP”

#9. Select this “Original_WSP” folder as the destination to publish the “CommonGlobalModules.wsp” and click “Publish”

*If you’re getting an error - “The following file cannot be found: "C:\Retail\Retail SDK\Commerce Run-time\References\Microsoft.Dynamics.Commerce.Runtime.WorkflowFoundation.Activities.dll”, a quick workaround is to copy the required DLL into the reference folder.
Eg. Copy the file “Microsoft.Dynamics.Commerce.Runtime.WorkflowFoundation.Activities.dll” from “C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.Dynamics.Commerce.Runtime.WorkflowFoundation.Activities\v4.0_6.3.0.0__31bf3856ad364e35” to “C:\Retail\Retail SDK\Commerce Run-time\References”

#10. Right click on the “SharePoint.Web.Storefront” project and click “Publish”

#11. Select “Publish to File System”

#12. Browse to “C:\Retail\OnlineChannel\StoreFront” and create a new folder called “Original_WSP”

#13. Select this “Original_WSP” folder as the destination to publish the “Storefront.wsp” and click “Publish”

#14. Open up DemoData solution from “C:\Retail\Retail SDK\Online Channel\DemoData\SharePoint.DemoData.sln”

#15. Update the project’s Site URL property

#16. Build the solution

#17. Right click on the SharePoint.DemoData.Contoso project and click “Publish”

#18. Select “Publish to File System”

#19. Browse to “C:\Retail\OnlineChannel\ContosoDemoData” and create a new folder called “Original_WSP”

#20. Select this “Original_WSP” folder as the destination to publish the “ContosoDemoData.wsp” and click “Publish”

#21. Update the “oob-settings.xml” and “oob-topology.xml” in the folder “C:\Retail\OnlineChannel\tools”
*Update the value as required for deployment (More info: http://technet.microsoft.com/en-us/library/jj991927.aspx)

Eg.
For “oob-settings.xml”, set these
- <Setting key="SharepointPackageInstanceIdentifier" value="1" />
*The “1” correspond to the number on the site

- <Setting key="StoreFront_ShoppingCartExpiryTerm" value="3" />
- <Setting key="StoreFrontPublicSSLCertDirectory" value="%SystemDrive%\Sharepoint_Install\Certificates\SSL" />
- <Setting key="StoreFrontWebAppPoolUser" value="CONTOSO\RetailOnlineUser1" />
- <Setting key="ProductCatalogWebAppPoolUser" value="CONTOSO\Admin" />
- <Setting key="ProductCatalogName" value="Contoso product catalog" />
- <Setting key="STSWebAppPoolUser" value="CONTOSO\Admin" />
- <Setting key="RetailJobUser" value="CONTOSO\Admin" />
- <Setting key="ChannelId" value="5637144608" />
- <Setting key="DestinationId" value="9D58F661-7501-4E6D-B667-85F9F1A7C86D" />
- <Setting key="PublishingConnectorServiceInstanceServers" value="AX2012R2A" />
- <Setting key="FarmAdministratorAlias" value="CONTOSO\spfarm" />
- <Setting key="FarmAdministratorEmail" value="spfarm@contoso.com" />
- <Setting key="ChannelDatabaseServerName" value="AX2012R2A" />
- <Setting key="ChannelDatabaseName" value="RetailContosoStorefront" />
- <Setting key="IdentityProviderDatabaseServerName" value="AX2012R2A" />
- <Setting key="CustomClaimsProviderDatabaseServerName" value="AX2012R2A" />

For “oob-topology.xml”, set these
> Change the generate=”true” to “false”, so that it won’t generate the package (we’ll use the one generated in previous steps under “Original_WSP” later)
  <WSPPackage generate="false" deploy="true" retract="true" unpack="true">
  <RootFolder>..\CommonGlobalModules</RootFolder>

  <WSPPackage generate="false" deploy="true" retract="true" unpack="true">
  <RootFolder>..\StoreFront</RootFolder>

  <WSPPackage generate="false" deploy="true" retract="true" unpack="true">

  <RootFolder>..\ContosoDemoData</RootFolder>

> Set the line below if you’re not using Facebook authentication
  <TrustedIdentityTokenIssuer id="Facebook" install="false" deleteifexists="false">

#22. Open the file “C:\Retail\OnlineChannel\RetailJobs\SP\SharePoint.Services.RetailJobs_RetailPublishingJobFeature_1\Assets\RetailPublishingJob.App.config” and on Line 25, change the Version to 1.0.0.0 & PublicKeyToken to your token.
*As mentioned in Step #1, this is the manual work to change the version & public key token.

#23. Run SharePoint PowerShell as administrator

#24. Change the directory to “C:\Retail\OnlineChannel\tools”

#25. Execute the PowerShell script - .\Generate-WSP.ps1 oob-topology.xml oob-settings.xml

This will package up the retail jobs to “C:\Retail\OnlineChannel\RetailJobs\Final_WSP” and leave the other 3 intact (CommonGlobalModules, StoreFront, & ContosoDemoData).
After this step is done, we have one “Final_WSP” for retail jobs, this will be used during the deployment later.
At this point, the folder structure should looks like this (1 “Final_WSP” and 3 “Original_WSP”)

Before deployment, we need to prepare the other 3 “Final_WSP” from the “Original_WSP” (CommonGlobalModules, StoreFront, & ContosoDemoData).

#26. Update the “oob-topology.xml” in the folder “C:\Retail\OnlineChannel\tools” again
> Change the generate=”false” to “true”
  <WSPPackage generate="true" deploy="true" retract="true" unpack="true">
  <RootFolder>..\CommonGlobalModules</RootFolder>

  <WSPPackage generate="true" deploy="true" retract="true" unpack="true">
  <RootFolder>..\StoreFront</RootFolder>

  <WSPPackage generate="true" deploy="true" retract="true" unpack="true">
  <RootFolder>..\ContosoDemoData</RootFolder>

> Change the generate=”true” to “false”
  <WSPPackage generate="false" deploy="true" retract="true" unpack="false">
  <RootFolder>..\RetailJobs</RootFolder>

#27. Execute the PowerShell script - .\Prepare-FromWSP.ps1 oob-topology.xml oob-settings.xml
After the script execution completed, we’ll have “Final_WSP” for the other 3 folders (CommonGlobalModules, StoreFront, & ContosoDemoData)

#28. Execute the PowerShell script - .\InstallPrereqs-SPFarm.ps1 oob-topology.xml oob-settings.xml
*This will take quite some time to run

#29. Execute the PowerShell script - .\Deploy-FarmSolutions.ps1 oob-topology.xml oob-settings.xml
*This will take quite some time to run

The updated retail store DLL and code has now been deployed, you can start putting in breakpoint, attach to process and debug.

NOTE: Once this custom solution is deployed, next time when code is changed, just copy the DLL into the GAC (no need to redeploy the whole thing as above)


Perform debugging:
#1. Open the StoreFront solution (“C:\Retail\Retail SDK\Online Channel\StoreFront\StoreFront.sln”)

#2. Attach to the Online store process (Menu: Debug > Attach to Process…)
*Refers to Debug AX 2012 Retail Online Store commerce runtime services for details on how to identify and attach to the Online store process

#3. Put breakpoint to the code you wanted to debug, the breakpoint will appear in solid red dot

#4. Run the process that you wanted to debug (Eg. Click on the link on the Online store that will trigger the breakpoint)

NOTE:
If the debugging still not working, you might want to check the web.config to enable the debugging if it is not enabled yet.
Eg. <compilation batch="false" debug="true">