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

Enable the sa account or set back sysadmin permission on an account

issue: The domain account used to install sql server 2008 had full control of sql. However, for some reason that account cannot really do anything right now as the serverrole permissions.


solution:I researched and found out that i can change to mixed mode and log in using the sa account.

1) Log on to the server using the local admin account

2) Stop SQL Server Services & Moss Services (Purpose: to ensure that no
service is trying to connect once SQL Server is in Single user mode)

3) Set LoginMode to 2 in the Registry
a. Command Prompt type "regedit"
b. HKEY_Local_Machine\Software\Microsoft\Microsoft SQL
Server\MSSQL10.MSSQLSERVER
c. Click on MSSQL Server -->To the right click on Login mode
d. Change to 2 (To enable Mixed Mode Authentication in SQL)

4) (Command Prompt 1) Open Command Prompt (Right click and select
Run as Administrator) --> Navigate to the instance where the
Binn folder is located
a. D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
b. Type sqlservr.exe -m -f
c. Enter
(-m - single user mode)
(-f - minimal config mode)

NOTE: Have to see: SQL Server is now ready for client connect Recovery is complete

5) LEAVE WINDOW OPEN

6) (Command Prompt 2)Open a new command prompt(Right click and
select Run as Administrator) --> Navigate to the C prompt
a. Type osql -S servername -E
b. Alter login sa ENABLE
c. GO
d. Alter login sa WITH PASSWORD = 'installer'
e. GO

NOTE: Once there isan't an error it will go to 1> (this enables you to enter a new query)

f. Type EXIT
g. Close Window
(osql same as sqlcmd)
(-S to present the server name)
(-E Trusted Windows Connection)

7) Open Management Studio
a. Select SQl Server Authentication
b. Login sa
c. Enter the password that was set 'installer'
d. Connect
e. Expand Security --> Expand Logins
f. Right click properties for an account --> Server Roles Select sysadmin

8) Close Command Prompt 1

9) Log off server as local admin; Log in as the changed account

10) Start back SQL Services & Moss Services

11) From Management Studio, Disable sa account
a. Expand Security --> Expand Logins
b. Right click sa --> Properties
c. Select Status --> Login Disabled

12) Change back Log In Mode to 1 in the registry
a. Command Prompt type 'regedit'
b. HKEY_Local_Machine --> Software --> Microsoft --> Microsoft SQL Server
--> MSSQL10.MSSQLSERVER
c. Click on MSSQL Server --> To the right click on Login mode
d. Change to 1

Problem during Com connector startup

Exact Error Message
"Server process could not be started because the configured identity is incorrect. Check the username and password"

Details:
The error, as displayed in the Figure above, can occur if an incorrect username and/or password have been assigned to the Navision Axapta Business Connector

To resolve this issue, follow the steps below:
1. Click Start > Run, input dcomcnfg in the Open: box and click OK

2. For Windows 2003 and Windows XP
Component Services will open. Expand Component Services > Computers > My Computer > COM+ application

3. Right click AxaptaCOMConnector.Axapta.1 or AxaptaCOMConnector.Axapta2.1 and select Properties

4. select the Identity tab, place the bullet in This user: (if not already), input the correct user name and password. If the correct username already appears, erase what is currently displayed and use the Browse: button to reselect the correct user, and then input the correct password in the Password and Confirm password fields. Finally click Apply and OK

How to compare Date type field with Datetime type field?

The validFromDatetime field from the HRPPartyPositionTableRelationship is an Datetime type field and the projTransDate field from the projJournalTable an date type field in the example below.

// Example begin
Timezone timezone = DateTimeUtil::getUserPreferredTimeZone();
;

while select positionTable
where positionTable.HRMReferenceType == 0
&& positionTable.Reference == EmplTable.EmplId
&& positionTable.ValidFromDateTime >= datetobeginUtcDateTime(projJournalTable.ProjTransDate, timezone)
&& positionTable.ValidToDateTime <= datetobeginUtcDateTime(projJournalTable.ProjTransDate, timezone)
{
error("error message");
return;
}
// Example end

Search table id in AX 2009

The following job prints a list with all the table id's and there name in AX. Handy when you have for example a record in a table with a RefTableId.

