SqlPackage not accepting fully qualified name of t

I am using SymmetricDS version 3.9.15 on Azure SQL for both master and slave databases. I am able to configure the tool correctly to synchronize the databases but while publishing the database project the SqlPackage command gives an error due to the fully qualified name(including catalog name & schema name) of the table and function in the following trigger.

    USE [STAGING_PROD_Copy]
    GO

    /****** Object:  Trigger [dbo].[SYM_ON_D_FOR_TRGGRLL_1_PRMRYSTGNG_PRDS]    Script Date: 08-11-2018 21:17:09 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    create trigger [dbo].[SYM_ON_D_FOR_TRGGRLL_1_PRMRYSTGNG_PRDS] on [STAGING_PROD_Copy].[dbo].[__RefactorLog] with execute as caller after delete as                                                                                                                             
      begin                                                                                                                                                                  
        declare @NCT int 
        set @NCT = @@OPTIONS & 512 
        set nocount on                                                                                                                                                       
        declare @TransactionId varchar(1000)                                                                                                                                 
        if (@@TRANCOUNT > 0) begin                                                                                                                                           
           select @TransactionId = convert(VARCHAR(1000),transaction_id)    from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0                                           
        end                                                                                                                                                                  

        if (1=1) begin                                                                                                                           
            insert into  "STAGING_PROD_Copy"."dbo".sym_data (table_name, event_type, trigger_hist_id, pk_data, old_data, channel_id, transaction_id, source_node_id, external_data, create_time) 
            select '__RefactorLog','D', 100, 
              case when deleted."OperationKey" is null then '' else '"' + replace(replace(convert(varchar(36),deleted."OperationKey") ,'\','\\'),'"','\"') + '"' end, 
              case when deleted."OperationKey" is null then '' else '"' + replace(replace(convert(varchar(36),deleted."OperationKey") ,'\','\\'),'"','\"') + '"' end, 'matter', 
                  @TransactionId,  "STAGING_PROD_Copy".dbo.sym_node_disabled(), null, current_timestamp
            from deleted where 1=1                                                                      
        end                                                                                                                                                                  

        if (@NCT = 0) set nocount off                                                                                                                                         
       end                                                                                                                                                                    
    ---- go
    GO

The two references that are causing the issue are as follows.

"STAGING_PROD_Copy"."dbo".sym_data
"STAGING_PROD_Copy".dbo.sym_node_disabled

How to specify the source and target schema/catalog in such a way so that it's not used while installing the sync triggers.

I am using the following sym_router & sym_trigger table.

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,target_catalog_name,target_schema_name,target_table_name,USE_SOURCE_CATALOG_SCHEMA,router_type,create_time,last_update_time)
values('primary_2_secondary-staging_prod-us', 'primary-staging_prod-us', 'secondary-staging_prod-us', null, null, null, 0,'default',GetDate(), GetDate());

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,target_catalog_name,target_schema_name,target_table_name,USE_SOURCE_CATALOG_SCHEMA,router_type,create_time,last_update_time)
values('secondary_2_primary-staging_prod-us', 'secondary-staging_prod-us', 'primary-staging_prod-us', null, null, null, 0,'default', GetDate(), GetDate());



insert into sym_trigger 
(trigger_id,source_catalog_name,source_schema_name, source_table_name, channel_id, sync_on_insert, sync_on_update, sync_on_delete, last_update_time,create_time, sync_on_incoming_batch)
values('TriggerAll_1', null, null, '__RefactorLog', 'matter', 1 , 1, 1, GetDate(), GetDate(), 1);

Upon writing to Microsoft support I got the feedback that cross-database queries are not supported in Azure SQL. But there are 1000 auto-generated sync triggers in the database with fully qualified names(inlcuding catalog & schema) of tables and functions and manual alter trigger is not possible.

Even exporting the database with such a trigger is causing an error in SSMS.

Regards

Rajat Agrawal

回答1:

Azure SQL Database not only does not support cross database queries, it also does not support three- or four-part names when you make reference to a table/object.

Have you considered using Azure SQL Data Sync to synchronize your databases? You won't have these kind of issues with Azure SQL Data Sync and is fully supported by Microsoft/Azure.

If you want to export that database you will have to script those triggers, remove them, export your database, import it on a new server and run the script to recreate all triggers.

回答2:

You can disable catalogue name in triggers using the following option in SymmetricDS.

mssql.include.catalog.in.triggers=false

(Includes the catalog/database name within generated triggers (catalog.schema.table). May need turned off to support backup processes such as creating a bacpac file)

You can read more about this option here.

https://www.symmetricds.org/doc/3.9/html/user-guide.html#_runtime_parameters

Regards Rajat Agrawal