Archive for April, 2007

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

A new way to post requests to PBS XML Interface

April 11, 2007

In the past when posting a request to PBS XML Interface, we need to do the tedious work of building the xml string for it. As the PBS XML Reference comes with the xsd files, we can make our life easier by generating classes from those xsd files and use it in object oriented way. This approach is much elegant and easy to use than concatenating string.

Please download the full article here:

A New Way to Post Requests.doc

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'

Worry free "stop" statement

April 11, 2007

When working on a Pivotal Client Script, we often add the “stop” statement to launch the script debugger and then remove it from the release version. Most of the time we forget to do it, right?

Fortunately, Pivotal has a feature to turn on the debug flag (UIMaster.DbgFlag) by adding &debug at the end of the url to launch Active Access as below:


I found this flag is very useful when it is combined with our favorite “stop”statement.

If DbgFlag Then Stop

We can just leave the code on the release version and it won’t be executed unless we add the &debug at the end.

Using Non-default Search Result List

April 11, 2007

Since Version 5.0 Pivotal allows us to use non default search result list to display results from an active search.

Although it is very straight forward deal, we still need to pay a little bit attention to the SearchSource parameter that is expected by UseSearchResultsList function. It expects Business Object Internal Name instead of table name and Business Object has to be made visible.

Sub ShowNonDefaultSearchResult(strBusinessObject, _
                                strSearchName, _ 
    Const strSEARCH_TYPE = "search"
    Dim objSearchFactory 
    Set objSearchFactory = _ 
    objSearchFactory.SearchType = searchTypeRegular   
    objSearchFactory.Options.UseSearchResultsList _ 
        strBusinessObject, strSearchResultName
    Set objSearchFactory.Search = _ 
    objSearchFactory.Options.AutoRun = True
    objSearchFactory.UsePlatformButtons = True  
    objSearchFactory.Parameters(0) = _ 
    UIMaster.ShowMultiSelectModal objSearchFactory, Null
End Sub

How to get Pivotal Connection String

April 11, 2007

The C# code below shows us on how to build the trusted connection string from the registry stored by Pivotal.

using Microsoft.Win32;


private string GetConStringByPivotalSystem(string systemName)


    string pivotalKey =


            + systemName;

    string odbcKey = “SOFTWARE\\ODBC\\ODBC.INI\\”;

    string sODBC;

    string serverName;

    string dbName;


    RegistryKey key =

       Registry.LocalMachine.OpenSubKey(pivotalKey, true);


    sODBC = Convert.ToString(key.GetValue(“User Data”));


    odbcKey = odbcKey + sODBC;

    key = Registry.LocalMachine.OpenSubKey(odbcKey, true);

    serverName = Convert.ToString(key.GetValue(“Server”));

    dbName = Convert.ToString(key.GetValue(“Database”));

    return “Data Source=” +

                serverName + “;Initial Catalog=” +

                dbName + “;Integrated Security=SSPI;”;


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