Dynamics Ax Forum: September 2012

Copying a record

I've experienced that one of the tasks often used when manipulating data is record copying.
For various reasons, an existing record needs to be modified and saved as a new one. The
most obvious example could be when a user requires a function that allows him or her to
quickly duplicate records on any of the existing forms.
There are several ways of copying one record into another in X++. In this recipe, we will explain
the usage of table data() method, global buf2buf() function, and their differences. As an
example, we will copy one of the existing customer records into a new one. Normally, copying
a customer involves more data around the customer like customer contacts, bank accounts,
printing settings, and similar, but for demonstration purposes, we will assume that our goal is
only to copy the customer record itself without worrying about related data.

Before you install the Reporting Services extensions [AX 2012]

Imparted from Here

Before you install the Microsoft SQL Server Reporting Services extensions, you must complete the following procedures.

Verify that you have the required permissions to install the Reporting Services extensions

To install the Reporting Services extensions, you must have the required permissions. For more information, see Verify that you have the required permissions for installation.

Install prerequisites

On the computer where Reporting Services is installed, or where you plan to install Reporting Services, run the prerequisite validation utility to verify that system requirements have been met. For information about how to run the prerequisite validation utility, see Check prerequisites.

For more information about the hardware and software requirements for Microsoft Dynamics AX, see the system requirements.

Configure the Reporting Services instance by using the Reporting Services Configuration Manager tool

Use the following procedure to configure the Reporting Services instance.

Note

If you installed the Reporting Services instance in its default configuration, Reporting Services is already configured for you. We recommend that you complete this procedure to verify that the options are configured correctly.

Open Reporting Services Configuration Manager (Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager).
Connect to your Reporting Services instance.
Configure the options that are described in the following table. For detailed information about each option, see the SQL Server documentation.

Click this option…

To do this…

[ServerName]\[InstanceName]

Verify that the Reporting Services instance is running. If it is not running, click Start.

Service Account

The action you should take depends on the configuration mode you selected when installing the Reporting Services instance.

· If you selected the Install the native mode default configuration option, no action is required. The service account is set to the account you specified when installing the Reporting Services instance.

· If you selected the Install, but do not configure the report server option, select the built-in account, Network Service, as the service account.

Note

When you install the Reporting Services extensions, the Business Connector proxy account will automatically be assigned as the service account for the Reporting Services instance.

Web Service URL

Create a virtual directory for the Reporting Services web service. By default, the virtual directory is named ReportServer and the URL is http://[SSRSServerName]:80/ReportServer.

Database

Create a database for the Reporting Services instance. By default, the database is named ReportServer.

Report Manager URL

Create a virtual directory for Report Manager. Report Manager is the website that reports are published to. By default, the virtual directory is named Reports and the URL is http://[SSRSServerName]:80/Reports.

E-mail Settings

This option is not required. For more information, see the SQL Server documentation.

Execution Account

Take no action.

Note

When you install the Reporting Services extensions, the Business Connector proxy account will automatically be assigned as the execution account for the Reporting Services instance.

Encryption Keys

This option is not required. For more information, see the SQL Server documentation.

Scale-out Deployment

This option is not required. For more information, see the SQL Server documentation.

Click Exit to close Reporting Services Configuration Manager.

Configure the Reporting Services instance for local administration

To administer an instance of the report server locally, you must complete additional configuration steps when you deploy Reporting Services on Windows Server 2008. Windows Server 2008 limits the overuse of elevated permissions by removing administrator permissions when you access applications. If you are a member of the local Administrators group, you run most applications as if you are using the Standard User account because the operating system removes permissions.

Although this practice improves the overall security of your system, it prevents you from using the predefined, built-in role assignments that Reporting Services creates for local administrators. However, by completing additional configuration steps, you can manage the report server's content and operations by using standard user permissions. For instructions, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008 on TechNet.

After you have configured the Reporting Services instance for local administration, verify that you can access the sites that are listed in the following table.

Web site	Default URL
Reporting Services web service	http://[SSRSServerName]:80/ReportServer
Report Manager	http://[SSRSServerName]:80/Reports

Verify that you have the required permissions for installation [AX 2012]

Imparted from Here

Before you begin the installation of Microsoft Dynamics AX, work with a system administrator to make sure that the account that you log on with at each server has appropriate permissions.

The permissions in the following table are implemented based on the principle of least privilege.

In all cases, you must be a member of the Administrators group on the local computer where you are installing a component.

The following table lists the permissions that are required in addition to administrator access on the local computer.

