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.