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:
- tt_*
- tmp_*
- tmp_snapshot_*
- tmp_staging_*
There are others that end with:
- _ii
- _dd
- _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.
-
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.
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
Recent Comments