Component	Additional permissions that are required to install the component
Databases	Membership in the dbcreator role on the instance of Microsoft SQL Server. If you install the databases remotely from a computer other than the database server, you must log on to the remote computer by using an account that is an administrator on the SQL Server computer. Setup requires access to SQL Server services.
Application Object Server (AOS)	Membership in the sysadmin role on the instance of SQL Server that you want to connect to
Enterprise Portal for Microsoft Dynamics AX	· Membership in the System administrator role in Microsoft Dynamics AX · Membership in the Administrators group in Windows on the Web server · Membership in the Farm Administrators group in Microsoft SharePoint 2010 products · Membership in the dbcreator role on the instance of SQL Server that is used for SharePoint 2010 products · Membership in the WSS_Content_Application_Pools database role in the SharePoint_Config database
Enterprise Search	· Membership in the System administrator role in Microsoft Dynamics AX · Membership in the Administrator group in Microsoft SharePoint Services · Membership in the dbcreator role on the instance of SQL Server that is used for Microsoft SharePoint Services
Help server	Membership in the System administrator role in Microsoft Dynamics AX
Microsoft SQL Server Reporting Services extensions	Membership in the System administrator role in Microsoft Dynamics AX
Microsoft SQL Server Analysis Services configuration	· Membership in the System administrator role in Microsoft Dynamics AX · Membership in the SQL Server securityadmin server role · Membership in the SQL Server db_owner database role for the Microsoft Dynamics AX database
Client	None
Microsoft Office add-ins	None
Remote Desktop Services integration	None
Debugger	None
Visual Studio Tools	None
Trace Parser	None
Web services on Internet Information Services (IIS)	Membership in the System administrator role in Microsoft Dynamics AX
.NET Business Connector	None
Synchronization proxy	· Membership in the dbowner database role in the SQL Server database for Microsoft Project Server · Membership in the System administrator role in Microsoft Dynamics AX
Synchronization service	Membership in the System administrator role in Microsoft Dynamics AX
Management utilities	None
Retail Headquarters	None
Retail POS	None
Retail Store Connect	None
Retail Transaction Service	None
Retail Store Database Utility	None
Retail POS Plug-ins	None
RapidStart Connector	None

Top of Form

Naming schema for Microsoft SQL Server software update packages

This post is imparted from Here

Microsoft has adopted a standardized naming schema for all the software update packages for SQL Server that are created and distributed.
A software update package is an executable (.exe or .msi) file that contains one or more files that may be applied to Microsoft SQL Server installations to correct a specific problem. Software update packages are distributed by Microsoft Customer Support Services (CSS) to customers whose computers are affected by a specific problem.
Microsoft has adopted a naming schema for software update packages for the following reasons:

Creates consistency across Microsoft software update packages
Easier to search for software updated packages and Knowledge Base articles
Clear identification of the language and SQL Server version for which the software update package are applicable

Package information and release types

Each software update package that is selected at download time is contained within a self-extracting executable that facilitates easy installation and deployment of the software update package.
SQL Server software update packages typically fall into two major release types:

GDR (General Distribution Release): GDR releases are reserved for those key fixes identified by SQL Server support to potentially affect a broad customer base.
Hotfix: Hotfix releases are typically for fixes to isolated issues not affecting a large customer base; while the product is in mainstream support. Hotfix are released in two major types:
- COD (Critical On Demand) or OD (On Demand): COD or OD releases are reserved for critical customer requests where key business functionality is impaired from the issue encountered. As the nature of the request, these releases do not follow a regular cadence.
- CU (Cumulative Update): CU releases are non critical requests which provide fixes for isolated issues not affecting key business functionality. The CU releases on a two-month cadence while the product and service pack are in mainstream support.

To learn more about the ISM and the different release types, which SQL Server servicing follows, see the following Microsoft Knowledge Base article:

935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

Packages and file naming schema

Naming schema for SQL Server software update packages

SQL Server software update packages can be easily identified using the following naming schema.

Software update package name schema

To distinguish between the various software update packages available online, the following schema is employed:

<product name or product program name>_<SP number or RTM>_<servicing release>_<KB article number>_<build number optional>_<architecture identifier>

Extracted SQL Server file name schema

Once the primary SQL Server software update package has been downloaded and extracted, the file name will resemble the following:

<product name or component>-<KB article number>-<build number optional>-<version optional>-<architecture Identifier>-<language code optional>.exe

Extracted feature pack name schema

Once a software update package for a feature pack has been downloaded and extracted, the file name will resemble the following:

[feature pack file name].msi

ProductName This is the full product name, which includes the product version information. For SQL Server, this attribute could be one of the following:
- SQLServer2005
  SQL Server 2005
- SQLServer2008
  SQL Server 2008
- SQLServer2008R2
  SQL Server 2008 R2
SP number or RTM The service pack level of the product or component which it can be applied on top of. RTM indicates the product without any service packs installed.
KB article number The Microsoft Knowledge Base article number that is associated with the software update.
Servicing Release The release type for the software update. For details, visit the “Package information and release types” section.
- COD: Critical On Demand
- OD: On Demand
- CU: Cumulative Update followed by the cumulative update release number
Architecture identifier This field is used to indicate on which processor architecture the particular hotfix package runs. The current options are the following:
- x86: This package runs on x86 platforms.
- ia64: This package runs on Itanium IA-64 platforms for 64-bit.
- x64: This package only runs on AMD x64 and compatible systems.
Version An optional field that indicates the version of the software release.
Build number An optional field which is used to indicate the SQL Server build number included in the software update.
For example, in SQL2000-KB840223-8.00.1007-ia64-ENU.exe, the build version of SQL Server is 8.00.1007. This will correspond to the file version of Sqlservr.exe and to the returned value from @@version run against this server instance.

