TIBCO Cloud Integration Connect - (SCRIBE) How to change the schema prefix for SQL Server tables created by a data replication app

TIBCO Cloud Integration Connect - (SCRIBE) How to change the schema prefix for SQL Server tables created by a data replication app

book

Article ID: KB0072666

calendar_today

Updated On:

Products Versions
TIBCO Cloud Integration - Connect ( Scribe ) -

Description

When a data replication app runs and records are written to the target SQL Server database, the app will use the settings for the SQL Server user selected in the TIBCO Scribe Online Connector For Microsoft SQL Server connection settings. Tables are created with the Default Schema prefix defined under the SQL user's properties in SQL Server. Depending on what the default schema value is set to, tables being created by the data replication app may appear as domain\serviceaccount.D365entityname or dbo.D365entityname. 

User-added image

The default schema may not be what is desired and there will be a tables created by the data replication job that have an unwanted schema. 

Issue/Introduction

Changing the Default Schema value for a user in Microsoft SQL Management Studio being used with the TIBCO Scribe Online Connector For Microsoft SQL Server.

Resolution

Follow the steps to set the Default Schema value for the Microsoft SQL Server user being used in the connection
  • Open Microsoft SQL Server Management Studio and connect to the database.
  • Expand the SQL Server Security folder.
  • Expand Logins and find the user being used in the connection.
  • Right click the user and select Properties.
User-added image
  • Select User Mappings.
  • Find the database that you are working with and make changes to the Default Schema field as needed. 
  • Click OK to save changes. 
User-added image
  • Reset the connection metadata for the Microsoft SQL Server connection so new tables are created with the new desired schema prefix. 
Follow the steps below to change the schema of a table using SQL Server Management Studio
  • in Object Explorer, right-click on the table and then click Design.
User-added image
  • Press F4 to open the Properties window. In the Schema box, select a new schema.
User-added image
  • Close the properties window and the design window and save changes.
User-added image
  • Refresh the database to verify the new schema on the table.
Powered by Wolken Software