Monday, November 29, 2010

Microsoft Dynamics GP - More than just Program Files!

In recent days, I have been resting from a long standing project in beautiful Las Vegas, Nevada. Months of traveling every two weeks back and forth have certainly paid off with my customer going live with their newly reimplemented systems. As a Delta regular, working at 35,000 feet during commute time has become more than just a laptop maneuvering exercise and has driven me to explore other obscure features of my beloved Microsoft Dynamics GP, all on my way to the client.

One such thing I decided to explore is, how much of a footprint does a Microsoft Dynamics GP (versions 10 and 2010) installation really have? This curiousity took a different dimension after reading a recent article by fellow blogger Jen Kuntz on Uninstalling Microsoft Dynamics GP 2010 the hardway. In this article, Jen outlines a number of locations, both logical and physical, where she had to search for references to Microsoft Dynamics GP 2010 in order to be able to reinstall the application. She based some of her findings on a previous article I had also written, How to manually uninstall Microsoft Dynamics GP 10.0, but quickly learned that the task at hand won't be that simple.

So, let's get the obvious out of the way: we already know about the %ProgramFiles%\Microsoft Dynamics\GP folder and a the obvious HKLM\SOFTWARE application registry entry, but what about the *rest*? What rest you may ask... It turns out, Microsoft Dynamics GP has its fair share of "things it keeps track of", so here they go:

Folders

1. %ProgramFiles%\Common Files\Microsoft Dynamics GP\{Component GUID}\ - At first glance, this folder stores a copy of all chunk files pertaining to the installation, for both installed and uninstalled features. The existence of this folder would suggest that Microsoft Dynamics GP, and concretely Dynamics GP Utilities uses these chunk files to rebuild the existing dictionaries at their latest service pack.

2. %ProgramFiles%\Common Files\microsoft shared\Dexterity\ - Stores the Dexterity Shared Components, which also include the GPConn.dll COM component and GPConnNet.dll .NET assembly. The latter two DLLs allow external applications to connect to Microsoft Dynamics GP using the credentials of the user currently signed on to the system.

3. %Windir%\Assembly\ - This folder is also known as the Global Assembly Cache (GAC) and there are a number of eConnect and eConnect Serialization assembly references. The GPConnNet.dll assembly also has a reference in the GAC.

4. %Temp%\ - This folder corresponds to the user temporary folder. The temp folder now stores the XML layout of the homepage and all navigation bars. These XML files are created on demand, each time an option is selected. In addition, the temp directory stores any local C-tree file for temp tables used in report processing. See An open operation on table '[TEMP Table]' errors over at Developing for Dynamics GP for more information on C-tree temp tables

5. %AppData%\Microsoft Business Solutions\Microsoft Dynamics GP\ - This folder stores the application's autocomplete information - See AutoComplete Data for GP – it’s not really a Mystery by MVP Leslie Vail.

6. %Windir%\Installer\{Component GUID}\ - This folder stores the installer information used by Microsoft Dynamics GP to change or remove the installation.

Registry Entries

These are only a few locations outside of the actual %ProgramFiles%\Microsoft Dynamics\GP folder where related information can be found. But what about the Windows Registry?

1. HKEY_CLASSES_ROOT\Installer\Products\{Component GUID} 

2. HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\{Component GUID}

3. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Business Solutions\Great Plains\

4. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\Folders\

5. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\{Component GUID}

Note: There are a number of other Components that reference the Microsoft Dynamics GP component GUID. These don't necessarily need to be removed from the Registry, but I won't be surprised if a decent Registry cleaning application picks up on them.

6. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{Component GUID}

7. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{Component GUID}_Ex

8. HKEY_USERS\S-1-5-21-3533039690-2615561654-1627596621-1001\Software\Microsoft\Windows\CurrentVersion\Explorer\TypedPaths

Note: The component GUID was located in the url1 and url2 attributes of this key.

If you ever need to uninstall Microsoft Dynamics GP 2010 or 10.0 manually, having the above list of entries may just save your life. If you know of any other obscure folders or Registry entries, please feel free to post a comment to this article and tell me how you came across your finding.

