Qlik Replicate using existing SQL Server target view for replication

[Alan_Wang]

By default, Qlik Replicate reads primary keys from source tables and creates target tables using those same keys. If you want to use an existing view that doesn’t share the same key columns, you can modify the replication process to define matching key columns and adjust the task settings to prevent it from reloading the target table.

In table transformations, use Set Key Columns > Use transformation definition to ensure the key columns match the target view.

But using Views as the target (instead of a table) will result in this error, as indexes cannot be applied to views.

[TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 1939 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot create index on view 'PPHTRAN' because the view is not schema bound. Line: 1 Column: -1 [1022502] (ar_odbc_stmt.c:5083)

Resolution

Target views behave differently from tables, but an internal parameter can be used to trigger a manual query. To achieve this, add the $info.query_syntax.create_index internal parameter and value to the SQL Server target endpoint.

1. Go to the  Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Add the following:
   
   • Parameter: $info.query_syntax.create_index
   • Value: select 1
     
     The SELECT 1 query itself has no effect, but it overrides the task behavior, preventing it from attempting to create a new unique index on the view, which is not supported.
     
     
     
     For more information about Internal Parameters, see Qlik Replicate: How to set Internal Parameters and what are they for?

Internal Investigation ID(s)

SUPPORT-9276

Environment

• Qlik Replicate