Resolving Contract Line Errors

Steps to correct contract line allotments

Dynamics CRM 4 does not provide a way to revise case resolution allotments after the case has been resolved. Reactivating the case and reclosing it will double-dip allotments from the contract lines, leaving them incorrect.
This must be resolved behind the scenes, using SQL scripts.
NOTE: This is not a supported resolution as it directly modifies data rather than relying on the CRM interface. Always make sure and backup a SQL database before modifying data directly.
This article applies specifically to Dynamics CRM v4.0

Get the Case Resolution Activity ID

Use this procedure to get the activity id related to the closed case.
Open the case. Click the History menu item on the left. The top activity should be the case resolution activity. Open it, then click CTRL+N to open the activity in a new browser window.
The Activity ID is the long string between the {} characters

http://server:5555/company/cs/cases/closecase.aspx?id={68C018E8-5D08-E011-B563-001A4B4D64BE}&_CreateFromType=112&_CreateFromId=%7b616C59EA-2DE8-DF11-BA3B-001A4B4D64BE%7d

Get the Contract Line ID

Click through the case to the contract line and use the same procedure as above to get the contract line id.

Double-Check Your Work

Before you start updating all willy-nilly, make sure you have the correct records.
Using SQL Management Studio, connect to the CRM Company database. Use the following scripts to return the records you’re looking to update. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

SELECT *
FROM ContractDetailBase
WHERE ContractDetailId = '<CONTRACT LINE ID>'

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '<RESOLUTION ID>'

Sample:

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '81A0A2C9-DE1F-DF11-9063-001A4B4D64BE'

Verify that you’re looking at the correct record and note the current values. In the ContractDetailBase table, we’re specifically looking at the AllotmentsUsed and AllotmentsRemaining fields. For the IncidentResolutionBase table, we’re changing the TimeSpent field.

Update Your Values

If everything matches up and we’re sure we have the right records, use the scripts below to update the correct fields. Replace the <NEW VALUE> tag with the number you wish to use. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

UPDATE IncidentResolutionBase
SET TimeSpent = '<NEW VALUE>'
Where ActivityId = '<CONTRACT LINE ID>’

UPDATE ContractDetailBase
SET AllotmentsUsed = '<NEW VALUE>', AllotmentsRemaining = '<NEW VALUE>'
WHERE ContractDetailId = '<RESOLUTION ID>'

You’ll first adjust the Resolution Activity to the correct value. Make a note of how much it as changed and then adjust the Allotments Used on the Contract Line accordingly. Then, use the same number and adjust the Allotments Remaining as well.
For example, we closed a case and resolved it for 30 minutes, when actually it should have been resolved for 45 minutes. The Contract Line is for a total of 600 minutes (10 hours) and after we closed this case, 4 hours (240) had already been used up.
Old Values

Case Resolution: TimeSpent = 30
Contract Line: Allotments Used = 240
Contract Line: AllotmentsRemaining = 360

Corrected Values

Case Resolution: TimeSpent = 45
Contract Line: Allotments Used = 255
Contract Line: AllotmentsRemaining = 345

CounterPoint SQL DTS Errors

One of the software packages our company sells / supports is a Point of Sale package called CounterPoint by Radiant Systems. For more information about CounterPoint, check out our website at www.accelerando.net

One of our project managers was migrating a customer from the older Pervasive based version to the new SQL based version. CounterPoint provides a utility to import historical data the uses the DTS engine to push the data into the new databases.

As you may know, DTS is a depreciated feature and can be difficult to get running if running on a new system as the SQL installs no longer include it.

There is a backwards compatibility toolkit available for SQL that provides DTS capabilities and this was installed. However, CounterPoint was still firing errors when the DTS package kicked off. The errors we were getting in the log file when running were

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:ActiveX Scripting Transform 'DTSTransformation__4' was not able to initialize the script execution engine.

Ultimately, this came down to missing DLL files in the DTS implementation. Following is a list of the steps I came up with to enable DTS functionality.

Requirements

• SQL 2005 Backward Compatibility Components (source below)
• The redistributable DTS files (available on SQL 2000 Media)

Steps

• Install the SQL 2005 Backward compatibility components
• Open a command prompt and change directory to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
• Register the following list of DLL’s using the syntax regsvr32 dllname.dll
  • dtsffile.dll
  • dtspkg.dll
  • dtspump.dll
  • axscphst.dll
  • custtask.dll

