SytelineMaster

They are not stated in Syteline manual, but from my experience, I would suggest the following routine after upgrade a Syteline DB.

1) Regenerate _all table, by using “Update _All Tables” form.

2) Regenerate trigger, by using “Trigger management” form.

3) Regenerate Replication Triggers, if you are using multi-site replication.

4) Drop all the _tt and _tmp tables.  These would force system to rebuild those temp tables.  Please refer to Information on the different temporary tables in a SyteLine database for a script to drop all those temp table.

In the SyteLine database you will find Temporary tables that start with:

1. tt_*
2. tmp_*
3. tmp_snapshot_*
4. tmp_staging_*

There are others that end with:

1. _ii
2. _dd
3. _all.

The first six are various forms of permanent "temporary" tables, used for temporary processing of data.

The *_all tables are used to remove the requirement that another site must be connected in order to query its data. They contain a subset of columns from the other sites.

More detail:

• tt_ and tmp_ tables are shared temporary tables that allow for less blocking on the actual data tables while processing a limited number of records. Since the actual data table is queried when users are viewing a form, it is advantageous to move processing of smaller amounts of records to a temporary table thereby avoiding contention in the database. There is no real difference between those that start with tt_ and those that start with tmp_. tmp_ is the new standard prefix for tables where data is temporarily stored to support some process. The prefix tt_ was used for that functionality converted from SL6 where tt-* was used.

• tmp_snapshot and tmp_staging are generally used by Print/Post forms. tmp_snapshot_ is a static copy of the data so that printing uses the same data as posting (avoiding problems where someone changes the data between the two steps). tmp_staging is basically a RowPointer to the original record, plus some flags to indicate what steps of print/post have been completed.

• _ii & _dd tables were created to avoid SQL performance problems specific to trigger actions. They are used exclusively by triggers to pass data from the trigger to procedures that they call. Simplifying the trigger decreases the likelihood that SQL will need to recompile the trigger.

• Another important table of note is dynamic_table. This table dictates how permanent "temporary" tables are built. Since some of these tables are snapshots of other tables, database schema maintenance is less error prone when a change to the real table is automatically applied to the permanent "temporary" table. This also allows for more fluid introduction of UET’s into standard SyteLine processing.

• The life span of the data in all of these tables is intended to be short. Once all steps are complete in any given application, the records (which are segregated by by ProcessId) should programmatically be deleted. If records persist, then it is a sign of abnormal application shutdown, or a bug in the code which fails to execute the cleanup. If you know that no one is in a given application, for instance if you have shut down SQL services, you can delete the records in the table, but note that this should not be a routine action as these tables should be self-purging and may have entries in them relating to scheduled tasks that have not yet been activated.

• If the table is listed in dynamic_table, then if needed, the table can be deleted and the system will recreate it when necessary.

The following script will delete all temporary tables that are dynamic and will be recreated by the system when needed.
DO NOT MODIFY SCRIPT – – – -ALWAYS MAKE BACKUP OF DATABASE BEFORE RUNNING SCRIPT

declare @command1 nvarchar(2000)
declare @command2 nvarchar(2000)
declare @whereand nvarchar(2000)

set @command1 = ‘Drop table ?’
set @command2 = ‘Print "Dropped table ?"’
set @whereand = ‘and
(o.name like "tmp_ap%"
or o.name like "tmp_snapshot_ap%"
or o.name like "tmp_staging_ap%"
or o.name in (SELECT name from dynamic_table))
and o.name not in ("tmp_aps_sync","tmp_ap_quick_payments")’

exec sp_MSforeachtable
@command1 = @command1,
@command2 = @command2,
@whereand = @whereand

set @command1 = ‘Truncate table ?’
set @command2 = ‘Print "Truncated table ?"’
set @whereand = ‘and o.name in ("tmp_ap_quick_payments")’

exec sp_MSforeachtable
@command1 = @command1,
@command2 = @command2,
@whereand = @whereand

For those who come from old school of Progress Syteline (Symix) like me, “For Each” command is so nature to us, in terms of loop through record for processing.  But SQL is pretty much bulk processing language.  There is simply no equivalent command as “For Each” in T-SQL. 

There are basically two way to loop through each record in a record set for processing in T-SQL.  The first one, also the most popular one being used through out Syteline SP, is to use the CURSOR. 

Use CURSOR

Here is a example:

DECLARE CUR_ITEMWHSE CURSOR LOCAL STATIC FOR
    SELECT
        item,
        whse,
        qty_on_hand,
        qty_alloc_co
    From itemwhse
    where itemwhse.qty_on_hand > 0 and qty_alloc_co > 0

OPEN CUR_ITEMWHSE
    WHILE @Severity = 0
    BEGIN
        FETCH CUR_ITEMWHSE INTO
            @item,
            @whse,
            @qty_on_hand,
            @qty_alloc_co

        IF @@FETCH_STATUS = -1
            BREAK

        …  /* Your process logic here */

     END

CLOSE CURSOR CUR_ITEMWHSE

Other way is to use the temp table.

Use Temp Table

You will first need to define a temp table with a field “Processed”

declare @Consumers table (
    [OrderID] [int] NOT NULL,
    [OrderDate] [smalldatetime] NULL,
    …   

    [Processed] [smallint] null
primary key (OrderID)
)

You then loop through the record set by select the first 1, which hasn’t been processed.

/* Loop through Consumers  to process     */       
While Exists(Select 1 from @Consumers where processed = 0)
begin
select top 1
    @OrderID = [OrderID],
    @OrderDate = [OrderDate],
     …
      from @Consumers where processed = 0

    … /* Your process logic here  */

Update @Consumers

set processed = 1 where OrderID = @OrderID

end

Be noted that these two method can not be mixed.  You can’t declare cursor for a temp table.