Archive for the ‘Migration’ Category

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.

Advertisements

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
GO
DECLARE @Last_Id AS BINARY(8)

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

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