How to export/import from SysLastValue table

Recently, I was troubleshooting an issue related to user settings. i.e. There was a requirement to migrate the user settings from one environment to another.

As we all know, User related setups for queries, forms, reports etc are stored in SysLastValue table. This is a system table and can't be accessed directly through AOT.

Solution:

NOTE: PLEASE BACK UP YOUR SYSLASTVALUE TABLE BEFORE YOU ATTEMPT THE BELOW STEPS AND TRY TO VALIDATE THE STEPS FIRST IN A TEST/SANDBOX ENVIRONMENT

1. Created a definition Group for SysLastValue table

2. Filter only for SysLastValue table

3. Set up the filter criteria For export in our case we have to do for one user

4. Note that at this point when we do the export, the .dat and .def file which is generated won't be having any data in it. This is because by default the System tables are not included in the Standard Export/Import Wizard of AX.

We need to do a small code change inorder to achieve this, Go to AOT > Classes > SysDataExpImp

5. Modify the candoTable method inorder to comment out the code which does the system table check for SysLastValue

6. Now, once again do the export and there you go..

Initially, I tried to do the SQL way, but figured out that it doesn't work and moreover SQL is not the correct way as we can miss out on Recids.

SQL Script: Remove duplicates from table

USE [AXDBSP4]
GO
/****** Object: Table [dbo].[CUSTINVOICESALESLINKDUP] Script Date: 10/06/2009 10:45:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****** Create a new table where you copy/paste the founded duplicates ********/
CREATE TABLE [dbo].[CUSTINVOICESALESLINKDUP](
[INVOICEID] [varchar](20) NOT NULL DEFAULT (''),
[INVOICEDATE] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
[SALESID] [varchar](20) NOT NULL DEFAULT (''),
[ORIGSALESID] [varchar](20) NOT NULL DEFAULT (''),
[INVOICEACCOUNT] [varchar](10) NOT NULL DEFAULT (''),
[ORDERACCOUNT] [varchar](10) NOT NULL DEFAULT (''),
[DELIVERYNAME] [varchar](30) NOT NULL DEFAULT (''),
[DELIVERYADDRESS] [varchar](250) NOT NULL DEFAULT (''),
[PARMID] [varchar](20) NOT NULL DEFAULT (''),
[INVOICINGADDRESS] [varchar](250) NOT NULL DEFAULT (''),
[INVOICINGNAME] [varchar](30) NOT NULL DEFAULT (''),
[PURCHASEORDER] [varchar](20) NOT NULL DEFAULT (''),
[CUSTOMERREF] [varchar](30) NOT NULL DEFAULT (''),
[DATAAREAID] [varchar](3) NOT NULL DEFAULT ('dat'),
[RECID] [int] NOT NULL,
[RECVERSION] [int] NULL
) ON [PRIMARY]

GO
/****** Create an index where you ignore duplicate keys *********/
Create unique index removeduplicates on [dbo].[CUSTINVOICESALESLINKDUP] (INVOICEID, INVOICEDATE, ORIGSALESID) with IGNORE_DUP_KEY
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CUSTINVOICESALESLINKDUP] WITH CHECK ADD CHECK (([RECID] <> 0))
GO
/****** Insert the table with the duplicates into the new table because off the created index the duplicates will not be copied********/
insert [dbo].[CUSTINVOICESALESLINKDUP] select * from [dbo].[CUSTINVOICESALESLINK]

SQL Script: Found duplicates

SELECT [INVOICEID]
,[INVOICEDATE]
,[ORIGSALESID]
,[DATAAREAID]
,count ([DATAAREAID])
FROM [AXDBSP4].[dbo].[CUSTINVOICESALESLINK]
GROUP BY INVOICEID, INVOICEDATE, ORIGSALESID, DATAAREAID
HAVING (COUNT(DATAAREAID) > 1)
order by 1

Axapta Version - Overview

SQLSYSTEMVARIABLES

When restoring an Axapta 3.0 database from a different server, you might have experienced the following error message:

[Microsoft][ODBC Database Server Driver][SQL Server] Invalid object name ‘SQLSYSTEMVARIABLES’.

The problem is your database user (default bmssa) can not access the SQLSYSTEMVARIABLES table. If the original and new SQL server use the same user id, the following query analyzer command should fix it:

exec sp_change_users_login 'update_one','bmssa','bmssa'

If that doesn’t help or user ids are different, try changing the database and object owners.

exec sp_changedbowner 'bmssa'

changes the owner of the database. I have used this script to change the owner of tables:

DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)



SELECT

@oldOwner = 'AX30'

, @newOwner = 'bmssa'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @oldOwner + '''

)

EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''



EXECUTE sp_MSforeachtable @sql


And Another one for the views:

DECLARE @oldOwner sysname, @newOwner sysname, @sql varchar(1000)



SELECT

@oldOwner = 'AX30'

, @newOwner = 'bmssa'

, @sql = '

IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.VIEWS

WHERE

QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

AND TABLE_SCHEMA = ''' + @oldOwner + '''

)

EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''



EXECUTE sp_MSforeachtable @sql

Dispatcher initialisation error trapping exceptions (GridEX20.ocx)

Error during full compile of an axapta 3.0 environment on Windows 2003 Server SP1 and SQL 2005.



solution:
Go to http://www.janusys.com/janus/beta/downloads_patches.htm and download: Janus GridEX 2000b - For DAO 3.6 & ADO 2.x (GridEX20.ocx version 2.0.0.2215). Unzip the file and copy gridex20.ocx into your clients bin-directory. That shoud fix it.

The error occours because the Data Execution Prevention system in XP SP2 and
Windows 2003 Server SP1 is somehow incompatible with the gridex20.ocx that
ships with the Axapta client.

Do you search a PC Shop?

For Computer Sales & Services come to the Ziva shop.

Ziva is specialized in:

* New PCs, notebooks and related components
* PC Repairs and upgrades
* Solving your Software problems
* Software upgrades
* Network installation and configuration

Address:

Ziva - http://www.ziva.be - shop@ziva.be
Veldkant 31a
2550 Kontich
Belgium
phone: 03/451.24.80
fax: 03/450.80.39