Disclaimer: Modifying the Windows Registry can cause serious damage to your computer or render it unusable, if not done properly. Please consult with a professional before making any changes to your environment.

The above entries may vary due to operating systems configuration or platform. Please take this into account before attempting any changes to your environment.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, November 26, 2010

Add-In Initialization Error when launching Microsoft Dynamics GP 2010

Lately, many users have reported getting the error depicted below after launching Microsoft Dynamics GP 10 with Service Pack 5 or Microsoft Dynamics GP 2010 with Service Pack 1:



Add-In Initialization Error
The error references the Microsoft.Dynamics.GP.OnlineServices.dll assembly file as the root cause of the problem. This assembly was shipped with Microsoft Dynamics GP 10 SP5 and is a part of the Dynamics Online Services application.

The error typically indicates that the add-in assembly is loaded, however, it most likely indicates that the Dexterity portion of the application, dictionary DO6499.dic, isn't loaded in the path found in the DYNAMICS.SET file -- which should have also generated a dictionary load error prior to receiving the above message -- or isn't loaded at all.

To fix this error:

1. Run a repair or update on the Microsoft Dynamics GP client under the Windows Control Panel. This will reinstall the Dynamics Online Services application dictionary and the assembly.

NOTE: You will not see this application in a list of selections as it appears to be a "default" application and it will reinstall itself.


However if you not interested in repairing the application and rather completely get rid of the Dynamics Online Services feature, then follow these steps:

1. Under the Microsoft Dynamics\GP folder, locate and delete the DO6499.dic dictionary file.

2. Remove all references of this product from the application launch file, DYNAMICS.SET.

6499
Dynamics Online Services
:C:Program Files/Microsoft Dynamics/GP/DO6499.DIC
:C:Program Files/Microsoft Dynamics/GP/Data/DO6499F.DIC
:C:Program Files/Microsoft Dynamics/GP/Data/DO6499R.DIC

NOTE: Don't forget to decrease the number of products by 1 in the DYNAMICS.SET file. Usually, this is the first entry in the launch file.

3. Locate the AddIns folder and delete the Microsoft.Dynamics.GP.OnlineServices.dll assembly file
4. Also remove the Application.DynamicsOnlineServices.dll from the GP installation folder

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, November 25, 2010

Happy Thanksgiving!

It's this time of the year again... I am personally thankful for your readership and all the feedback I have received from you throughout the year from a good number of you following this blog. Now, go eat some turkey!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Tuesday, November 23, 2010

Disabling Multiple Ledgers functionality in Microsoft Dynamics GP 2010...after the fact

Let's face it! Like many things in life, configuration decisions are revisited even after going live (rightfully so!) with your system. What was viewed and considered a requirement a few months aback and worked during User Acceptance Testing turns out to be something the business no longer needs today, due to changes in direction, or changes in business conditions.

Just recently, I came across a request for disabling the new reporting ledgers functionality in Microsoft Dynamics GP 2010. While this implementation was not live, this issue was clearly affecting the consulting teams ability to move forward.

The following script should disable the reporting ledgers function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

UPDATE GL40000 SET Allow_Reporting_Ledgers = 0, UseLedgersForAcctBalance = 0;
DELETE FROM GL40001;

Once the script is executed, go back to the General Ledger Setup window. You will notice that a BASE ledger is created by default, but also notice that the Allow flag is unchecked.


General Ledger Setup
Click the Ok button to continue.

Now, if you open the GL Transaction Entry screen, you will notice that the Ledger ID field is no longer present.

Transaction Entry
Hope you found this post useful.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Sunday, November 21, 2010

Granting Microsoft Dynamics GP user minimal access at the database level to setup additional users

After the long title of this post, you probably already have the idea of what the article will be about. However, back in April of 2009, I wrote about the POWERUSER role and the Microsoft SQL Server sysadmin server role - see Microsoft Dynamics GP 10 POWERUSER role vs Microsoft SQL Server sysadmin role - and explained the key differences between the two. Among other things, I discussed how a GP user login that's assigned to the sysadmin server role on Microsoft SQL Server becomes able to setup new users in GP.

