Unit of Measure Schedule Troubleshooting with CRM Connector for GP

When running the CRM Connector for GP, one of the maps that integrates the two systems is a UofM Schedule to Unit Group map that transfers the GP Unit of Measure schedules into CRM.

One of the requirements for the map to run successfully is that the UoM schedules in GP can’t have repeating values in the schedule. Take for example the situation below.

Schedule for Gallon
Unit Name	Quantity	Equivalent
Gallon	1.000	Gallon
Gallon	0.018	Drum
Drum	55	Gallon
Schedule for Drum
Unit Name	Quantity	Equivalent
Drum	1	Drum
Drum	55	Gallon
Gallon	0.018	Drum

A limitation of CRM is that a Unit Group cannot contain the unit name more than once. During the map’s integration, only the first 2 lines of each schedule would be integrated, the connector stops processing with the first duplicate is detected.

Finding those duplicates can be a little tricky though. You’ll be able to see them in the log for the map in the Connector for Dynamics mapping client. However, this isn’t very descriptive. Here’s an example of an error message.

[UofM Schedule to Unit Group] has encountered an error while processing key [GAL]. A Unit named GAL already exists in the specified Unit Group which may or may not have the same Base Unit.  Microsoft Dynamics CRM does not currently permit Units to have the same name when they belong to the same Unit Group.

Once you do find the U of M schedule that’s causing problems, it’s sometimes challenging to find the duplicates. It would also be handy to know if there’s going to be any problem rows before you Activate the map.

I’ve written a script to help you find (and mark) the problem rows before you even start the integration.

Search for Duplicate UofM Schedule Rows

SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)

Return all the rows from IV40202 that are duplicates

SELECT *
FROM IV40202 WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

To make the records easier to find in GP, we can use the Long Description field for the row as an indicate.
NOTE: THIS WILL OVERWRITE THE LONG DESCRIPTION FIELD FOR ANY RECORD THAT’S A DUPLICATE!!

Update Long Description to indicate problem rows

UPDATE IV40202
SET UOFMLONGDESC = 'Duplicate'
WHERE (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
IN (
SELECT (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) AS DIST
FROM IV40202
GROUP BY (RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM))
HAVING (COUNT(RTRIM(UOMSCHDL) + '-' + RTRIM(UOFM)) <> 1)
)

Now, if you open the Unit of Measure Schedule in the GP client and pull up one of the problem schedules, you should be able to expand the rows and see the lines that are causing or will cause problems.

Update:

In the above example tables, you’ll encounter errors when you try to delete the duplicate rows. GP doesn’t want you to have the base unit of measure in the schedule more than once, even though it allows you to enter it. In this case, you’d have to remove the extra row from the SQL table. Beware, modifying data directly in SQL is NOT supported by Microsoft.

CounterPoint v7 Gift / Credit Card Expiration

One of the applications I support is Radiant’s CounterPoint Point of Sale software. In the v7 version of the product, we had an incident where a customer had a gift card that had an available balance and an expiration date in 2020.

When swiped, the card would read properly, then come back with a ‘Card Expired’ error.

The answer is a little obscure, but it’s a work-around that was done to make the product Y2K compliant. Since CounterPoint v7 is written in COBOL, it maintains only a 2 digit year record. To get around this, it also maintains a ‘current century’ setting that defines to the system what years fall within the current century. By default, that is set to 1920 – 2020. So with the card expiring after 2020, the system thought it was reading 1920 and errored out.

To resolve, click through to Setup / System / Company / Option #7 is the current century.

Update to some time before company opened (eg: 1980 - 2080)

Done!

Sideloading the Kindle Fire

Amazon’s app store for the Kindle Fire is woefully incomplete. The Facebook ‘app’ is just a link to the mobile facebook site, there’s no Youtube client and the built-in browser is…well, clunky is a good description.

There is, however, a way to get other apps onto the Fire by going around the Amazon Market. It involves installing the apps on an Android device with full access to the Google Market, then backing up the applications and copying them onto the Kindle.

I’m providing a shortcut for the first part and have published a .ZIP file containing a few of the apps that I use. I’m only including apps that are not available already in the Amazon Market. Follow the steps below to load the them onto your Kindle Fire.

If there’s an app you want that’s not included, let me know and I’ll see if I can get it to work.

Requirements

• Kindle Fire – available here if you don’t have one.
• PC – available here.
• USB Cable (USB to Micro-B USB) available here.

Sideload steps

• On the Kindle Fire, download and install the Easy Installer app from the Amazon Market.
  • Go to the Apps tab, click the Store link and then search for Easy Installer.
