Database synchronization

Whenever the object data model in Dynamics AX is modified, database synchronization is required. In Dynamics AX, this typically occurs automatically. However, at times it does not and at times there may be a need to manually synchronize the data model from Dynamics AX tables, fields, base enums, and extended data types, with the database. For example, if extended data types, base enumerators, tables, or field information is changed in Dynamics AX, the SQL database tables must be updated to be in sync. This may occur when the licensing schema changes in an environment or a modification is made. It is recommended to run this tool often to ensure that the data model in Dynamics AX is in sync with SQL. The following steps describe this process:

1. To synchronize Dynamics AX against SQL tables, run the SQL administration tool in Administration | Periodic | SQL administration.

   
2. Click on the Table actions button and click on Check/Synchronize to check if the Dynamics AX data model is in sync with SQL tables.

   
3. Then, pay special attention to any errors and warnings as they can signify data inconsistency and possible data loss. Information messages are not a concern since they do not affect data integrity or structure. If errors or warnings do exist, they must be fixed. Typically, running the synchronization fixes these issues. Since synchronization may delete fields in SQL tables, data may also be lost. Therefore, before performing synchronization, back up the Dynamics AX database in SQL. Once the database is backed up, click on the Continue button to synchronize the database.

   

   In some instances, manual intervention may be required. For example, in common cases when tables need to be re-indexed, you would browse the tables in SQL Management Studio and delete the deprecated indexes. Once complete, run the synchronization utility again.
4. If you are successful, there will be no message. However, if the synchronization fails or encounters errors, an Infolog window will appear with the errors. This may require manual intervention such as going into SQL Server and modifying the data manually.

To export / import data from within AX, definition groups approach should be used.

If want to take the data backup then follow the below procedure and by means of this you can take the backup of the compnay data in which you currently working on

• Goto Administration module
• Under periodic
• expand Data export/import menu
• in that select  'Export to'
• give the location where you want to store the data

after this procedure the system will generate 2 files (DAT and DEF)

Import Data into Sql Server From Excel Spreadsheet

I have an Excel sheet now I need to insert data from that sheet into SQL Server 2008.

I need to create a temp table and insert data into that table. The database name is Employee
Can you pls provide me the syntax for achieving it.

A simple search: http://support.microsoft.com/kb/321686 Probably easiest is SELECT * INTO #tmptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [SheetName$])

 

 Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2008 to Excel & Excel to Server 2008

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
 @outputfile = 'd:\testing.xls', 
 @query = 'Select * from Database_name..SQLServerTable', 
 @colheaders =1, 
 @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
 @db_name varchar(100),
 @table_name varchar(100), 
 @file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
 @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
 information_schema.columns
where 
 table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Import Data into Sql Server From Excel Spreadsheet using SQLBulkCopy class

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using System.Data.Common;

using System.Data.SqlClient;

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            // Connection String to Excel Workbook

            string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"";IMEX=1;";

            // Create Connection to Excel Workbook

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

            {

                OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet

                using (DbDataReader dr = command.ExecuteReader())

                {

                    // SQL Server Connection String

                    string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

                    // Bulk Copy to SQL Server

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

                    {

                        bulkCopy.DestinationTableName = "ExcelData";

                        bulkCopy.WriteToServer(dr);

                    }

                }

            }

        }

    }

}

Executing .NET code in Dynamics AX 2009: Solving error “the selected file is not a valid .NET assembly”

To increase the functionality of AX is possible to execute .NET code.
In general terms the idea is create a .dll with the .NET code and after add the .dll as reference in AX. After that you’ll be able of execute the .NET code in AX.

Just follow these steps:

1. Create a new project (I’m going to use Visual Studio 2010):





Please be sure that you’ve selected the .NET Framework 3.5: if you select a newest one AX won’t work with your .dll:



2. Create the .NET code you want to execute:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

<p>using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms;   namespace MyDLL {     public class TestingMyDLL     {         public void showMessage()         {                           MessageBox.Show("Executing .NET code with AX!");         }     } }</p><p> </p>

3. Build the solution:



4. Locate the .dll generated under the Project folder:



And copy it in your AX testing environment. Take care with the path in which you copy your .dll: it has to be located in the “bin” directory of your AX installation (in my case C:\Program Files (x86)\Microsoft Dynamics AX\50\Client\Bin) otherwise AX will show you the following message:



And maybe you wont use the library:



5. Import the dll into AX. Open the AOT and do right click in the References node and click ‘Add reference’:







Press OK.