static void findTables(Args _args)
{
Dictionary dictionary;
TableId tableId;
tableName tableName;
;

dictionary = new Dictionary();

tableId = dictionary.tableNext(0);
tableName = dictionary.tableName(tableId);

while (tableId)
{
info(strfmt("%1 - %2",int2str(tableId), tableName));

tableId = dictionary.tableNext(tableId);
tableName = dictionary.tableName(tableId);
}
}

Command-line parameter ... is invalid. Check spelling and start Microsoft Axapta again.

The following error message appeared to a client of me yesterday.

Situation problem:
Axapta is installed on a Server and the client accesses the axapta icons on a shared directory. There was only one user that cannot open axapta. For all the other users was axapta still available.

Solution:
The problem was a security issue linked to the user. The user logged on automatically to the directory with the wrong user name and password. So you have to changes the stored user names and passwords on the computer.

How to Manage Stored User Names and Passwords
To manage stored user names and passwords, follow these steps:

1. Log on to the computer as the user whose account you want to change.
2. Click Start, and then click Control Panel.
3. In Control Panel, click User Accounts under Pick a category to open the User
Accounts dialog box.
4. Open the Stored User Names and Passwords dialog box; to do so, use the
appropriate method:
* If you log on with an account with administrative privileges:
1. Under or pick an account to change, click your user account to
open the What do you want to change about your account? dialog box.
2. Under Related Tasks, click the Manage my network passwords.

Remove an Entry
To remove a credential:

1. In the Stored User Names and Passwords dialog box, click the credential that you want, and then click Remove. The following message is displayed:
The selected logon information will be deleted.
2. Click OK.
3. In the Stored User Names and Passwords dialog box, click Close.

New AX Book: Managing Your Supply Chain using Microsoft Dynamics AX 2009

My and other's review:

Scott’s books are a must read for any team working to maximize their return on their Microsoft Dynamics investment.

Book Description:

Managing Your Supply Chain addresses the need for an overall understanding about using Microsoft Dynamics AX to manage manufacturing and distribution businesses. The targeted reader includes those individuals implementing or considering Dynamics AX as their ERP system as well as those providing sales and implementation services. Firms involved with a system selection process may be considering Dynamics AX as a candidate package, and this book can help reduce selection risks, evaluate system fit and needed customizations, and provide a vision of an integrated system. The book can help businesses involved in implementing and using Dynamics AX by accelerating the learning process, reducing implementation time and costs, and reducing user resistance to change. It suggests changes that can improve system usage and revitalize a wayward implementation. For firms providing sales and services related to Dynamics AX, this book can accelerate the employee learning process for providing knowledgeable customer service in sales, support, and professional assistance.

You can buy the book on amazon:
http://www.amazon.com/Managing-Supply-Chain-Microsoft-Dynamics/dp/0979255228

Microsoft Dynamics AX 2009 Service Pack 1

In addition to a number of optimizations and Hot Fixes, Microsoft Dynamics AX 2009 Service Pack 1 offers partners and customers a number of enhancements including Electronic Signature, Right-to-Left Date Support, and support for new GLS layers, Windows Essential Business Server 2008 add-in, Microsoft SQL Server 2008 and Microsoft Dynamics Mobile Platform.

Service Pack 1 is a prerequisite for the installation and/or support of Lean Manufacturing and the Environmental Sustainability Dashboard.

Project Time Management

Project Time Management delivers time entry and approval functionality for project and professional service organizations. The flexible time periods and workflow-based approval engine enable organizations to tailor time entry and approval to their business needs. Delivered via the Microsoft Dynamics AX client or in Enterprise Portal, organizations will be able to roll out time entry to all project participants in the organization. Accurate and timely recording of hours for projects will give project and practice managers up-to-date visibility on project status and profitability. Moreover, it will be possible to invoice customers faster, which can have a positive impact on the organization‘s cash flow. Availability is planned for Q1 CY2009.

