Microsoft Dynamics GP Home Page scripting errors on Citrix XenApp

November 3, 2011

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

September 26, 2011

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

August 17, 2011

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

August 8, 2011

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’

July 6, 2011

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 Secure Link 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

May 18, 2011

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

February 1, 2011

>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/