Microsoft Dynamics GP Home Page scripting errors on Citrix XenApp

For the past 2 weeks I have been working on a fairly complex installation of Microsoft Dynamics GP for a hospitality staffing company whose hardware infrastructure is hosted by a very reputable provider. My client has made all their applications available to their end users via Citrix XenApp. Xenapp is Citrix Systems’ secure on-demand application delivery platform for the Citrix Presentation Server.

After completing the installation of Microsoft Dynamics GP on the Citrix Servers, the next step was exposing the application to XenApp. This by all accounts is a fairly straightforward process. I logged into XenApp, launch Microsoft Dynamics GP and everything was working fine: home page, reports, SSRS reports, Business Analyzer, Management Reporter, the whole 9 yards!

A couple days later, users began reporting home page script errors as the one shown below:

Typically, if you follow the prescribed KB article 918313 – Frequently asked questions about the home pages and area pages features in Microsoft Dynamics GP, specifically Q21 and Q31 you end up understanding the following:

1. There must be a UserData folder under the %Appdata%\Microsoft\Internet Explorer folder. Under normal circumstances, this folder will get created when the user profile is first initiated.

2. You cannot roam user profiles and in particular the user Temp folders as this causes issues with Microsoft Dynamics GP.

We went through both questions – well, we went through all the questions – and realized that the UserData folder was not created for the end-users. Remember, I was not getting this error under my domain account, but I attribute this to the fact that I was a domain admin. Effectively, we setup the UserData folders, but the problem persisted.

I then went to the Run… command utility with my Windows account and typed %Appdata% and quickly realized that Windows Explorer was taking me to \\\UserData$\Appdata\…, in effect confirming that roaming profiles was active for all users on the domain.

This seems to contradict the previous point #2 above. However, this is where all hell broke lose with the infrastructure vendor. It turns out that disabling roaming profiles on a Citrix farm may cause all kinds of unpredictable results for other applications such as Microsoft Outlook which tend to lose personalized settings when bouncing between servers in the farm – this is a load balanced environment with 4 servers.

In short, disabling roaming profiles was not an option!

However, in working with the point Citrix and Windows engineer at the infrastructure provider and running some ProcMon traces, we realized that each time the home page attempted to load a file, ProcMon would display errors stating that the file location could not be found (effectively what the error was displaying even after the UserData folder was created). Since the profiles were being roamed this was disconcerting.

Of course, the immediate thought was, “it must be a permissions issue!”. We then tested this theory by opening Windows Explorer and attempting to navigate to the Roaming Profile location with a standard user account. No luck!

The engineer quickly realized that the only way this could happen was via a Global Policy setting. In doing some more research, the likely candidate emerged: Remove Run Menu From Start Menu.

Clearly, from the policy description above, UNC paths are blocked and accessing local folders like \temp is a no, no (temp is roamed off to another server, remember?). Once we disabled the policy, Microsoft Dynamics GP started working like a charm and the errors were gone!

Now, you may say, “not sure we want to enable the Run commad for end-users”. In my client’s particular case, this made no difference because applications are being deployed via XenApp and not accessed from a desktop, so disabling the policy won’t have any effect. At the end of the day, we all went home happy, which begs the question…

Can the above KB article be revised?

Until next post!

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

Jumping on the Windows 8 Metro bandwagon

In my most recent In My Humble Opinion article I weigh in on the subject of Windows 8 Metro and Microsoft Dynamics GP “12” web client, and Microsoft’s decision to go it the Silverlight route. Read why I think Silverlight is still the best choice as a development platform and how Windows 8 can still drive business applications requiring rich controls on the front-end.

You can read the article at – Jumping on the Windows 8 bandwagon on the Community site.

For more information on Windows 8, including a link to download the Developer’s edition, click below:

Read more at the Microsoft News Center

Download the Windows Developer Preview

Until next post!

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

Default Printer not ‘sticking’ when running Microsoft Dynamics GP in Terminal Services RemoteApp

A bit of theory…

RemoteApp programs are programs that are accessed remotely through Terminal Services and appear as if they are running on the end user’s local computer. Users can run RemoteApp programs side by side with their local programs. A user can minimize, maximize, and resize the program window, and can easily start multiple programs at the same time. If a user is running more than one RemoteApp program on the same terminal server, the RemoteApp programs will share the same Terminal Services session.