• On your PC, download the application zip file from my FTP site using the link below in the Current App List section.
• Connect your Kindle Fire to your PC using a USB cable.
• After a few minutes, the Kindle should show up as an attached drive with a drive letter.
• Copy the files from the zip file you downloaded in step 2 above into the X:\Downloads folder on the Kindle (where ‘X’ is the drive that your computer assigned to the Kindle)
• Note that you have to have the Kindle powered on and unlocked in order for it to show up.
• After the files are copied on, give it about 15 seconds to finish up and then unplug the Kindle.
• Launch the Easy Installer app that you installed in step 1 above.
• The Installer app will scan your Kindle for new install files. Put a check mark by the apps you want to install and then hit the Install Selected Apps button at the bottom of the page.
• As each application installs, click the Done button after it installs and you’ll be taken to the install screen for the next app.
• If something messes up after the install, just go back and run the installer again and reinstall.

Current App List

I have pulled together a few apps that work (mostly) and have eliminated a few that don’t. If you have something you want me to try, let me know and I’ll probably be able to try it out. They have to be FREE apps in the Android market because there’s no authentication mechanism on the Kindle Fire.

Download here - APKPack.zip

Working Apps (included in the .ZIP file download)

• Daily Office / Mission St. Clare (1.0)
• Dice Bag (v5.2.2)
• Manage your dice and auto-roll
• Dolphin Browser (v7.3.0)
• Alternate web browser
• Facebook (v1.8.2)
• Firefox Browser (v10.0) 
• Go Launcher X (v2.7.1)
• Alternative to the Amazon home screen – makes it look like a regular tablet
• Google Maps (v6.0.3)
• Network Tools (v1.1.6)
• Server monitoring via ping
• Screen Filter (v1.2)
• Lets you really dim the screen for reading at night and for improved battery life
• Spellbook for D&D 3.5 (v2.0.5)
• Because I’m a geek and I like keeping my list of spells for D&D handy
• Spotify (v0.4.12.180)
• Kind of works, the player controls are hidden by the Fire’s bottom navigation menu
• You have to quit the app to stop it from playing
• WatchESPN (v1.3.1)
• Doesn’t work for Insight Broadband, but might work for you if you’re a Time Warner customer.
• XKCD Viewer (v3.0.2)
• Because the one in the Amazon Market is crap
• Youtube v2.3.4)
• So much better than viewing in the crappy Amazon browser

Failed Apps (Tested and won’t install or don’t run right)

• Android Marketplace
• Gmail client
• Google+
• Google Listen
• Google Music
• Louisville Mobile
• US Bank
• WeatherBug

Uninstalling Apps

If you decide you don’t like one of the apps you installed, go to the app screen, long press on the app icon and choose Remove from Device.

Not all the apps showing up in the Installer?

The Installer doesn’t rescan every time it runs. To force it to rescan, click the Menu button and choose Settings, then Clear Cache.

Version History

APKPack.zip

Dolphin Browser (v7.2.1) / Facebook (v1.8.1) / Go Launcher X / Google Maps (v6.0.3) / Screen Filter (v1.2) / Spellbook for D&D 3.5 (v2.0.5) / Youtube v2.3.4)

APKPack2.zip

Added Dice Bag (v5.2.2) / Network Tools (v1.1.6)/ Spotify (v0.4.12.180) /WatchESPN (v1.3.1) / XKCD Viewer (v3.0.2)

Acknowledgements

This process was based in part on this article by PC Mag -

How To Run Almost Any Android App On the Kindle Fire

Recommended SQL Source Books

Based on a recommendation list by Kevin Kline of Quest Software. This is his recommended list of, if I was only going to buy one or two books in each category of SQL management, here’s what I would buy. They’re on my Amazon wishlist if someone’s feeling generous…anyone…Bueller?

For T-SQL programming
 

For SQL Internals

For Administration

CRM: Comparing Time Worked with Time Billed on Resolved Cases

This article applies to CRM 4.0

We’re a technical service company and we manage the work effort needed to resolve issues by using cases and tasks in CRM. At the end of a case, however, the person resolving the case determines how much of the time worked was billable.

On the one hand, we want to be fair and balanced to our clients, but on the other hand we put bread on the table by selling our knowledge and experience by the hour. There’s not an easy way to report on the discrepancies between how much work was done and how much was billed.

Thanks to David Jennaway, I’ve got a handy-dandy T-SQL script that I used to come up with an Excel spreadsheet that pulls the data from a SQL view. I’ve modified his original script somewhat to bring back the Account Name rather than the Incident ID.

select
    i.accountidname,
    i.title,
    ir.actualend,
    max(timespent) as BillableTime,
    isnull(sum(a.actualdurationminutes), 0) as TotalActivityTime

from filteredactivitypointer a
    join filteredincident i on a.regardingobjectid = i.incidentid
    left outer join filteredincidentresolution ir on i.incidentid = ir.incidentid and ir.statecode = 1
    and ir.actualend = (select max(actualend) from filteredincidentresolution ir2 where ir2.incidentid = ir.incidentid and ir2.statecode = 1)
