Database schemas in TimeXtender Data Integration allow developers to apply a specific schema to a table or group of tables. Schemas can be used to restrict access to tables that are not useful or needed for report designers, simplifying the creation of reports based on the available schemas.
Schemas can be set for either Data Areas or individual tables. Schema settings applied at the table level take precedence over those applied on the data area.
When adding a data area, the name provided will be set as the default schema for the objects in the new data area.
The data area name being used as the schema for the tables in this data area is done is so that Direct Read will be able to work with tables in different data areas. The next data area created may also be in the same database but will be differentiated from the other data areas as its name will be used as the schema for its tables and objects.
Edit existing schema
The schema name can be amended after it has been created. Use the following steps to edit an existing schema:
Right-click on the default schema
Click Edit Schema
Enter a new name in the Name field to change the schema name.
A refresh message will appear advising a refresh is needed to display the new schema name in the data area tree. Click Yes.
Lastly, deploy all the tables in the data area to apply the new schema name.
Troubleshooting
Different data areas in the same Prepare instance should not use the same schema name. The following message will appear if a data area attempts to use a schema name that already exits in a different data area.
In the warning dialog above, it is not advisable to click Yes and attempt to use the existing schema name, though it is possible.
If an attempt is made to deploy tables whose schema has the same name as a different data area schema, then errors are likely to occur due to that fact that the tables may have the same schema and name as tables existing in other data areas. If the deployment of tables in the first data area is successful, then it is likely that the subsequent deployment of tables in the second data area will have errors.
Adding a Database Schema to a Data Area
Use the following steps to create a database schema.
In the Data Area, right-click on the Database Schemas folder, and then select Add Database Schema.
Enter a name for the new schema in the Name box, and enter the schema owner In the Owner box below. The default owner is "dbo". Click OK to create the schema.
To configure the schema behavior, right-click the new schema in the Database Schemas folder and select the appropriate behavior from the following options as delineated below.
None: The schema will not be applied unless manually assigned to the table.
Main default schema: The schema will be applied to all tables and views in the data area.
Main Raw default schema: The schema will be applied to all Raw (_R postfix) tables in the data area.
Main Transfer default schema: The schema will be applied to all Transfer (_T postfix) views in the data area.
Main Valid default schema: The schema will be applied to all Valid (_V postfix) tables and views in the data area.
Main Error/Warning default schema: The schema will be applied to all Link and Message (_L and _M postfix) tables in the data area.
Main Securable Column default schema: The schema will be applied to all secured columns created.
Main Securable View default schema: The schema will be applied to all secured views created.
Main Key Store default schema: The schema will be applied to all key stores created.
Use the following steps to manually assign the schema to a table, as may be necessary when None is selected as the Schema Behavior. Right-click the table, click Table Settings and click the Table Schemas tab. Select the schema to be used for the table based on the options as described in Step 3 above.
Assigning user rights to the schema can be done through SQL Server Management Studio or T-SQL. Click on the link below to open an article on the Microsoft website that details how to grant user rights using T-SQL: http://msdn.microsoft.com/en-us/library/ms187940.aspx
Configuring Schemas for Tables
Table schemas are configured in the Table Settings as follows:
Right-click on a table and select Table Settings.
Select the Table Schemas tab where the various objects that comprise the table along with their schemas are listed out. Select the appropriate schema to associate with the various tables (raw, valid, etc.). The lists includes all the schemas that have been added to the data area of the table. If needed, click Add new schema... to add a new schema for use with the table or data source.
Click OK
Troubleshooting
If a new schema is added to either the entire data area or a table, and if the deployment is run with the Only modified tables and views option as shown below, then the deployment may not be able to complete and result in an error.
The error displayed may be similar to the following:
Error: Create failed for table ‘schemaname.tablename_r’ An exception occurred during while executing a Transact-SQL statement or batch. The specific schema name “schemaname” does not exist or you do not have permission to use it.
The reason for the error above is that the Only deploy modified tables and views deployment option will excludes the deployment of the new schema, which needs to be deployed before the table using this schema can be deployed.
The deployment of the schema can be done as follows:
Page 1 / 1
@Thomas Lind Do you know in what database table/field in the Timextender Project Repository we can look up what schema a specific TX table is stored in?
@sten.lomme ,
in TX 20.10.34 the DataTables] table holds the SchemaName] field and there is also TxObjectSchemaSettings] which (I believe) may hold the Schema allocations for individual tables.