Software update package and extracted file name mapping

The following tables illustrates the mapping between the “Download File Name” on the hotfix download page and the actual name of the package once downloaded and extracted.

SQL Server software Update Package

Package	Software Update Package Name	Extracted SQL Server File Name
CU Package for SQL Server 2005	SQLServer2005_SPx_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005-KBxxxxxxx-Arch-Lang.exe
CU Package for SQL Server 2008	SQLServer2008_RTM_CUxx_kbxxxxxx_10_00_xxxx_Arch SQLServer2008_SPx_CUxx_kbxxxxxx_10_00_xxxx_Arch	SQLServer2008- KBxxxxxxx-Arch.exe
CU Package for SQL Server 2008 R2	SQLServer2008R2_RTM_CUxx_kbxxxxxx_10_50_xxxx_Arch SQLServer2008R2_SPx_CUxx_kbxxxxxx_10_50_xxxx_Arch	SQLServer2008R2-KBxxxxxxx-Arch.exe
CU Package for SQL Server 2012 R2	SQLServer2012_RTM_CUxx_kbxxxxxx_11_00_xxxx_Arch SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQLServer2012-KBxxxxxxx-Arch.exe

SQL Server Feature Pack

Package	Software Update Package Name	Extracted SQL Server File Name
SQL Native Client	2005_SPx_SNAC_CUxx_kbxxxxxx_9_00_xxxx_Arch 2008_RTM_SNAC_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SNAC_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SNAC_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SNAC_CUxx_kbxxxxxx_10_50_xxxx_Arch	sqlncli.msi
SQL Writer	2005_SPx_SQLWriter_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLWriter.msi
AS OLE DB for SQL Server 2005	2005_SPx_ASOLEDB_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_ASOLEDB9.msi
AS OLE DB for SQL Server 2008	2008_RTM_ASOLEDB_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_ASOLEDB_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_ASOLEDB_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_ASOLEDB_CUxx_kbxxxxxx_10_50_xxxx_Arch	SQLServer2008_ASOLEDB10.msi
AS OLE DB for SQL Server 2012	2012_RTM_ASOLEDB_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_ASOLEDB_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQL_AS_OLEDB.msi
ADMOMD.net	2005_SPx_ADMOMD_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_ADOMD.msi
XMO/SMO (Shared Management Objects) for SQL Server 2005	2005_SPx_XMO_CUxx_kbxxxxxx_9_00_xxxx_Arch	SQLServer2005_XMO.msi
XMO/SMO (Shared Management Objects) for SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012	2008_RTM_SMO_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SMO_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SMO_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SMO_CUxx_kbxxxxxx_10_50_xxxx_Arch 2012_RTM_SMO_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_SMO_CUxx_kbxxxxxx_11_00_xxxx_Arch	SharedManagementObjects.msi
Reporting Services for SharePoint for SQL Server 2005	2005_SPx_RSShrPnt_CUxx_KBxxxxx_9_00_xxxx_arch	SharePointRS.msi
Reporting Services for SharePoint for SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012	2008_RTM_RSShrPnt_CUxx_KBxxxxx_10_00_xxxx_arch 2008_SPx_RSShrPnt_CUxx_KBxxxxx_10_00_xxxx_arch 2008R2_RTM_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch	rsSharePoint.msi (x86 and x64 only)
Reporting Services for SharePoint for SQL Server 2008 R2	2008R2_RTM_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_RSShrPnt_CUxx_KBxxxxx_10_50_xxxx_arch	rsSharePoint.msi (x64 only)
Report Builder Click Once	2008_RTM_RBClckOnc_CUxx_kbxxxxx_10_00_xxxx_Arch 2008_SPx_RBClckOnc_CUxx_kbxxxxx_10_00_xxxx_Arch	RB2ClickOnce.msi (x86 and x64 only)
Report Builder for SQL Server 2008	2008_RTM_RprtBlder_CUxx_KBxxxx_10_00_xxxx_Arch 2008_SPx_RprtBlder_CUxx_KBxxxx_10_00_xxxx_Arch	ReportBuilder.msi (x86 only)
Report Builder for SQL Server 2008 R2	2008R2_RTM_RprtBlder_CUxx_KBxxxx_10_50_xxxx_Arch 2008R2_SPx_RprtBlder_CUxx_KBxxxx_10_50_xxxx_Arch	ReportBuilder3.msi
Sap BI	2008_RTM_SapBI_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008_SPx_SapBI_CUxx_kbxxxxxx_10_00_xxxx_Arch 2008R2_RTM_SapBI_CUxx_kbxxxxxx_10_50_xxxx_Arch 2008R2_SPx_SapBI_CUxx_kbxxxxxx_10_50_xxxx_Arch 2012_RTM_SapBI_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_SapBI_CUxx_kbxxxxxx_11_00_xxxx_Arch	SapBI.msi
Stream Insight	2008R2_RTM_StrmNsght_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_StrmNsght_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_StrmNsght_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_StrmNsght_CUxx_KBxxxxx_11_00_xxxx_arch	StreamInsightClient.msi
Synchronization	2008R2_RTM_Synch_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_Synch_CUxx_KBxxxxx_10_50_xxxx_arch	Synchronization.msi
PowerPivot for Excel Client	2008R2_RTM_PPExcel_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_PPExcel_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_PPExcel_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_PPExcel_CUxx_KBxxxxx_11_00_xxxx_arch	PowerPivot_for_Excel_x86.msi
Stream Insight and Server	2008R2_RTM_PPServer_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_PPServer_CUxx_KBxxxxx_10_50_xxxx_arch 2012_RTM_PPServer_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_PPServer_CUxx_KBxxxxx_11_00_xxxx_arch	StreamInsight.msi
Master Data Services	2008R2_RTM_MDS_CUxx_KBxxxxx_10_50_xxxx_arch 2008R2_SPx_MDS_CUxx_KBxxxxx_10_50_xxxx_arch	MasterDataServices.msi (x64 only)
Data-Tier Application Framework	2012_RTM_DAC_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_DAC_CUxx_KBxxxxx_11_00_xxxx_arch	DACFramework.msi
ADOMD.NET	2012_RTM_ADMOMD_CUxx_kbxxxxxx_11_00_xxxx_Arch 2012_SPx_ADMOMD_CUxx_kbxxxxxx_11_00_xxxx_Arch	SQL_AS_ADOMD.msi
LocalDB	2012_RTM_LocalDB_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_LocalDB_CUxx_KBxxxxx_11_00_xxxx_arch	SqlLocalDB.msi
Transact-SQL Language Service	2012_RTM_TSQLLAN_CUxx_KBxxxxx_11_00_xxxx_arch 2012_SPx_TSQLLAN_CUxx_KBxxxxx_11_00_xxxx_arch	TSqlLanguageService.msi