If you selected the .NET Framework 4 when creating the project in step 1 you’ll get this error trying to import the reference:The selected file is not a valid .NET assembly, therefore the refence cannot be added.



6. Create a new job/class in AX:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

static void testingNETCode(Args _args) {     MyDLL.TestingMyDLL          testingDll;     InteropPermission           permission;     ;       try     {         permission = new InteropPermission(InteropKind::DllInterop);         permission.assert();           testingDll = new MyDLL.TestingMyDLL();         testingDll.showMessage();     }     catch     {         error('Error executing DLL code');     } }

7. Run the job:

Dynamics AX 2009: Creating a simple SSRS Report

Dynamics AX 2009 features support for writing reports using SQL Server Reporting Services. In this post, I’m going to show you how to create a simple SSRS report that accesses data in the AX database.

The overall process I will describe will focus on the first stage of authoring and deploying the report from the developers perspective.

In future posts I’ll cover

• Formatting the report
• Adding menu items to launch the report
• Security
• Accessing OLAP data
• etc.

The basic steps

• Create an AX Query
• Create a new AX Reports Project and a new report in that project
• Create a dataset in the report bound to that query
• Create a design to report to render the dataset
• Preview the design
• Deploy the design to SSRS
• View the report via IE
• Save the Report project back into AX

The report I will create will be a simple unformatted table of customers and customer IDs.

Create an AX Query

Launch the Dynamics Client

Open the AOT

In the AOT, right click on Queries and select New Query

By default a new query will be create with a default name (in this case “Query1”)

Right-Click on that query and select Rename

Give it the name “DemoQueryCustomers” and hit RETURN

Expand that query in the AOT

Right-click on Data Sources and click New Data Source

A new data source will be created and by default attached to some table (in this case it will be called “Address_1”

If you hover over this datasource you will see the query string

Right click on that data source and select Properties

The property window for that data source will appear

Navigate to the Table property and change it to the “CustTable”

Click on the Name property

You’ll notice that the Name changes to “Cust_Table_1”

Close the property window

Click Save All in the AOT

Close the AOT

Create a new AX Reports Project and a new report in that project

Launch Visual Studio 2008

File > New > Project

The New Project dialog will launch

Under Project Types, select Visual C#  / Dynamics

Under Templates select Dynamics AX Reporting Project

Change the Name to “DemoReportsLibrary1”

Click OK

An new AX Report Library project is created

By default it contains a report called “Report1”

Right click on this report and rename it to “DemoReportCustomers”

Create a dataset in the report that is bound to the AX query

In the report, right click on Datasets and select new Dataset

By default the name of the Dataset is “Dataset1".

Rename it to “DataSetCustomers”

In the properties window for the Dataset

Verify that the Data Source is “Dynamics AX”

Verify that the Data Source Type is “Query”

Verify that the Default Layout is “Table”

Click on the Query field

Click on the ellipsis button in the value for the Query field

A dialog will launch that will list all the queries in AX. It will take a few seconds to populate this dialog with all the queries so be patient.

Under Select Query, choose the query you previously created “DemoQueryCustomers”

After you select that query the right column will be filled with fields from the query.

By default All will be selected.

Uncheck All

Check the AccountNum field

Check the City field

Check the Name field

Check the State field

Click OK to close the dialog

You’ll now see that ”DataSetCustomers” contains all the fields you selected.

Select “DataSetCustomers” and drag it into the “Designs” node

After you finish dragging, you’ll see that a design has been created. It will be given the name “AutoDesign1”

Preview the design

With AutoDesign1 selected, click Preview in the toolbar

You’ll notice a message at the top of the preview saying “The design has validation warnings” and you can see the warnings in the error list at the bottom.

We’ll ignore this for now.

Click the Save icon to save the report

Deploy the design to SSRS

Right click on the solution and select Deploy

At the status bar in the bottom you’ll see a message on the left and some animation on the right indicating that the deployment is in progress.

Eventually it will say “Deployment succeeded” in the status bar

View the report via IE

Launch IE and navigate to your SSRS reports server (in this example it is http://isotopex1:81/reports/)

Navigate into the Dynamics link

Find the report we deployed

It will be listed as “DemoReportsLibrary.DemoReportCustomers.AutoDesign1”

Click on it to view the report

And now you’ll see the report

Save the Report project back into AX

Close IE

In the solution, right click the report project (not the solution) and select Save to AOD

Open the Dynamics AX client

Open the AOT

In the AOT expand the Report Libraries node

you’ll see the report library “DemoReportsLibrary” is now in AX

if you need to edit the report library again just locate it in the AOT, right-click it and select Edit in Visual Studio