However, those of you who are database administrators have been quite reluctant to add logins to the sysadmin group, and quite understandably so. After all, logins added to the sysadmin server role can do anything on the database server, and we sure don't want that to happen either.

In response to this, and to the many requests lately on the forums, my friend Robert Cavill, with Emeco Group in Australia, has submitted the following script, which gives a specific user ID in Microsoft Dynamics GP, minimal but sufficient permissions at the Microsoft SQL Server level to create new users. In addition, this script allows Robert's first level support staff with access to Microsoft Dynamics GP, the ability to reset passwords for their user base.

GrantUserRights.sql
--Created by Robert Cavill
--This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

DECLARE @sUSERID NVARCHAR(15)
SET @sUSERID = 'LESSONUSER1'

SET NOCOUNT ON;
SELECT 'EXEC master..sp_addsrvrolemember @loginame = ''' + @sUSERID + ''', @rolename = N''securityadmin'';'

SELECT
'USE [DYNAMICS];
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'

SELECT 
'USE [' + RTRIM(INTERID ) + '];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ' + QUOTENAME(@sUSERID, CHAR(39)) + ') 
  CREATE USER '+ QUOTENAME(@sUSERID) + ' FOR LOGIN ' + QUOTENAME( @sUSERID ) + '; 
EXEC sp_addrolemember N''db_accessadmin'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''db_securityadmin'',' + QUOTENAME(@sUSERID, CHAR(39)) + ';
EXEC sp_addrolemember N''DYNGRP'', ' + QUOTENAME(@sUSERID, CHAR(39)) + ';
GO'
FROM DYNAMICS..SY01500

When this script is executed against the DYNAMICS database for a specified Microsoft Dynamics GP user (@sUSERID variable), the result is another script granting the correct access to all the Microsoft Dynamics GP company databases.

Result
EXEC master..sp_addsrvrolemember @loginame = 'LESSONUSER1', @rolename = N'securityadmin';

USE [DYNAMICS];
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin

USE [TWO];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'LESSONUSER1') 
  CREATE USER [LESSONUSER1] FOR LOGIN [LESSONUSER1]; 
EXEC sp_addrolemember N'db_accessadmin', 'LESSONUSER1';
EXEC sp_addrolemember N'db_securityadmin','LESSON

Upon running the result script, the new database permissions will enable the Save button on the User Maintenance window, and allow users to be assigned to companies in the User Access window.

Here are a few additional tips:
  1. With this approach, the Microsoft Dynamics GP user is not a member of the sysadmin fixed server role.
  2. The user ID must already exist in Microsoft Dynamics GP with access to at least one company so they can log on.
  3. If, after executing this script, you attempt to delete the user ID from GP, it will fail.
In the following post, I will publish the script that will reverse the outcome to allow deletion of the user ID from Microsoft Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, November 18, 2010

And speaking about Conferences...

It turns out, this is the time of the year when I start looking forward to next years events I would like to attend... as a speaker! It's my honor and priviledge to introduce two of the events I will be speaking at:


Microsoft Dynamics GP Technical Conference 2011 will be held in Fargo, North Dakota, USA on the 1st - 3rd March 2011.











Microsoft Dynamics Convergence 2011 Atlanta will be held in Atlanta, Georgia, USA on the 10th - 13th April 2011.

You will also be pleased to know that I will be presenting with my dear friend, Microsoft's David Musgrave, who has been cleared for both presentations. So, please, please, please help us with session ideas for the conference by emailing your suggestions.

Also, please feel free to post comments on this article AND also submit your thoughts to the Convergence NA 2011 Call for Topics site before it closes on the 20-Nov-2010. Kevin Machayya from the Microsoft Dynamics Partner Community Blog explains more in his post: Help Identify the Hot Topics for Convergence 2011.

If you are excited about Microsoft Dynamics GP development or would like to learn more about the Support Debugging Tool, or if you have ideas for new topics you would like us to cover, please don't hesitate to contact us.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Wednesday, November 17, 2010

Dex - Why do memory tables seem to be slower in Dexterity 11.0?

As more of you begin to migrate your Dexterity 8.0 and Dexterity 9.0 customizations to be compatible with Microsoft Dynamics GP versions 10.0 and 2010, you may have noticed some performance decrease in your applications if using memory tables.

The reason for this decrease in performance? There was a change to how memory tables were implemented since Dexterity 10.0.

Originally, memory tables used the Ctree data structure but were created in memory. However, they needed large contigious blocks of memory and as more developers used them and data stored in them grew, Runtime Engine crashes were more noticeable when memory tables failed due to running out of usuable memory.

See KB article 898993 - You receive an "Open operation on table mytable has used a bad file name" error message when you try to access a memory table in a program that you create by using Great Plains Dexterity.

The change was to implement memory tables as local Ctree tables in the user's temp folder. Each session/instance of the Runtime Engine will create a temporary (TNT****) folder and in that folder you will see the *.dat and *.idx files for the respective memory tables.

This means that performance of memory tables will have dropped as the file system and hard drives are now involved. But application stability has been restored.

Thanks to Microsoft's David Musgrave for the detailed explanation.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Friday, November 12, 2010

How to change your Microsoft Dynamics GP 2010 Map Services

In recent weeks, the newsgroups have been flooded with questions about the Bing Maps service not working at the street level for addresses in Microsoft Dynamics GP 2010 - see Microsoft Dynamics GP 2010 map buttons not drilling down to the street level. While this issue is slated to be fixed in Microsoft Dynamics GP 2010 Service Pack 2, the bottom line is, it has left a bit of a bad taste in users relying on this functionality.

In addition, some users have questioned whether they can use a map service of their choice. There are a few well known services out there, for example Yahoo! Maps, Google Maps, or even some region specific services like Australia's WhereIs.com.

The good news is the Microsoft Dynamics GP community is full of individuals who are willing to share their knowledge without any restrictions and my buddy Jon Eastman at Touchstone Group, a Microsoft partner in the United Kindom offers this Dexterity based solution.

The Dexterity solution implements an AFTER trigger on the FormatWebAddress() function of the of the syMapPoint form. The processing function, GenerateGoogleMapsURL(), overrides the formatted address returned by the FormatWebAddress() function based on the address parameters. The Google Maps URL is then masked with the correct parameters and returned by the function for Microsoft Dynamics GP call to the browser application.

GenerateGoogleMapsURL()
{ function GenerateGoogleMapsURL

  Created by Jon Eastman, Touchstone Group
  This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
function returns string  sWebAddress;

in  string  sAddress;
in  string  sCity;
in  string  sState;
in  string  sZip;
in  string  sCountry;

sWebAddress = "http:\\maps.google.com\maps?q=" + FormatSegment(sAddress) of form syMapPoint + 
     "," + FormatSegment(sCity) of form syMapPoint + 
     "," + FormatSegment(sState) of form syMapPoint + 
     "," + FormatSegment(sZip) of form syMapPoint +
     "," + FormatSegment(sCountry) of form syMapPoint;

Finally, we need to register our function trigger in the Startup script for the Runtime Engine to recognize our integrating solution event.

Startup
{ Startup
  Created by Jon Eastman, Touchstone Group
  This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
}
local integer l_result;

l_result = Trigger_RegisterFunction(function FormatWebAddress of form syMapPoint, TRIGGER_AFTER_ORIGINAL, function GenerateGoogleMapsURL);

So, I figured, this is way cool! So why not implement the Visual Studio Tools for Microsoft Dynamics GP version of it? This would give me a chance to showcase the new event registration methods for functions and procedures.

The following C# code shows the event registration using Visual Studio Tools, but this time using Yahoo! Maps:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Dexterity.Bridge;
using Microsoft.Dexterity.Applications;
using Microsoft.Dexterity.Applications.DynamicsDictionary;

namespace sampleMapService
{
    public class GPAddIn : IDexterityAddIn
    {
        // IDexterityAddIn interface
        SyMapPointForm mapService;

        public void Initialize()
        {
            Dynamics.Forms.SyMapPoint.Functions.FormatWebAddress.InvokeAfterOriginal += new SyMapPointForm.FormatWebAddressFunction.InvokeEventHandler(FormatWebAddress_InvokeAfterOriginal);

        }

        void FormatWebAddress_InvokeAfterOriginal(object sender, SyMapPointForm.FormatWebAddressFunction.InvokeEventArgs e)
        {
            mapService = Dynamics.Forms.SyMapPoint;

            e.result = "http:\\\\maps.yahoo.com\\map?q1=" + mapService.Functions.FormatSegment.Invoke(e.inParam1) + 
                        "+" + mapService.Functions.FormatSegment.Invoke(e.inParam2) +
                        "+" + mapService.Functions.FormatSegment.Invoke(e.inParam3) + 
                        "+" + mapService.Functions.FormatSegment.Invoke(e.inParam4) +
                        "+" + mapService.Functions.FormatSegment.Invoke(e.inParam5);

        }
    }
}

In the above code, we register an InvokeAfterOriginal event, which is very similar to the trigger registration created in Dexterity. Once the event is registered, the actual method, FormatWebAddress_InvokeAfterOriginal() is implemented by reformatting the web address using the event arguments passed by Microsoft Dynamics GP to our method.

Hope you find these two approaches useful. For more information on these development methods, please visit the Learning Resources page on this blog or visit Developing for Dynamics GP.

Downloads

VST Google Maps solution - Click here
VST Yahoo! Maps solution - Click here
VST Sample Map Service Source Code - Click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Monday, November 8, 2010

The Dynamics GP Blogster in Las Vegas, Nevada

The Dynamics GP Blogster will be visiting Las Vegas, Nevada, November 9 - 16. For those of you who would like to exchange business cards and ideas please don't hesitate to reach me at the Trump International. I will be available in the afternoons after 5:30 PM and as time permits.

For those of you who decide to tag along for dinner or otherwise, table topics are fairly open or if you are a prospective or existing customer considering Dynamics GP or looking to move to the next level and would like some insight, please feel free to drop by. Please email me in advance using the link on the right so I can keep a headcount.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Thursday, November 4, 2010

Integration Manager: Integrating journal entries with Analytical Accounting Information

My good friend, David Musgrave, somehow manages to get me involved in interesting topics circulating in his inbox. Just recently, he came across a fairly long thread among his peers, needing to work out some Integration Manager issues for journal entries with Analytical Accounting information. David was kind enough to involve me, as I had posted an answer on the newsgroups a long time ago on this same issue.

If you are one of the fervourous Integration Manager fans out there and have had to work on integrating journal entries with Analytical Accounting information, you may know this is only possible with the eConnect Adapter, not the Standard Adapter.

The eConnect Adapter was introduced with Integration Manager version 10, and replaces the old SQL Optimized Adapter available in prior versions of Integration Manager. The eConnect Adapter in turn, leverages eConnect components to deliver a robust transactional environment for high volume integrations using ADO.NET to access Microsoft Dynamics GP company databases.


eConnect Adapter - Journal Entry# field with Use Default rule value


However, the eConnect Adapter, though, while it provides a Use Default rule value for the Journal Entry# field, this setting causes the integration to fail, as eConnect (the component) requires a value to be supplied, this is, the actual journal number.

Of course the question now is, how do you retrieve the next journal number from your company database to supply this value to the Journal Entry# field to allow the integration to be successful and thereby, preventing you from having to manually reserve ? The answer is, scripting, of the VBScript type.

You can add VBScript code to the Before Document event script in Integration Manager to retrieve the next journal number from your company database, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
Option Explicit

Const adCmdStoredProc = 4
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adInteger = 3
Const adVarchar = 200
Const adBoolean = 11
Const adChar = 129
Const adDate = 7
Const adNumeric = 131

Dim SqlStmt
Dim objConnection, objCommand, NextJournal



Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLNCLI10;Server=MGB001\GP11;Database=TWO; Trusted_Connection=yes;" 

With objCommand
 .ActiveConnection = objConnection
 .CommandType = adCmdStoredProc
 .CommandText = "glGetNextJEWrapper" 'our wrapper stored proc
 
 .Parameters.Append .CreateParameter ("@IO_iOUTJournalEntry", adInteger, adParamOutput, 4)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 .Execute 
 NextJournal = objCommand.Parameters("@IO_iOUTJournalEntry").Value
End With

SetVariable "gblJournal", NextJournal

Set objCommand = Nothing
Set objConnection = Nothing

The above code calls the stored procedure dbo.glGetNextNumberWrapper, which leverages the existing Microsoft Dynamics GP's dbo.glGetNextJournalEntry stored procedure to retrieve the next journal number, stored in the dbo.GL40100 (General Ledger Setup) table. As this is a call to a standard Microsoft Dynamics GP stored procedure, we are avoiding the use of custom code to retrieve the journal number and increment the value at the same time.

It is also necessary to note that the above code uses a Trusted Connection to connect to the company database. You can change the connection string as you see fit, just keep in mind that if you are going to use a SQL login, it cannot be a Microsoft Dynamics GP user login as the password for these logins are encrypted on SQL Server.

The following is the code for the dbo.glGetNextNumberWrapper stored procedure called by the Before Document script:

IF OBJECT_ID('dbo.glGetNextJEWrapper') IS NOT NULL
 DROP PROCEDURE glGetNextJEWrapper;
GO
CREATE PROCEDURE glGetNextJEWrapper 
 @IO_iOUTJournalEntry int OUTPUT,
 @O_iErrorState int OUTPUT
AS
DECLARE @l_tINCheckWORKFiles tinyint = 1, @I_iSQLSessionID int = USER_SID(), @O_tOUTOK tinyint;

IF @IO_iOUTJournalEntry IS NULL 
 SET @IO_iOUTJournalEntry = 0

EXECUTE glGetNextJournalEntry 
   @l_tINCheckWORKFiles
  ,@I_iSQLSessionID
  ,@IO_iOUTJournalEntry OUTPUT
  ,@O_tOUTOK OUTPUT
  ,@O_iErrorState OUTPUT
GO
GRANT EXECUTE ON glGetNextJEWrapper TO DYNGRP;

For more information on connection strings, visit http://www.connectionstrings.com/. Also, check the following article over at Developing for Dynamics GP on why does Microsoft Dynamics GP encrypts passwords.

Once the Before Document event script is implemented, you can then add a small field script to the Journal Number field to retrieve the value stored in the gblJournal Integration Manager global variable, as follows:

' Created by: Mariano Gomez, MVP
' This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.5 Generic license.
CurrentField.Value = GetVariable("gblJournal")

Integration Manager has great import capabilities when combined with the power of scripting and when you have a clear understanding of the underlaying technologies that support it.

Please enter your comments on this article or any methods you have used in the past to overcome similar issues.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Edits:
05/25/2011 - fixed IM global variable name as it was not matching between the Before Document script and the Journal Number Field Script, causing nothing to be assigned to the field and erroring out the integration.

Monday, November 1, 2010

Recovering missing eConnect stored procedures

A frequently asked question among developers is, what to do when eConnect stored procedures go missing from a Microsoft Dynamics GP company database.
As for a background, eConnect stored procedures are typically prefixed by the letters "ta", and are encrypted at the database level.

Object Explorer view of eConnect stored procedures
Because of their encryption, the CREATE function cannot be scripted as with unencrypted stored procedures, rendering the following error message:



But because things can go wrong (and may go wrong), what can you do to recover a missing eConnect stored procedure? Fortunately, the answer lays in a previous topic I covered a few months aback: The Microsoft Dynamics GP Database Maintenance Utility
. The Database Maintenance Utility can also recover missing eConnect stored procedures and any other stored procedure that matter. Because eConnect is not a listed product in the DYNAMICS.SET file, your only choice of product is Microsoft Dynamics GP.

As with all maintenance operations, be sure to backup your database before performing this action. Safe eConnect stored procedures recovery!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/