Best practices As a best practice, consider providing a name that you can use to easily identify packages during downloads.

Package descriptions

This section describes each of the packages that are listed and their purposes. Installing a newer MSI package over an older MSI package removes the older version in favor of the newer version. Uninstalling a feature pack update by using an MSI package will completely remove the component. However, for the main CU package, uninstalling the .exe file causes a rollback to the previously installed version.

SQL Server software update package

File name

SQLServer2005-KBxxxxxxx-Arch-Lang.exe (For SQL Server 2005)

SQLServer2008-KBxxxxxxx-Arch.exe (For SQL Server 2008)
SQLServer2008R2-kbxxxxxx-Arch (For SQL Server 2008 R2)
SQLServer2012-kbxxxxxx-Arch (For SQL Server 2012)

Purpose The SQL Server software update package will update the SQL Server instance by using a collection of all SQL Server hotfixes that have been created since the product was released. The package will apply updates to all installed components if an update has been made. This package will update SQL Server DB & Engine, Analysis Service, Integration Services, Reporting Services, Replication Engine, and Manageability.

SQL Server Native Client

File name

sqlncli.msi (For SQL Server 2005, 2008, 2008 R2, 2012)

Purpose Microsoft SQL Server Native Client is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server features. This installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

Report builder

File name

ReportBuilder.msi (For SQL Server 2008)
ReportBuilder3.msi (For SQL Server 2008 R2)

Purpose Report Builder provides an intuitive report authoring environment for business and power users with a Microsoft Office look and feel. Report Builder supports the full capabilities of Report Definition Language (RDL) including flexible data layout, data visualizations, and richly formatted text features of SQL Server Reporting Services. The download provides a stand-alone installer for Report Builder.

Report Builder Click Once

File name

RB2ClickOnce.msi (For SQL Server 2008, SQL Server 2008 R2)

Purpose The Click Once version of Report Builder designed to be started from Report Manager or a SharePoint library.

Reporting Services for SharePoint

File name

SharePointRS.msi (For SQL Server 2005)

rsSharePoint.msi (For SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012)

Purpose Microsoft SQL Server Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 and 2008 report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.

SQL Writer

File name

SQLWriter.msi (For SQL Server 2005)

Purpose The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup. Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.

AS OLE DB

File name

SQLServer2005_ASOLEDB9.msi (For SQL Server 2005)

SQLServer2008_ASOLEDB10.msi (For SQL Server 2008 and SQL Server 2008 R2)

SQL_AS_OLEDB.msi (For SQL Server 2012)

Purpose The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining. Note: Microsoft SQL Server Analysis Services OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0.