The following Microsoft TechNet article explains in more detail:

Terminal Services RemoteApp (TS RemoteApp)

Background

Microsoft Dynamics GP (versions 10.0 and 2010), is currently supported in a Terminal Server RemoteApp environment, but a number of my clients and forum users have reported in numerous occasions that the Default Printer settings are not ‘sticking’ or simply saving when loging out of the application (which also closes the RemoteApp session) and returning to it.

Print Setup window

The Solution

Puzzled by this, I began doing some digging and found out that the default printer “problem” is actually not a problem, but rather the way RemoteApp decides how the session disconnection will occur. Playing along with the disconnection, there was a new Terminal Server group policy setting that was introduced to control time limits for disconnection and there lies the dirty little secret.

If you are experiencing the issue I described above, please take a look at the following article by the Remote Desktop Services (Terminal Services) Team Blog:

Terminal Services RemoteApp™ Session Termination Logic
http://blogs.msdn.com/b/rds/archive/2007/09/28/terminal-services-remoteapp-session-termination-logic.aspx

The article unveils the steps required to change this behavior and make your Microsoft Dynamics GP printer ‘stick’.

Also, if you are using Named Printers with Microsoft Dynamics GP in a Terminal Services RemoteApp environment, take a look at the following articles over at Developing for Dynamics GP:

Using Named Printers with Terminal Server
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/08/15/using-named-printers-with-terminal-server.aspx

Named Printers application default printer selections not “sticking”
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/06/24/named-printers-application-default-printer-selections-not-quot-sticking-quot.aspx

Troubleshooting Named Printers Issues
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/05/26/troubleshooting-named-printers-issues.aspx

Please let me know with your comments if any of the above recommendations by the Remote Desktop Services team worked for you.

Until next post!

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

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client’s identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP

--  This code is licensed under the Creative Commons 

--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

use DYNAMICS;

go



DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);

DECLARE @sqlStmt varchar(255);



DECLARE c_reportsecurity CURSOR FOR 

	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a

		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)

	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')

	ORDER BY a.USERID;



OPEN c_reportsecurity;

FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;



WHILE @@FETCH_STATUS = 0

BEGIN

	SELECT @ssrsrole = 

		CASE 

			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'

			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'

			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'

			WHEN @securityroleid = 'CA AGENT*                ' THEN ''

			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'CL AGENT*                ' THEN ''

			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'

			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'

			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''

			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''

			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''

			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'

			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'

			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'

			WHEN @securityroleid = 'PM AGENT*                ' THEN ''

			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''

			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'

			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'

			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'

			WHEN @securityroleid = 'RT AGENT*                ' THEN ''

			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''

			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'

			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'

			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''

			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''

			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''

			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''

			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''

		END

	

	IF (@ssrsRole <> '') 

	BEGIN

		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');

		EXEC(@sqlStmt);

	END

	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

END



CLOSE c_reportsecurity;

DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time… phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/

Violation of PRIMARY KEY constraint ‘PKSY60100’

Just recently, I assisted a partner with an issue they were having creating a new company in Microsoft Dynamics GP 10 – though, I supposed the same could happen with any other version. In the process of creating the company record, almost at the end of all the routines executed by Dynamics Utilities they were getting the error:

Violation of PRIMARY KEY constraint ‘PKSY60100’. Cannot insert duplicate key in object ‘dbo.SY60100’

KB Article 871699  suggests the problem could be that the DYNAMICS database is associated with a database owner (dbo) other than DYNSA. After running the sp_helpdb system stored procedure, it was determined that the database owner of the DYNAMICS database (and other company databases) was indeed ‘sa’. Knowing this obviously helps, and the solution is as simple as changing the database owner back to DYNSA.

The partner then ran the sp_changedbowner system stored procedure to reset the database owner to DYNSA and got the following error:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database

Having gotten this error, we proceeded to drop the DYNSA from the DYNAMICS database as follows:

USE DYNAMICS;
GO
DROP USER DYNSA;
GO

