Archive for the ‘Pivotal Tips’ Category

Better than a napkin: Track your BM changes

May 28, 2007

As Pivotal Customizers, we all love the feature of Transporter that allows us to easily transport changes at the element level from one BM to another. Even for some of us, building a transporter file is part of their day to day life.

When creating a transporter file, first, we need to know which elements to be included to the transporter file and make sure we are not missing anything. Some people keep track their changes on a napkin or their journal, just use their memory to remember, or run the script below to get all the changes automatically. The choice is yours.

Here is the script to keep track the changes since certain date – it’s not not complete, however I found it is good enough for Active Access Development.


Using client script to host xml configuration

May 16, 2007

To make an appserver rule configurable, we often need to store some parameters as a separate xml file. The only problem with this approach is that we have to remember to copy the xml file to the (production) server.

In Pivotal, the more practical way to store the configuration is to host the xml string inside a client script. Since the client script is part of the BM, it will always be available and there is no special deployment required.

Below is the example on how to retrieve xml configuration from a client script.

<?xml version="1.0" encoding="utf-8"?>
  <logging logFile = "D:\PivotalLog.txt" logLevel = "2">

Fig. Sample of XML Configuration hosted as a client script

string ClientScriptMapping = "CMS_Config";
string mapping = 
string logSettingString = 
    XmlDocument xd = new XmlDocument();
    string xPathString = "/config/logging";
    XmlNode xNode = xd.SelectSingleNode(xPathString);
    logFile = xNode.Attributes["logFile"].Value;
    logSettingString = xNode.Attributes["logLevel"].Value;
    logSetting = (LogLevel)Convert.ToUInt16(logSettingString);

Fig. .NET ASR to retrieve xml string a client script

Migration: Using rsp_tablename_update_formulas

April 13, 2007

One thing we are missing when doing migration with direct sql approach is that all table level formulas will not be automatically calculated. In the past we must reproduce the code inside our migration script to handle all formulas manually.

To help the performance, Pivotal automatically generates a bunch of stored procedures inside the ED with the following format rsp_tablename_update_formulas. Most of them have only three parameters:

@recordId binary (8) 
@CurrentUserId binary(8) 
@CurrentUserName varchar(255) 

These stored procedures, we can use it out-of-the-box by calling it inside our migration script.

Unfortunately, for more complex formulas such as the ones with date related, soundex etc, Pivotal platform decided to do the calculation at the application level and pass the calculated values the stored procedures as additional parameters such as @P1, @P2, etc.

The beauty with this approach is, we only need to re-code the application level formulas and leave most of the calculation to the Pivotal auto generated stored procedures.

Migration: Generate Pivotal ID in SQL 2005

April 11, 2007

Below is the sample code on how to generate Pivotal ID using the new ROW_NUMBER() feature in SQL 2005.

USE AdventureWorksLT

    @Last_Id = Last_Id_Value 
FROM CMS59_ED.dbo.RSys_Last_Id 
WHERE Table_Name = 'Contact'

    CONVERT(bigint, @Last_Id) + ROW_NUMBER() 
    OVER(ORDER BY CustomerID) 
    AS Binary(8)) AS Contact_Id,
FROM SalesLT.Customer

How to reset Pivotal License Info

April 11, 2007

You need to run this script to totally reset all the license information from your Pivotal BM or ED.

use <your_bm_or_ed>
update users set lkey = null

update rsys_system_flags
set rn_value = null
where rn_flag like 'plm%'

update rsys_system_flags
set rn_value = NEWID()
where rn_flag = 'MasterSourceDatabaseId'

update rsys_system_flags
set rn_value = NEWID()
where rn_flag = 'DatabaseId'

How to add version number to Pivotal BM?

April 10, 2007

During Pivotal Development often we need to be able to track which version the users/testers are running against. Below is the sample client script code to show the version number. The version number itself stored as a global variable. Please note that you need to make the client script as global script.

    1 Dim strVersion                              

    2 Dim objNewCell


    4 strVersion = “”


    6 Set objNewCell = _

    7     UIMaster.documentMenu._

    8     getElementsByTagName(“TABLE”)(0). _

    9     insertRow(1).insertCell(0)


   11 objNewCell.className = “FindTitleCell”

   12 objNewCell.innerText = “BM Version: “ + strVersion