Intelligent Data Management Tool
The Intelligent Data Management Tool will help organizations to optimize the database and reduce data storage costs. The first release of this tool offers organizations the ability to analyze data storage on tables and indexes. The tool also helps removing redundant, historical data. This release will only be available in US-English, and is planned for release in Q1 CY2009. The major enhancement of the second release of this tool, planned in Q3 CY2009, is the ability to archive redundant, historical data.

Lean Manufacturing

Lean Manufacturing in Microsoft Dynamics AX empowers people in manufacturing organizations to streamline and integrate processes, minimize waste, optimize the supply chain, and manage demand-driven operations. Robust lean manufacturing functionality, delivered as an integral part of a flexible, security-enhanced, and familiar end-to-end business management solution, builds on the solution‘s collaboration tools and access to real-time business insight to facilitate value stream mapping, process integration, informed decisions, and continuous improvement. As a result, manufacturing organizations can help to effectively implement, energize, and sustain lean manufacturing strategies to reduce time-to-market, increase profitability, and gain a competitive advantage. The plan for the Lean Manufacturing module is to include elements such as:
- Value stream
- Cell concept
- Lean order schedules
- Heijunka board
- Kanban
- Manufacturing without production order
- Kanban board
- Kanban inventory overview

Lean Manufacturing will be available in US-English only. The module is planned for release in Q1 CY2009.

Environmental Sustainability Dashboard

Companies are experiencing increasing pressure from all angles to adopt environmental sustainability initiatives within their businesses. Increasing regulations, threat of exposure by NGOs, supply chain reporting, increasing risk, employee retention and brand protection are just some of the issues companies face when it comes to managing the environmental impact they have. Indeed, a company should be able to view every decision it faces in terms of its environmental impact, as well as in terms of the financial repercussions of that decision. In order to enable this, environmental information must permeate the ERP system.

Experts agree that an important first step companies must take to prepare for the market transition brought on by climate change is to determine their carbon footprint. Tracking their energy consumption and greenhouse gas (GHG) emissions will enable them to understand their level of exposure as we move toward a seemingly inevitable carbon market. The Environmental Sustainability Dashboard for Microsoft Dynamics AX 2009 can facilitate the data collection required to bring a level of consistency to their GHG/carbon footprint tracking process.

The dashboard will include the four core environmental performance indicators as defined by the Global Reporting Initiative (GRI), which is the standard in environmental reporting. The Environmental Sustainability dashboard is planned for availability in Q1 CY 2009 in over 10 languages.

More information about SP1:

https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID={0F3196F4-3ABB-4402-B43F-3F0524BE37A4}&NRORIGINALURL=/partnersource/support/selfsupport/servicepacks/DynamicsAX2009sp1&NRCACHEHINT=Guest&wa=wsignin1.0

Business Benefits and Key Features of Microsoft Dynamics AX 2009

The following section gives a high level overview of the business benefits that Microsoft Dynamics AX 2009 offers organizations. More detailed documents describing the enhancements as well as training documents and white papers are available on PartnerSource and CustomerSource.

Increase Productivity
Microsoft Dynamics AX 2009 helps people be more productive and manage work effectively by providing familiar, easy-to-use tools that help them work faster and make decisions with confidence. Key Components that have been added in this release are:
- More than 30 Role Centers which have intuitive, graphical displays of the information and activities most relevant to specific job roles. People can quickly prioritize work by viewing relevant tasks and information in their Role Center, a single location that provides a unified work list, visual task queues, access to business processes and reports, business notifications, key performance indicators (KPIs), and other important information. Moreover, these Role Centers help people understand the context of business processes and keep critical tasks, projects, and orders on track with notifications and alerts initiated by automated workflows.
- Microsoft Dynamics AX takes advantage of research for other Microsoft products, such as Windows Vista® and the 2007 Microsoft Office system, to enhance usability while providing richer functionality and introduces the familiar concepts like the address bar with breadcrumb path, travel buttons, list pages, advanced filter capabilities, visualizations and more.
- Help people communicate and collaborate more easily through integration with Unified Communications and Microsoft SharePoint Technologies.
- Enable self-service BI by providing data cubes that can be displayed as KPIs in employees‘ Role Centers, and viewed with more detail in Excel. Automatically generate custom reports by using Microsoft SQL Server® Report Builder, an ad hoc query tool and component of Microsoft SQL Server Reporting Services.

