Saturday, June 28, 2014

Dynamics GP Community is under attack!

It seems our community is under spam attack! It is getting spammed by few LOVE Specialists! I would kindly ask for users who has outstanding questions to “repost” their questions once Microsoft folks resolves this attack!

I feel like CAPTCHA will be added soon to the community to avoid such spammers and also to annoy users!


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Analytical Accounting vs. Multidimensional Analysis

In earlier Dynamics GP versions, we used to use a module called MDA to manage our accounts dimensions and reduce the size of the chart of accounts, the module is very stable and works perfectly but with limited functionalities, when the Analytical Accounting was introduced, this module was retired and was no longer enhanced by Microsoft.

Personally, I been fighting with Analytical Accounting issues for many years now, I can count free hours spent to correct journals at the database level that unjustifiably corrupted and was not linked to the correct figures, which makes it a little bit annoying.

A couple of days back I seen a “conspiracy” being prepared between GP MVPs (including me!) against the Analytical Accounting pushing hard to get the MDA module back to track and enhanced to compete the AA module.

Frank has created the Connect request and we all voted to get this done, we need your help getting this ranked to the top of the list and to be considered in the very near release:

https://connect.microsoft.com/dynamicssuggestions/feedback/details/907907/bring-mda-back-to-dynamics-gp


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Thursday, June 5, 2014

Dynamics GP 2013 - Web Client Only Users & Active Directory Integration (LDAP)

This morning I been thinking about the new security structure for Dynamics GP web-client user who does not have SQL Users, and how these users will interact with SQL Server, I had to go through this as I have a client who’s running a very strict policy in granting SQL permissions for domain users.

Getting through this, I have created few users with multiple scenarios as below:

  1. The first user linked to domain account called that “TEST1”, but didn’t check “Web Client user only (no SQL Server Account)”
  2. The second user linked to domain account called that “TEST2”, and checked the “Web Client user only (no SQL Server Account)” option.

I have noticed that the first case created an “SQL User Account” called “TEST1” and didn’t granted the domain user any access to the database, and the second didn’t create any users which been a mystery to me understanding which user will be used to access the SQL Server!

To test this I have modified the GL00100 table and added a new column called “UserName” –this is for testing only, you cannot do this on GP tables!- and set the default value for this column to be “SUSER_SNAME()” function which returns the user who’s currently logged in and started my testing by creating new accounts!

Logged in by TEST1 to the web client and created a new account, the result was somehow wired, the user who was recorded was “DYNSA”

image

Logged in by TEST2 to the web client and created a new account, the result was the same, the user who was recorded was “DYNSA” as well.

Then logged in but selected “SQL Authentication” option from the login page:

image

The result was as expected, the logged in user was “TEST1”!

Now it been clear, all windows authentication users will be using one account to access the SQL, the login is the one you specified during the installation of the web client, so if you been counting on the user who is currently logged in in your customizations you might need to review your code!

Hope that helps giving you a good understanding to the new security process.


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP 2013 R2 upgrade: The following SQL statement produced an error: exec wfDeployClrAssemblies

During the upgrade process to Dynamics GP 2013 R2 with one of my customers we got the error in the subject, the issue is with dropping one of the SQL level Assemblies called “System.DirectoryServices” that been used by “Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine”.

The "System.DirectoryServices" assembly is being used by the Workflow module in Dynamics GP, if you have workflow installed you will not be able to drop this reference as you will be having another assembly depending on this and the other assembly is being used by 7 functions and stored procedures.

This assembly basically is used to connect your active directory and get the needed details that are being used by the system, dropping all these objects is safe as the upgrade process will automatically create them back for you.

To resolve the issue, first find the following functions and stored procedures, right click them one by one and hit drop:

image

Go to DYNAMICS database and navigate “Programmability>> Assemblies” and find “Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine”, right click the assembly and hit drop and then drop “System.DirectoryServices”:

image

Reinitiate Dynamics GP Utilities and and the upgrade will proceed safely, don’t worry about the deleted objects as the upgrade will recreate them all.

Hope that this helps.


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Update Currency Index field across all database

You might need to change the currency index for one of your currencies, I needed this during a consolidation project for multiple DYNAMICS databases and needed to change the currency index field for all company tables, take a look into the below script, I have used the “Information_Schema” to get all columns that are called “CURRNIDX” and updated these using SQL Cursor, considering that I taking a backup for each table before doing the operation:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CURRNIDX' AND COLUMN_DEFAULT IS NOT NULL
OPEN CURR
FETCH NEXT FROM CURR INTO @TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Statement = 'SELECT * INTO ' + @TABLENAME + '_BAK FROM ' + @TABLENAME
EXEC (@Statement)