Having dropped the user from the database, we needed to re-add DYNSA as the database owner of the DYNAMICS database. This time, I decided to try the new ALTER AUTHORIZATION statement as the customer is running Microsoft SQL Server 2008 R2, as sp_changedbowner will be deprecated from SQL Server sometimes soon.

ALTER AUTHORIZATION ON DATABASE::DYNAMICS TO DYNSA;

Once we executed this command, we restarted the company creation process in Utilities and the error was no longer.

If you find yourself in a similar situation then this should definitely help.

Until next post!

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

Microsoft Dynamics GP Database Installation and Upgrade Statuses

I have to admit that to deliver this post I had to dig deep into the bowels of the Microsoft Dynamics GP Utilities. Installation and upgrade statuses don’t seem to be anywhere (that I could find anyways) handy and can be very important when looking at a DEXSQL.LOG file for reasons of a failure during any of these processes.

Status Constant	Storage Value
DU_STATUS_DONE	0
DU_STATUS_START	1
DU_STATUS_INSTALL	2
DU_STATUS_UPGRADE	3
DU_STATUS_BIND_DEFAULTS	7
DU_STATUS_RECOMPILE	8
DU_STATUS_CONVERT	23
DU_STATUS_POST_CONVERT	30

During the installation or upgrade of a database, status codes are recorded in the DYNAMICS..DB_Upgrade table for each Microsoft Dynamics GP dictionary being updated.

Until next post!

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

>Troubleshooting printing issues with Microsoft Dynamics GP

>Just lately, I have seen a number of inquiries over the various forums on printer support for Microsoft Dynamics GP (usually, versions 10.0 and 2010). If you have been in the channel as an ISV, partner, or customer since the days of the former Great Plains Software, you probably still remember that in the past testing of Microsoft Dynamics GP included exhaustive testing of a number of printers for compatibility.

In fact, leading up to the release of version 10.0, it was not uncommon to see the now infamous Printer Compatibility List, which would outline an extensive list of printers tested for all sort of “maneuvers” including large print jobs, paper handling, character blurriness, and so on. There are still some reminiscent KB articles from back in the days when printers were tested – go grab them now before they disappear!

KB article 870301 – Compatibility of Dynamics With the HP LaserJet 6L or HP LaserJet 6L SE Printer
KB article 865797 – HP OfficeJet 520 Printers
KB article 865796 – HP 6LXI Printers
KB article 865782 – HP Laserjet 5000, 5M and checks
KB article 865399 – Okidata ML 320 printing checks

Starting with version 10.0, Microsoft decided that “enough is enough!”, well, not exactly in those terms, but the point being that printer manufacturers had sorted out most of the issues revolving around standards that plagued the 90’s and 00’s. Printer compatibility was mostly an issue during the transition from impact printers to laser printers — for those of you out there too young to remember, this is what a dot matrix or impact printer looked like. This one in particular, happens to be an Epson (and yes, I owned one myself!).

Epson dot matrix printer

Another main reason was, they were simply too many printers to be tested! You can find the official word from Microsoft on printer testing with Microsoft Dynamics GP by using the following links:

Printer compatibility for Microsoft Dynamics GP 10.0 – click here
Printer Compatibility for Microsoft Dynamics GP 2010 – click here

Then what are you supposed to do if you suspect a compatibility issue between Microsoft Dynamics GP and a printer in your organization? Here is my list of suggestions/questions you should ask yourself to fight the printing battle:

1. Are you in a Terminal Server environment? Terminal Server environments are notoriously known for printing issues when printer driver versions on workstations do not match the Terminal Server’s (or viceversa) during the printer re-direction process. I strongly suggest you consider downloading the Terminal Server Printer Redirection Wizard Tool, which will help you troubleshoot and replace print drivers that were unsuccessfully redirected.

2. Are you using Named Printers? There are a couple known issues with Named Printers “acting up” and not saving certain settings, thus causing erratic behaviors when printing. However, if you are using Named Printers on a Terminal Server environment, my good friend, David Musgrave has written what I consider to be the ultimate guide to Using Named Printers with Terminal Server.