XMO/SMO (Shared Management Objects)

File name

I have SQL Server 2008. Do I need to apply both the SQL Server software update package and the SQL Server Native Client package on the server to get all SNAC fixes (e.g., consider linked server scenario where server is also a client)?
If client and server are on the same machine, individually installing the SQL Server Native Client package is not required. If client and server are separate, apply the SQL Native Client package to the client, and apply the SQL Server Software Update Package to the server to get all updates.
I have SQL Server 2005. Do I need to apply both the SQL Server software update package and the SQL Server Native Client package on the server to get all SNAC fixes?
Yes, both the SQL Server software update package and SQL Native Client Package are required to update the server and must be downloaded separately.
Do I need to install the Feature Pack and the SQL Server Software Update Package?
Each KB article will clearly identify the packages which need to be applied to the computer to obtain the described fix. SNAC and other MSI’s (SQLWriter, XMO, RS Sharepoint, RB Clickonce etc.) are version bumped every CU even if there are no new fixes.
How will we adjust the KB’s once we reach 999999?

Our KB’s are currently 6 digits but will be going to 7 soon. The schema shown above in this doc is using the Letter’s KB plus 5 X’s for a total of 7 characters to show our expected schema once we cross the 1,000,000 mark. Until such time do continue to use the given 6 digit KB.
How do I apply the SQLWriter.msi feature pack?
At this time you will need to run the SQLWriter.msi after the CU or COD package that you have downloaded for you fix.

sp_spaceused SQl Function

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Syntax

sp_spaceused [[ @objname = ] 'objname' ]
[,[ @updateusage = ] 'updateusage' ]

Arguments

[ @objname=] 'objname'

Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.

If objname is not specified, results are returned for the whole database.

objname is nvarchar(776), with a default of NULL.

[ @updateusage=] 'updateusage'

Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. updateusage is varchar(5), with a default of false.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If objname is omitted, the following result sets are returned to provide current database size information.

Column name	Data type	Description
database_name	nvarchar(128)	Name of the current database.
database_size	varchar(18)	Size of the current database in megabytes. database_size includes both data and log files.
unallocated space	varchar(18)	Space in the database that has not been reserved for database objects.

Column name	Data type	Description
reserved	varchar(18)	Total amount of space allocated by objects in the database.
data	varchar(18)	Total amount of space used by data.
index_size	varchar(18)	Total amount of space used by indexes.
unused	varchar(18)	Total amount of space reserved for objects in the database, but not yet used.

If objname is specified, the following result set is returned for the specified object.

Column name	Data type	Description
name	nvarchar(128)	Name of the object for which space usage information was requested. The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats² or sys.dm_db_index_physical_stats³ dynamic management views to obtain equivalent size information.
rows	char(11)	Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reserved	varchar(18)	Total amount of reserved space for objname.
data	varchar(18)	Total amount of space used by data in objname.
index_size	varchar(18)	Total amount of space used by indexes in objname.
unused	varchar(18)	Total amount of space reserved for objname but not yet used.

Remarks

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.

When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Note
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

Note

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

Permissions

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

Examples

A. Displaying disk space information about a table

The following example reports disk space information for the Vendor table and its indexes.

USE AdventureWorks2012;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO

B. Displaying updated space information about a database

The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned.

USE AdventureWorks008R2;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO

Dynamics AX: strScan Function

Searches a text string for an occurrence of another string.

int strScan( str _text1, str _text2, int _position, int _number)

Parameters

Parameter	Description
_text1	The string in which to search.
_text2	The string to find.
_position	The first position in the _text1 parameter to perform a comparison.
_number	The number of positions in the _text1 parameter for which to retry the comparison. If there is a minus sign in front of the _number parameter, the system searches the number of characters in reverse order from the specified position.

Return Value

The position in the string at which the specified string was found; otherwise, 0.

Remarks

The comparisons are not case sensitive.

Values for the _position parameter that are less than 1 are treated as 1.

The direction of the scan is controlled by the sign specified in the _number parameter. A positive sign indicates that each successive comparison will start one position closer to the end of the string. A negative sign indicates that each comparison will start one position closer to the start of the string.

strScan("ABCDEFGHIJ","DEF",1,10); //Returns the value 4.
strScan ("ABCDEFGHIJ","CDE",10,-10); //Returns the value 3.

Dynamics Ax : Read and Write from/ To Excel File using X++

the Main Classes will be used to read from and write to Excel file

1. SysExcelApplication to Access Excel File
2. SysExcelWorkbooks and SysExcelWorkbook to access workbook.
3. SysExcelWorkSheets to access worksheet.
4. SysExcelCells , SysExcelCell to access the cells.

Writing Data to Excel file
How it works
1. Use SysExcelApplication class to create excel file.
2. Use SysExcelWorkbooks and SysExcelWorkbook to create a blank workbook(by default 3 worksheets will be available).
3. Use SysExcelWorkSheets to select worksheet for writing data.
4. SysExcelCells to select the cells in the excel for writing the data.
5. SysExcelCell to write the data in the selected cells.
6. Once you done with write operation use SysExcelApplication.visible to open
file.