SET @Statement = 'UPDATE ' + @TABLENAME + ' SET CURRNIDX = [NEW CURRENCY ID] WHERE CURRNIDX = [OLD CURRENCY ID]'
EXEC (@Statement)

FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR




Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Dynamics GP 2013 R2 – New User Maintenance

I liked the new active directory utilization for GP 2013 R2! take a look how does it look like now:

image


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Currency Symbol is incorrect for some GP forms

After updating the currency index field across all database, one of the users has reported that currency symbol does not match the functional currency symbol defined in the currency setup, and for some screens multiple currencies are shown as below:

image

After looking for this I have found this in MC40000 table, the Currency ID field represents the correct functional currency but the index was referring to another currency, updating the currency index field fixed the issue.


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Update Company ID field across all company database

A couple of weeks back I needed to consolidate two DYNAMICS databases and move companies from the first DYNAMICS to the second, this for sure requires Company IDs to be changed as it might be taken by another companies on the main DYNAMICS, to fix this once for all I have wrote the following script:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CMPANYID' AND COLUMN_DEFAULT IS NOT NULL
OPEN CURR
FETCH NEXT FROM CURR INTO @TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Statement = 'SELECT * INTO ' + @TABLENAME + '_BAK FROM ' + @TABLENAME
EXEC (@Statement)
SET @Statement = 'UPDATE ' + @TABLENAME + ' SET CMPANYID = [NEW COMPANY ID] WHERE CMPANYID = [OLD COMPANY ID]'
EXEC (@Statement)
FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR

 


Hope that this helps.





Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Consolidating multiple Dynamics GP Companies into single DYNAMICS database

Recently I had an interesting project with one of my customers, they had the following environment:

Server I:

  • Dynamics GP 9 RTM with 14 active companies.
  • SQL Server 2005 (Collation is Arabic_CI_AS).
  • Windows Server 2003 & FRx

Server II:

  • Dynamics GP 11 RTM with 3 active companies.
  • SQL Server 2008 R2 (Collation is Latin)
  • Windows Server 2008 & FRx

The customer simply wanted to upgrade to Dynamics GP 2013 SP2 and to SQL 2012, and also wanted to consolidate the databases into one single DYNAMIMCS database.

Challenges:

Now we have few challenges detailed below:

  1. Dynamics GP 2013 does not support upgrading from GP 9.0
  2. Dynamics GP 2013 does not support SQL 2005 and GP 9.0 does not support SQL 2012.
  3. Database collation differences between companies.
  4. Data stored in DYNAMICS database like currencies, users and BP objects.
  5. Changes that will occur on columns like “Company ID” and “Currency Index” which will be changed due to DYNAMICS data consolidation.
  6. Security migration from GP 9.0 to GP 2013.
  7. FRx migration to Management Reporter.
  8. Minimum downtime need to be maintained! 17 companies across multiple countries cannot stay down forever!

Resolutions:

Well we believe that nothing is impossible in the IT field, we have planned for everything and scheduled the upgrade process, below the steps we followed:

Server I:

  • Took a backup for all databases.
  • Upgraded from version (GP 9.00.0114) to 9.00.0371 that supports the upgrade to GP 2010 (we need GP2010 because it supports both SQL 2005 and SQL 2012!)
  • Upgrade from (GP 9.00.0114) to GP 2010 (GP 11.00.2320) that supports direct upgrade to GP 2013.
  • Moved upgraded databases from SQL 2005 to SQL 2012 with moving all users and configurations.
  • Upgraded to GP 2013 SP2.
  • Checked System stability.
  • Updated Users permissions and passwords.
  • Installed Dynamics GP 2013 with SP2 on all clients
  • Installed Management Reporter that replaces the FRx on the server and on the clients.
  • Migrated existing FRx Reports to Management Reporter
  • Activated SQL Reporting Services
  • Installed and activated Dynamics GP Web Client
  • Started the data entry!

This operation took the first weekend!

Server II:

  • Took a backup for all databases.
  • Upgraded from your current version GP to the service pack that supports the upgrade to GP 2013.
  • Upgraded to GP 2013 SP2.
  • Checked System stability
  • Created new companies on the new server
  • Restored databases from old server to PROCCOSQL over newly created databases
  • Update databases collation to match old server collation (I used the tool that updates the SQL database collation by dropping all functions, constraints and indexes that is collation dependence)
  • Updated CURRENCY ID in all databases columns to match the newly added currencies, it was indexed as “100X” and changed to “10XX”
  • Update Company ID in all databases columns to match the newly added companies.
  • Add newly created companies to Management Reporter
  • Activate SQL Reporting Services

This operation took the second weekend! Till now everything is working properly and this is how their GP looks like right now:

image


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

Related Posts:

Related Posts with Thumbnails