SytelineMaster

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