static void Write2ExcelFile(Args _args)
{
    InventTable inventTable;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    cell = cells.item(1,1);
    cell.value("Item");
    cell = cells.item(1,2);
    cell.value("Name");
    row = 1;
    while select inventTable
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(inventTable.ItemId);
        cell = cells.item(row, 2);
        cell.value(inventTable.ItemName);
    }
    application.visible(true);
}

Reading Data from Excel File
static void ReadExcelFile(Args _args)

{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row;
    ItemId itemid;
    Name name;
    FileName filename;

;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename = "C:\\item.xls";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }

    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    do
    {
        row++;
        itemId = cells.item(row, 1).value().bStr();
        name = cells.item(row, 2).value().bStr();
        info(strfmt('%1 - %2', itemId, name));
        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
}

How to remove the duplicate objects in AOT Using X++

Scenario : Nags came across a requirement for duplicating the production instance.He followed the following steps
1. Setup a new instance and Copied the var.aod files from production instance and restored the DB with production DB.
2. After that he kept the application for compilation. When the compilation ends , he tries to synchronize the Data dictionary. But he was getting error during synchronization because some objects were duplicated in layers. e.g System will show two ItemId fields at table level.
Use the following code to delete the identical copy.
static void FindAndDeleteIdenticalObjects(Args _args)
{
    SysTreeNode comparable1, comparable2;
    TreeNode curLevelTreeNode, upperLevelTreeNode;
    UtilIdElements utilElements, joinUtilElements;
    ;
    while select UtilElements
    where UtilElements.utilLevel == UtilEntryLevel::var &&
    (
        UtilElements.recordType == UtilElementType::Form ||
        Utilelements.recordType == UtilElementType::Report ||
        Utilelements.recordType == UtilElementType::Table ||
        Utilelements.recordType == UtilElementType::Class ||
        Utilelements.recordType == UtilElementType::Enum ||
        Utilelements.recordType == UtilElementType::ExtendedType
    )
    {
        //Should use join if for a normal table, but not applicable for UtilElements
        //Performance hit if use exists join
        select firstonly recid from joinUtilElements
        where joinUtilElements.utilLevel != UtilElements.utilLevel &&
        joinUtilElements.name == UtilElements.name &&
        joinUtilElements.recordType == UtilElements.recordType;
        if (joinUtilElements.RecId)
        {
            //Thanks for Jim Shepherd here
            curLevelTreeNode = SysTreeNode::findNodeInLayer(UtilElements.recordType, UtilElements.name, UtilElements.parentId, UtilElements.utilLevel);
            upperLevelTreeNode = SysTreeNode::getLayeredNode(curLevelTreenode, 1);
            comparable1 = SysTreeNode::newTreeNode(curLevelTreeNode);
            comparable2 = SysTreeNode::newTreeNode(upperLevelTreeNode);
            if (SysCompare::silentCompare(comparable1, comparable2))
            {
                info(strFmt("Element name: %1, Element type: %2", UtilElements.name, enum2str(UtilElements.recordType)));
                //Remove the node
                curLevelTreeNode.AOTdelete();
            }
        }
    }
}

Dynamics Ax 2012 Reporting Architecture

The following diagram illustrates the architecture of the reporting functionality in
Microsoft Dynamics AX.

A user requests a report.
A menu item in the Microsoft Dynamics AX client may be bound to a report for Microsoft SQL Server Reporting Services. After a user clicks the menu item, a parameters form is displayed to the user. The user enters parameters to filter the data that is displayed on the report.
The Microsoft Dynamics AX client then requests the report from an instance of Reporting Services. The request includes the parameters that the user entered.
Reporting Services receives the request and requests the report data from the Microsoft Dynamics AX server.
Reporting Services receives the request and examines the report on the server. The report is stored as an .rdl file. The .rdl file indicates the report’s data source. The data source may be a Microsoft Dynamics AX query, a report data provider class, or an external data source that is accessed through report data methods.
If a Microsoft Dynamics AX data source is used for the report, Reporting Services uses the Microsoft Dynamics AX data extension to retrieve the data.
Reporting Services then requests metadata about the data source from Microsoft Dynamics AX. Then Reporting Services requests the data for the report.
The Microsoft Dynamics AX server receives the request and sends the report data back to Reporting Services.
The Microsoft Dynamics AX services examine the query in the Application Object Tree (AOT) to return the requested metadata. The services also run the query to generate the data for the report.
Microsoft Dynamics AX then returns the metadata and data to Reporting Services.
Note
Microsoft Dynamics AX enforces security on all data that it returns. If the user who is running the report is not allowed to see a specific field, the data for that field is not returned.
Reporting Services renders the report and sends it to the Microsoft Dynamics AX client.
The Microsoft Dynamics AX customization extension formats the report. The customization extension uses metadata to provide automatic formatting of data and can affect the positioning and layout of elements on the report.
Reporting Services then renders the report into a visual representation and sends that representation to the Microsoft Dynamics AX client.
The report is displayed to the user.
The Microsoft Dynamics AX client displays the report to the user in the report viewer control.

