SqlPackage不接受同步触发器中的表的标准名称 [英] SqlPackage not accepting fully qualified name of tables in sync trigger
问题描述
我正在Azure SQL上将SymmetricDS版本3.9.15用于主数据库和从数据库.我能够正确配置该工具以同步数据库，但是在发布数据库项目时，由于以下触发器中表和函数的全限定名称(包括目录名称和架构名称)，SqlPackage命令会给出错误./p>
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_data
"STAGING_PROD_Copy" .dbo.sym_node_disabled
"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.
我正在使用以下sym_router& sym_trigger表.
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);
在写给Microsoft支持后，我得到的反馈是Azure SQL不支持跨数据库查询.但是数据库中有1000个自动生成的同步触发器，其中包含表和函数的完全限定名称(包括目录和架构)，因此无法进行手动更改触发器.
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.
即使使用这样的触发器导出数据库，也会在SSMS中引起错误.
Even exporting the database with such a trigger is causing an error in SSMS.
致谢
Rajat Agrawal
Rajat Agrawal
推荐答案
您可以使用SymmetricDS中的以下选项在触发器中禁用目录名称.
You can disable catalogue name in triggers using the following option in SymmetricDS.
mssql.include.catalog.in.triggers = false
(在生成的触发器(catalog.schema.table)中包括目录/数据库名称.可能需要关闭以支持备份过程，例如创建bacpac文件)
(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
问候 拉贾特·阿格劳瓦尔(Rajat Agrawal)
Regards Rajat Agrawal
这篇关于SqlPackage不接受同步触发器中的表的标准名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