3. Are all the printer driver versions the same on every machine? Different printer driver versions, even for the same printer, can produce unexpected behaviors, for example, if bugs have been fixed in the latest version of the driver. So always ensure you are running the same printer driver version on all workstations – oh, and make sure you obtain the drivers directly from the manufacturer’s website, not the one shipped to Microsoft by the manufacturer. Many times, printer drivers are updated by the manufacturer after a version of Windows have been released.

4. Other common troubleshooting guidelines…does the problem happen to all users or one user in particular? You know you will be asked this question if you contact Microsoft Support, so do your homework and save yourself some time. This will allow you to establish whether there’s a method to the madness you are experiencing. By asking this question, Microsoft Support will attempt to establish whether there’s an issue with that particular user profile in your environment.

5. Does the problem happen with one machine in particular or does it happen across all machines? The objective here is to establish whether the behavior is consistent at the machine level, regardless of the user signed into the machine. If the problem can be replicated regardless of the user, then you can begin to establish a pattern. Working your way through several computers will help establish if there’s a bigger issue at play.

For additional information on troubleshooting printing issues:

KB article 959033 – Frequently asked questions about printer issues in Microsoft Dynamics GP 2010, Microsoft Dynamics GP 10.0 or in Microsoft Dynamics GP 9.0

Until next post!

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

>Microsoft SQL Server DSN Configuration

>As I wrapped up fellow MVP Victoria Yudin‘s book just a week aback and prepared to write a review, I was reminded of the importance of properly configuring your Microsoft Dynamics GP DSN connection — the artifact that allows the client software to read and write data to your company databases and the system database — something the Microsoft Dynamics GP installation gracefully setup automatically nowadays for you. So, if this is the case, why is it that so many people still have issues with DSN connections and the Microsoft Dynamics GP client not “seeing” the server, I wondered.

So, I figured in this article, I would go back to basics to walk through some of the common issues and demistify the Microsoft SQL Server DSN configuration options that the setup gracefully takes care of.

1. First up, if you are on Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, you should be using the Microsoft SQL Server Native Client driver. At this point, if you have any legacy ODBC drivers for systems that have been upgraded from SQL Server 7.0 or SQL Server 2000, these should not be used to connect to SQL Server 2005 or SQL Server 2008, because a) you are not taking full advantage of the Native Client driver’s performance, and b) simply because the old driver is not designed to be used with the new.


Getting started


Clearly name your connection so you can identify which version of GP or server you are targetting. If you have multiple physical environments, for example, test and production, clearly name the driver to distinguish which environment you are targeting. Also, note that you can setup a driver that points to an instance of SQL Server by using the MACHINE\INSTANCE_NAME convention. Starting with version 10.0, the ODBC name must be exactly the same across all workstations where Microsoft Dynamics GP is installed.

2. Microsoft Dynamics GP only supports SQL Server authentication. As much as you complain or rant about the system not supporting Integrated Windows Authentication, you cannot set the authentication method to anything other than SQL Server authentication, in which case you will need a login ID and password only to test your connection. You can certainly avoid going through the other steps of the setup if you choose to uncheck the Connect to SQL Server to obtain default settings for the additional configuration options checkmark.



Setting up the Authentication method

Also note the checkmark option’s prompt. The settings are read from SQL Server to obtain the default ODBC connection settings. This might sound redundant, but you will understand what this means next.

Some frequently asked questions as well are:

a) Why can’t I use my Microsoft Dynamics GP account to authenticate my ODBC connection?

Because Microsoft Dynamics GP encrypts passwords on SQL Server. Since the password is encrypted on Microsoft SQL Server you would have to enter the sequence of characters that are a part of the encrypted password to authenticate and this is simply not feasible as well, you don’t know the encrypted password to begin with. For more information see Why does Microsoft Dynamics GP encrypt passwords over at Developing for Dynamics GP.

b) Does the password I enter here get stored with the Connection?

Categorically No! The user Id and password information entered here is only used for verification of the SQL Server default settings and testing of the connection itself. They are never stored with the setup.

3. With newer versions of Microsoft Dynamics GP, there’s no need to set the default database to Microsoft Dynamics GP system database, DYNAMICS. However, since I am an old timer and still have my own quirks, I do it as a standard practice. The default database is the master database when no other database is specified in the connection.

Choosing default database