Delete User Layer with AX 2012 Management Shell

this is going to be a short post about how to delete a user layer from the model store in AX 2012.

to delete a user layer from the model you first need to install theManagement Tools from your AX 2012 setup. Once this is done, you can access the Microsoft Dynamics AX Management Shell and write the following script:

axutil delete /layer:Usr

See the picture below for more details:

As you can see, deleting a layer is not difficult at all and it happens fairly quick too. Also, it is very important that you have a backup of the databasebefore performing this action.

How to create project with all objects in Specific layer In Dynamics AX

Challenges:

When investigating code in Ax, it may be useful to get a quick overview of all the code in a specific layer.
When you intend to move code from one layer to another, you may need a list of objects in a specific layer.

Follow these steps in order to get a solution for both challenges with ease:
Step 1: Create a new project.

You can rename your project as desired, we'll call it Project_USR_Layer for now. Then open the newly created project.

Step 2: Choose Advanced Filter/Sort from the toolbar.

If you are into keyboard short-cuts, you can use Ctrl-F3 for this as well.
A new dialog pops up.

I like my objects grouped in the project as they are grouped in the AOT, so I've chosen Groupings - AOT.
Step 3: Select the required objects.

Use the Select button on the dialog to select the objects you require for your project.
We want select all AOT objects from a specific layer, so we'll use the UtilLevel field in the selection criteria.

From the screenshot you can see that all objects from the USR layer are selected. If desired, you can limit the type of AOT objects for your project as well, by using the recordTypefield.
Confirm twice and the objects are added to your project. This may take some time, so be patient :-)

Similar challenge:

You can use the same method for selecting AOT objects modified/created by a specific programmer. Just set different selecting criteria in your query.

The server need to free resources and terminates client sessions, AX Hang up

Imparted from Here

Why does the server need to free resources and terminates client sessions?

You might got into this situation before... you are working in the Dynamics AX Client as usual and all of a sudden you see the following warning message:

Communication error
The server needs to free resources. Your session has been terminated.

After you click OK the Dynamics AX Client is closed (terminated).

OK, what has happened now? While the error message is technically correct, it's a bit misleading. The AOS would like to tell the Client that it is running out of memory and is trying to free used resources to keep "surviving". Usually when one user is getting this message other users are starting seeing this message too - until the AOS is restarted. It can also happen, and usually does, that the AOS service is crashing soon after the first users are seeing this message.

This message is often an indicator of either:

a Memory Leak or
a High Memory Situation

What is a Memory Leak or a High Memory Situation?

A Memory Leak is a situation where for an object (or more general a resource) memory was once requested, that is however not freed when the object (resource) is no longer needed and the last reference to the object was removed.

So if the application developer decides it is a good idea to hold GB of data in memory (e. g. for caching) this is not a Memory Leak – although looking at the memory consumption of the process it might look like. Unfortunately this example happens too often in reality and so some badly written X++ code can easily cause lots of trouble. We call this "not clever" usage of memory not a Memory Leak but instead a High Memory Situation.

So the difference is mainly if there is any chance to release the object (resource) and it's allocated memory at a later time or not.

Memory Leaks in Dynamics AX are seldom and usually located in the Dynamics AX Kernel itself. This does not mean they don't exists, in Dynamics AX 4.0 the issues KB948185, KB958213, KB973633 and KB978110 are some examples of a real Memory Leak.

But saying that, most of the time when you think you are running into a Memory Leak in reality you are facing a High Memory Situation caused by custom X++ code or custom modifications of standard code.

Why is a Memory Leak or a High Memory Situation bad?

Each user mode process in Windows can allocate memory – Virtual Memory. The amount of Virtual Memory depends on the version / configuration of Windows and the processor architecture (x86 or x64). It does not directly depend on the amount of Physical Memory (actually it could if the application requires but for Dynamics AX it does not). For more information on Virtual Memory see the Blog post Memory Management 101.

In a 32bit (x86) version of Windows in total 4 GB of Virtual Memory can be addressed in total however only a part of the address space is available for the user mode process, by default 2 GB. There are situation where 3 GB and even 4 GB are available for the 32bit user mode process, however I don't want to get into detail here. For more information see the KB article 888732.

The important fact to remember is: We have a limited amount of addressable Virtual Memory and if this is used up we are running into an out of memory situation. And unfortunately the Dynamics AX Kernel doesn't handle out of memory situations gracefully. When we are running out of memory the process most of the time simply terminates (crashes).

Tracking down the root cause