Manage Change & Growth
Microsoft Dynamics AX helps to build profitable supply chain relationships and quickly adapt internal processes to meet changing demands and helps support growing companies to meet the extended requirements.

Key components of this release include the following capabilities to help you:
- Scale your solution as your business grows. Three-tier architecture and integration with other Microsoft technology, such as Microsoft SQL Server 2008, give you the ability to easily add users and sites (more in the section below).
- Establish and automate supply chain collaboration by exchanging business documents via the enhanced Application Integration Framework (AIF).
o Use security-enhanced virtual networks, predefined industry templates, and graphical mapping tools by using Microsoft BizTalk® Server 2006 R2 and the BizTalk Adapter for business-to-business (B2B) scenarios.
o Create Web services to exchange information with suppliers. Web services can expose Microsoft Dynamics AX data or consume data from other systems.
o Exchange business documents easily with over 50 preconfigured documents.
- Use Windows Workflow Foundation to create business rules and customer workflows—or use templates—to automate business processes.

Compete Globally
More and more organizations and companies must face the challenges of being a part of a global economy. Whether it is expanding into new markets with sales and manufacturing sites, sourcing from vendors in other countries to reduce costs or facing new competitors from other countries in your home market, companies are feeling the pressures. This release addresses the challenges of competing in a global market by providing functionality to help businesses standardize and harmonize business process across an international organization and also helps to reduce cost by automating business processes. Enhancements in this version are:
- Promote the adoption of best practices by centralizing processes with support for shared services.
- Streamline procurement processes with purchase requisitions and requests for quotations (RFQs).
- Share quality management processes across the organization to help ensure products are manufactured to standard across a distributed system.
- Improve flexibility and handle more complex supply chain demands across your multi-site organization with the ability to use the site dimensions within a company.
- Consolidate financial information across multiple sites and international locations while maintaining local financial information relevant to each location.
- Provide integrated, Web-based expense management and entry, and standardize corporate best practices and processes across your global organization.
- New time zone support helps you to run more accurate operations across your international organization.
- Improve visibility across your global organization through the combination of Microsoft Dynamics AX with powerful Microsoft business intelligence applications built on the Microsoft SQL Server 2008 platform for more complex views of business data.
- Model, evaluate, and contribute to advanced planning and reporting scenarios across the company, such as consolidated budgeting and planning through integration with Microsoft PerformancePoint Server.
- Centralize deployment to improve visibility and streamline processes across the organization through the harmonization of country-specific layers

Simplify Compliance
Help reduce risk and liability associated with corporate governance, regulatory compliance, and customer initiatives.
- Improve your ability to manage human resource requirements by complying
U.S. employment regulations, including the Americans with Disabilities
I-9 regulations.
- Support green supply chain strategies with an environmental management accounting template that helps businesses facilitate effective waste
manufacturing process and environmentally efficient logistics systems.
- Take advantage of new supply chain opportunities by supporting customer
such as an RFID-enabled supply chain.
- Help ensure transparency into corporate governance and compliance
the Compliance Center, a single location for policies and procedures,
documents, database logs, reports, charts, and key risk indicators.
- Easily describe business processes with the built-in Task Recorder.

What is Microsoft Dynamics AX?

Microsoft Dynamics AX is a comprehensive business management solution for mid-sized and larger companies and organizations. We recognize that Microsoft Dynamics AX may fit well in customer organizations outside of this definition and we acknowledge that the mid-sized market segment is defined differently from country to country and from geography to geography. Additionally, Microsoft Dynamics AX can accommodate larger ―hub-and-spoke‖ organizations made up of multiple subsidiaries or divisions where particular subsidiaries or divisions have similar characteristics to mid-sized segment companies. Microsoft Dynamics AX helps organizations manage business processes in domains such as Finance, Supply Chain Management, Production, Project Management and Accounting, Sales and Marketing, Human Resources Management, Service Management, Compliance, Business Intelligence (BI), and Collaboration. Microsoft Dynamics AX is built to make it easy to do business across multiple locations and countries by consolidating and standardizing processes, providing visibility into processes, and help in simplifying compliance.