where i.statecode = 1

I actually saved this as a view, then called it from an Excel spreadsheet. That lets you pull the data into a pivot table, etc.

You can also then build on this and figure out your top accounts using a script like this -

SELECT
    Accountidname as AccountName,
    COUNT(accountidname) AS Cases,
    SUM(BillableTime) AS TimeBilled,
    SUM(TotalActivityTime) as TimeWorked,
    SUM(TotalActivityTime) - SUM(BillableTime) as Variance
from acc_ResolvedCaseTimeComparison
GROUP BY ACCOUNTIDNAME
ORDER BY TimeBilled DESC

Where myresolvedcaseview is the name that you gave your SQL view. This query brings back each account, the number of resolved cases, the amount of time worked, time billed, and the variance.

Note, you’re running directly against the SQL data, so it depends on specific security rights to the database to see the data.

Troubleshooting CRM Performance

Once in a while, the SQL box hosting CRM at the office begins to bog down. Here are a few scripts and procedures that I use to help troubleshoot what can be causing the slowdowns.

First of all – start with the basics. Does the server have adequate disk space. How badly fragmented are the drives?

NOTE: The built-in Windows Defrag won’t touch files larger than 2 GB so you may have to defrag by moving the large files onto another volume, defragging, then bringing them back.

Find a SQL process with high CPU utilization

Here’s a general SQL script that will show you the process causing high CPU usage. You can then use the SPID to kill the offending process. Insert the name of your organization database into the script as noted.

USE Master
GO

DECLARE @DATABASE_ID INT
SET @DATABASE_ID = DB_ID(‘organizationname_MSCRM');

SELECT ST.TEXT,
SP.*
FROM DBO.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.DBID = @DATABASE_ID
ORDER BY CPU DESC
GO

Once you know which process is stuck, you can kill it using the following command. Insert the correct SPID you gathered from the script above. NOTE: KILLING A RUNNING PROCESS IS NORMALLY A BAD IDEA. Only use this to kill a process that’s hung.

Kill spid

Cleaning up completed workflows

As workflows complete, their records remain in the AsyncOperationsBase table. Over time, this table can grow to enormous size. To resolve the problem, run the following script against your organization_MSCRM database, replacing with your orgname where noted. This is a one-time running script. If you want it to run on a recurring basis, you could set it up as a SQL job.

NOTE: There is sometimes business value in being able to see completed workflows. Make sure you really want them gone before running the script.

IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin     
begin tran     
insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)    
       Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end     
        if (@continue = 1)        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId            
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where WS.AsyncOperationId = d.AsyncOperationID
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
            delete @DeletedAsyncRowsTable     
end      
commit
end
--Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

The above script was taken from Microsoft KB968520

Finding and removing waiting workflows

Here’s a script to list open workflow operations on the sql server. These are Workflows that are stuck in a waiting state. Too many and the server starts bogging down. I start getting worried at 50,000 and then at 100,000 we usually see the server start to croak.

Run this against your organization_mscrm database.

Select DISTINCT(asyncoperation.Name ), COUNT(asyncoperation.Name ) AS NUM
from asyncoperation
where StateCode = 1
    and RecurrenceStartTime is null
    and DeletionStateCode = 0
Group BY asyncoperation.Name
ORDER BY NUM DESC

An example might be a workflow that no longer exists, but has live versions of itself stuck in a waiting state. To remove these orphaned workflows, use the following steps. You can get the workflow name from the script above. Insert into the marked locations below.

PLEASE NOTE: MS DOES NOT SUPPORT editing data directly in the database. You do this at your own risk, and for the sake of all that’s holy, backup your database first.

delete from workflowwaitsubscriptionbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from workflowlogbase

where asyncoperationid in

(select asyncoperationid from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1)

delete from asyncoperationbase where name = 'insert name of workflow here' and StateCode = 1

Async service on multi-core

KB Article 2489162 on Customersource / Partnersource has a good tip on improving CRM Async performance on multi-core servers. Apparently the Async service doesn’t automatically take advantage of multi-core processors. Login to CS/PS and search for article 2489162 to find the article titled How to improve Microsoft CRM Async Performance on multi-core servers

SQL Server–Reboot Pending

An annoying error that stops SQL reconfigs dead. Sometimes it’s warrented…so reboot your freaking server already. However, sometimes it’s a case of the computer’s right hand not knowing what it’s left one is doing and something gets stuck in the registry. Clearing that pesky stop error can sometimes be resolved by clearing the entries found on the following key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

Being a geek, I always save the key out before I delete it, but then I’ve been around long enough to have that healthy fear.