Finding out the cause of a Memory Leak or a High Memory Situation is a harder task. In the following sections I'm describing steps that can help to narrow down the cause. But still a lot of manual work is required to identify the piece of code bringing Dynamics AX into trouble.

Finding patterns in the memory consumption

As a start we should find out how the memory consumption behaves. Does the once allocated memory keep allocated or is it released when users are logging off or a lengthy running task is completed?

The Windows Task Manager can give us a first hint if the memory consumption changes quickly. On the Processes tab the column Virtual Memory Size (<= Win2003) or Memory – Working Set (>= Vista) is the interesting one to look at.

If the memory consumption is changing slower over a longer period of time the Performance Monitor is the better tool of choice. In Performance Monitor the following counters are usually helpful:

Process : Private Bytes : Ax32serv (all of them)
Process : Virtual Bytes : Ax32serv (all of them)
Processor : % Processor Time : _Total
Memory : Available Mbytes
Memory : Pages/sec

The Private Bytes and Virtual Bytes are the important ones, the other three just complete the picture.

Once the Performance Monitor log was recorded it needs to be analyzed and checked for common patterns:

Does the memory increase steadily in working hours or in after working hours (>> batch jobs)?
How fast is the memory increasing?
Is the maximum amount of allocated memory kept or do we see in general a release of memory but maybe not the whole allocated memory is freed?

Checking these steps can already give you a first idea where to look at.

Separating users on different AOS instances

If enough AOS instances are available or enough licenses for new AOS servers exist is has proved to be a good idea to separate three groups of users to their own AOS instances to see what group of users is involved in the issue:

Batch users
Business Connector users
Regular users

Seeing which user groups AOS instance is suffering from the memory consumption gives another ideas where to look at.

And by the way, looking at customization is always a good idea to start with.

The root cause is the load

Lets imagine you have a High Memory Situation caused by your custom X++ code. But looking at your X++ code you see it is optimal and using memory and resources wisely. Simply too many users at the same time are executing the code.

If you can't limit the number of users executing your code what can you do next?

You can add an additional AOS instance and distribute the load.
If you are running on a 32bit O/S you can think about switching to a 64bit O/S.
If you need continue to run on a 32bit O/S you can think about enabling the /3GB switch in boot.ini

Common issues

Over the time we have seen some common issues that cause effects identified as Memory Leaks or High Memory Situation:

For Dynamics AX 4.0 SP1 and SP2 its good to have a later Kernel installed. On SP1 you should use a Kernel version >= 4.0.2500.939 and on SP2 >= 4.0.2503.953.
Check for changes on the Database Tuning tab in the Dynamics AX Server Configuration Utility. By default the text boxes on this tab are empty. Usually the default settings are good and should not be changed! (See also: Random clients crashes)
In Dynamics AX 4.0 having the configuration key SysDeletedObjects40 still enabled after upgrade from Dynamics AX 3.0 was completed successfully can cause issues (e. g. when printing reports with a company logo). Recommendation is to turn this configuration key off (Administration | Setup | System | Configuration).
When custom external application are existing that are using the .NET Business Connector to connect to the AOS please check out the Blog post Debugging X++ Object Leaks for more information on how to correctly free Dynamics AX class and table objects.

Claim DB Space After Deleting Records in Table - Reduce DB Space

Recently I have delete 2 million unwanted records from my sql server database table, what i realise is even after deleting records, space used by database is not reducing.
After browsing help available on Internet, I found out
1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.
2) Even after deleting table , sql server doesn't reduce size of database.
3) Instead of Freeing space for deleted records, sql server marks pages containing deleted

records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.
So In order to claim database space after deleting records in Table, go through following steps:
1) Check what is Size of your Database using following command?
Exec sp_spaceused
2) Delete Records from table, If you have already did that skip this step.
3) Run below command to claim unused database space.
DBCC SHRINKDATABASE(0)
DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.
Best Practise to use this command

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the databasesize grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

More reading on this command
http://msdn.microsoft.com/en-us/library/ms190488.aspx
Few other things of Interest
If you have Created, Alter or Drop any Database table recently then run below command.
DBCC UPDATEUSAGE(0)
DBCC UPDATEUSAGE(0) - Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
More reading on this command
http://msdn.microsoft.com/en-us/library/ms188414.aspx
Example showing how this command helps me to reduce size of my database after deleting records from table.
1) Take Backup of your Production Database.
2) Take Backup of Table Scripts of your Production Database.
3) Create Test Database in Local Environment
5) Run Tables creation script
6) Restore Production Database to Test Database in local environment
I am assuming you are familiar with above steps, actual steps begin after this.
I am also assuming that you have already deleted unwanted records in table.
7) Check Size of your Database
Exec sp_spaceused

8) Run Update Usage command
DBCC UPDATEUSAGE(0)

9) Check Size of your Database
Exec sp_spaceused

10) Run Shrink Database command
DBCC SHRINKDATABASE(0)

11) Check Size of your Database
Exec sp_spaceused

If everything goes smooth then you would see that your database size is reduced.