What is still standard though is to disable the Use ANSI quoted identifiers and Use ANSI nulls, paddings and warnings in your connection settings. Now, keep in mind from my previous observation, that these connection settings are defaulted from Microsoft SQL Server settings at first. Why I emphasize this? Because I always get asked, Why are these checkmarks on? When in doubt, ask your Microsoft SQL Server administrator to show you the SQL Server properties for Connections.

Microsoft SQL Server Properties window – Connections tab

Also, you will want to note that at this point you can define a Mirroring server if you are running a mirrored database environment for your Microsoft Dynamics GP databases. For more information on Mirrroring, see KB article 926490 – Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP (access to CustomerSource or PartnerSource is required to view this article).

These are some frequently asked questions:

a) Why can’t I enable ANSI quoted identifiers and ANSI nulls, warnings, and pads?

The answer lays with Microsoft Dexterity. Dexterity (through the Runtime Engine) does not support quoted identifiers for character strings or hetorogeneous transactions — the function of ANSI nulls, warnings, and pads is to maintain consistency of transactions and queries across distributed platforms. Since Microsoft Dynamics GP is a client/server based application and the system database, DYNAMICS was designed to live on the same SQL Server with the company databases, there was really no need to maintain this compatibility. After all, no heterogeneous query would ever be issued to begin with. 

4. Some more settings that are key to keep in mind reside with this wizard page. Among them Perform translation for character data, which receives most of the attention among Microsoft Dynamics GP consultants and database adminstrators alike. This setting was designed to do, as it name suggests, translations of characters between the client code pages and the server code page.

Defining connection settings

In earlier versions of MDAC, i.e., MDAC 2.1 or later version of the SQL Server ODBC driver (version 3.70.0623 or later) or the OLEDB provider (version 7.01.0623 or later), under some circumstances you could experience translation of character data from the client code page to the server code page, even when Autotranslation is disabled for the connection. Autotranslation is not the only mechanism that can result in code page conversion. The SQL Server 7.0 ODBC driver and OLEDB provider introduced a behavior when connecting to MSDE 1.0, SQL Server 7.0, or later versions of either. All SQL statements sent as a language event are converted to Unicode on the client before being sent to the server. The end effect of this is similar to an Autotranslation of all data flowing from the client to the server through a language event, regardless of the current Autotranslation setting for the connection. This will not introduce any difficulties except when trying to store non-translated character data from a code page other than SQL Server’s code page.

So why do we talk about code pages? Because a) Microsoft Dynamics GP does not support Unicode characters. If you are a database developer it means, there is no such thing as NCHAR, NVARCHAR, or NTEXT table columns defined throughout the system or company databases, that would otherwise allow for Unicode character storage. Since this has been the case since version 1 of Microsoft Dynamics GP, it’s easy to see how a different client code page (as in, a different language being ran at the Windows operating system level) could cause issues if this option were to be enabled). I have ran my own tests with a Russian locale (code page 1251) and 1250 code page on SQL Server and when this option is enabled, I get jumbled data in my GP tables when submitting Russian characters to be stored on the server. That’s because, while there are subtle differences between code page 1251 and code page 1250, the latter only supports Eastern European languages that are based on the latin character set.

5. Once you have completed the settings, it’s now time to review the summary for all the options you have chosen. 

Reviewing connection configuration

6. Finally, you can test and you should now be good to go.

Testing connection

I hope this review of ODBC settings for the SQL Server Native driver have served as a good first step in understanding the configuration. With so many settings it’s easy to see why any subtle change would cause issues across the board..

Until next post!

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

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/

Microsoft Dynamics GP 2010 Service Pack 1 now available!

Over at Inside Microsoft Dynamics GP, Andy Westby has news on the release of Microsoft Dynamics GP 2010 Service Pack 1. Customers and Partners can now download the new service pack from these links:

CustomerSource: https://mbs.microsoft.com/customersource/downloads/servicepacks/mdgp2010_patchreleases.htm

PartnerSource:
https://mbs.microsoft.com/partnersource/downloads/servicepack/mdgp2010_patchreleases.htm

NOTE: As of the time of this post, I could not download the new service pack as it seems the download site is already extremely busy.

With the release of Service Pack 1, Microsoft will be launching 2 new online services. However, these have yet to be disclosed.

Until next post!

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