In our case, the axscphst.dll file was not registered because it had not installed with the Backward Compatibility Components. I was able to retrieve the file from the SQL 2000 media

Note that each of the above DLL files has an associated .RLL file that is located in Resources\1033 under the Binn folder listed above. The .RLL files are also available from the SQL 2000 Media.

NOTE: For my fellow co-workers, I have a zip file put together with all the files involved. It’s available on the ADrive FTP site. Contact me for connection information if needed. There’s a publically downloadable link here - http://tinyurl.com/sql2000dts

In coming up with this solution, I relied on the following sources:

• Redistributing DTS with your program
  • List of files needed to redistribute DTS
• SQL 2005 Feature Pack - Microsoft SQL Server 2005 Backward Compatibility Components
  • Install pack for the DTS files
• Technet: How to install support for DTS Packages
  • A listing of the procedures for installing DTS on various platforms

GP: Retrieve List of Company Names & IDs

Here’s a script to return you a quick list of the company ids (which are also the database name) and the company names for the companies defined in the DYNAMICS database

SELECT INTERID,CMPNYNAM FROM SY01500
ORDER BY CMPNYNAM

GP: Reset the System Password

Working as a GP Consultant, I occasionally run into a situation where a client doesn’t remember their System Password for GP. You know, the one that let’s you add users, change company information, the admin level tasks in GP.

The only supported solution is to contact MBS Support, who will walk you thru a process to gather information to send them.

There’s an easier way. Run the following script in SQL to reset the system password to blank. You can then go back into Setup and set it to whatever you want.

update DYNAMICS.dbo.SY02400
set PASSWORD = 0x00202020202020202020202020202020
WHERE DMYPWDID = 1

GP: Error setting User Access to Company

In Dynamics GP, administrators have the ability to control what companies users can access. This also controls what users see when the click the drop-down company field during login.

The menu instructions for this post are for GP 9.0. Newer versions provide the same functionality, although menu layout may be slightly different.

To change User Access for a user, click Tools / Setup / System / User Access. Clicking a username will display all of the available databases on the right and you can click the checkbox to allow or disallow access to specific companies for that user.

Every once in a while, especially have migrating to a new server or merging companies, you’ll get an error message “The user could not be added to one or more database”

A typical reason for this is that on the SQL side of the installation, the GP user already exists as a SQL user on the database, but GP doesn’t know about it. When you click the checkbox, GP tries to add that user to the database users. Since the object already exists, SQL kicks back an error that GP doesn’t know how to deal with.

To resolve the error, open SQL Management Studio and connect to your GP SQL instance. Then, run the following script, replacing <dbname> with the Database name of the company you’re trying to add the user to and replacing <username> with the user’s SQL login id. Our example is for company “Jim’s Bike Shop” that has a database name of JBS and the user id being jfallon.

USE <dbname>
DROP USER <userid>

So for our example, the code would be

USE JBS
DROP USER jfallon

Go back into GP and you should now be able to add the user to the company.

Shrink a LogFile in SQL

Over time, log files on SQL databases can become bloated. This procedure will provide scripts to shrink a log file back to manageable sizes.

1) Perform a complete backup of the database

2) Perform a backup of the transaction log file

3) Run following script to shrink the log file
USE [DB_NAME]
DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY

4) Check the size of the log file. If it has not shrunk, there may be some data causing it to fail the move. Use the following script to create a sample table and fill it with data which _should_ free up the stuck data.

/* Create the table */
USE [DB_NAME]
DROP TABLE MyTable
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)

/* Fill the table */
SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

5) After running the script rerun the shrink script. The transaction log should now shrink.

USE [DB_NAME]
DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY

6) If it still hasn't shrunk, delete data from the MyTable table and rerun the fill script. Try the shrink again.

/* Diagnostics */
DBCC LOGINFO

 

This tip came from Rob Bamforth on his blog at http://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/

Techniques to Shrink a SQL Database

Sometimes the problem is not an inflated logfile or dbfile, but the data itself. In that case, cleaning out old data will help regain that valuable disk space.

A way to gain a little space without purging data is to reindex the database. Indexes, like many other things in SQL grow over time and a reindex will shrink them back down to their fighting weight.

 

 

To shrink the indexes, use the following script…

• USE MyDatabase
• GO
• EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 85)"
• GO
• EXEC sp_updatestats
• GO

This script will take some time to process. After it is completed, you’ll need to shrink the actual file to regain that lost space.

• DBCC SHRINKFILE (N'DB_Logical_Name' , 100, TRUNCATEONLY)