主页 / dba / 问题 / 341987
Accepted
Ross Bush
Asked: 2024-08-28 21:54:32 +0800 CST

添加的文章被忽略

  • 772

有一个发布者-->分销商-->订阅者的设置，并且在这个系统中所有内容都已经配置好了，添加几个表和重新捕捉表的一些简单测试似乎运行良好。

问题是每个数据库有超过 6K 篇文章，多租户系统中有很多数据库。该系统已经运行了 20 多年，几乎所有数据库都积累了堆积物和无法编译的视图或存储过程。每次恢复数据库和/或需要重新发布数据库时，我们无法指向并单击；因此，正在测试以下内容。

我知道这可以编写脚本，但我们正在遵循此建议来重新捕捉表格。

初步测试

  • 创建包含 1 篇文章 tblAccountingBatch 的订阅。
  • 运行下面的脚本来添加所有具有 PK 的表和所有兼容的文章。
  • 重新运行快照代理并获取新表。

结果

  • 用户界面显示所有~5K 篇文章均处于活动状态并在子版块中被选中。
  • 快照运行并为初始表 tblAccountingBatch 生成 1 篇文章。
  • EXEC sp_helpsubscription 仅显示一个表 - tblAccountingBatch。
  • 我尝试使用 sp_addarticle 编写脚本来添加 UI 中勾选的另一个文章，但出现错误，提示该文章已被添加。

有谁知道断开连接可能在哪里吗？快照似乎看不到创建子版块后添加的文章。

我碰到了这个 KB，它描述了一些非常相似的东西；然而，DBA 说这些补丁已经应用于测试环境。

订阅创建后添加所有有效文章的脚本

SET NOCOUNT ON

USE VP_UPS_V05
GO

DECLARE @PublicationName NVARCHAR(200) = 'UPS_PUB'

EXEC sp_changepublication @publication = @PublicationName, @property = 'allow_anonymous' , @value = 'false' 
EXEC sp_changepublication @publication = @PublicationName, @property = 'immediate_sync' , @value = 'false' 


IF(CHARINDEX('VP_',DB_NAME()) = 0 OR CHARINDEX('_V05',DB_NAME()) =0 ) BEGIN
    RAISERROR('This script can only be applied against a Client database', 16, 1)
    RETURN
END


DECLARE @Debug BIT = 0

DECLARE @FullName nvarchar(1000);
DECLARE @ShortName nvarchar(300)
DECLARE @ObjectType nvarchar(300)
DECLARE @HasPrimaryKey BIT
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;


DECLARE @Exceptions TABLE(ShortName NVARCHAR(300), FullName NVARCHAR(300),ObjectType NVARCHAR(300), Result INT, ErrorMessage NVARCHAR(MAX))


DECLARE @IgnoredTables TABLE (TableName NVARCHAR(100))
INSERT INTO @IgnoredTables VALUES
    ('tblAccountingBatch')
    

DECLARE ObjectCursor CURSOR FOR
SELECT 
    ShortName,LongName,ObjectType,HasPrimaryKey
FROM
(
    SELECT  
        OBJECT_NAME(o.object_id) ShortName , 
        QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)) LongName, 
        CASE WHEN type_desc = 'USER_TABLE' THEN CASE WHEN  OBJECTPROPERTY(o.object_id,'tablehasprimaryKey') = 1 THEN 1 ELSE 0 END ELSE 0 END AS HasPrimaryKey,
        type_desc AS ObjectType,
        CASE 
            WHEN type_desc='USER_TABLE' THEN 0
            WHEN type_desc='VIEW' THEN 1 
            WHEN type_desc='SQL_SCALAR_FUNCTION' THEN 2
            WHEN type_desc='SQL_TABLE_VALUED_FUNCTION' THEN 3
            WHEN type_desc='SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 4
            WHEN type_desc='SQL_TRIGGER' THEN 5
            WHEN type_desc='SQL_STORED_PROCEDURE' THEN 6
            ELSE
                7
        END AS ProcessOrder
    FROM 
        sys.objects o
        LEFT OUTER JOIN @IgnoredTables IgnoredTable ON type_desc = 'USER_TABLE' AND IgnoredTable.TableName = OBJECT_NAME(o.object_id)
    WHERE 
        type_desc IN ('USER_TABLE','SQL_STORED_PROCEDURE','SQL_TRIGGER','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','VIEW')    
        AND 
        ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
        AND
        SCHEMA_NAME(o.schema_id)='dbo'
        AND
        IgnoredTable.TableName IS NULL
        AND
        ((type_desc = 'USER_TABLE' AND OBJECT_NAME(o.object_id) LIKE 'tbl%')OR(type_desc<>'USER_TABLE'))
)AS X 
Order BY ProcessOrder, ShortName;

--RETURN

OPEN ObjectCursor;

DECLARE @TotalObjects INT = @@CURSOR_ROWS
DECLARE @CurrentObject INT = 0

FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey

DECLARE @IsError BIT = 0
DECLARE @CompileResult BIT = 0
DECLARE @ErrorMessage NVARCHAR(MAX)

DECLARE @InsertCommand NVARCHAR(300)
DECLARE @DeleteCommand NVARCHAR(300)
DECLARE @UpdateCommand NVARCHAR(300)
DECLARE @ArticleType NVARCHAR(300)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentObject = @CurrentObject + 1
    PRINT 'Processing Object '+CAST(@CurrentObject AS VARCHAR(10))+'/'+CAST(@TotalObjects AS VARCHAR(10))+' - '+@ObjectType+' '+@ShortName
     
    SET @IsError = 0
    SET @ErrorMessage = ''

    IF(@ObjectType = 'USER_TABLE') BEGIN 
        IF(@HasPrimaryKey = 0 ) BEGIN
            SET @IsError = 1
            SET @ErrorMessage = 'Missing Primary Key'   
        END     
    END ELSE BEGIN      
        SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @FullName + '''';       
        BEGIN TRY
            EXEC @CompileResult = sp_executesql @Sql;
            IF @CompileResult <> 0 RAISERROR('Failed', 16, 1);
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = ERROR_MESSAGE()             
        END CATCH
    END

    IF(@IsError = 0 AND @Debug=0) BEGIN
        BEGIN TRY           
            IF(@ObjectType = 'USER_TABLE') BEGIN

                SET @InsertCommand = 'CALL sp_MSins_dbo_'+@ShortName
                SET @DeleteCommand = 'CALL sp_MSdel_dbo_'+@ShortName
                SET @UpdateCommand = 'CALL sp_MSupd_dbo_'+@ShortName

                exec sp_addarticle 
                    @publication = @PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = 'logbased', 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = 'drop', 
                    @schema_option = 0x000000000803509F, 
                    @identityrangemanagementoption = 'manual', 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo', 
                    @vertical_partition = 'false', 
                    @ins_cmd = @InsertCommand, 
                    @del_cmd = @DeleteCommand, 
                    @upd_cmd = @UpdateCommand
            END ELSE IF (@ObjectType IN ('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION','VIEW')) BEGIN
                IF(@ObjectType='SQL_STORED_PROCEDURE') 
                    SET @ArticleType = 'proc schema only'
                ELSE IF(@ObjectType = 'VIEW')   
                    SET @ArticleType = 'view schema only'
                ELSE
                    SET @ArticleType ='func schema only'
                    
                exec sp_addarticle 
                    @publication =@PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = @ArticleType, 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = N'drop', 
                    @schema_option = 0x0000000008000001, 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo'

            END
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = 'Could Not Add Article to Replication - '+ERROR_MESSAGE()  
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        END CATCH
    END 
    
    IF(@IsError = 1)  
        INSERT INTO @Exceptions SELECT  @ShortName, @FullName, @ObjectType, 0, @ErrorMessage
        
    FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor; 

SELECT * FROM @Exceptions
sql-server

1 个回答

  1. Best Answer

    我自己回答这个问题。我发现了许多与此相关的变体。就我而言，这归结为我在 UI 中添加文章时没有运行跟踪。我发现了一篇帖子，其中指出sp_refreshsubscriptions有时有必要调用。这就是我错过的。

    根据观察，如果我错了，请纠正我，第一个快照只需要：

    EXEC sp_reinitsubscription 
    @subscriber = @SubServer,
    @destination_db = @SubDB,
    @publication = @PubName,
    @invalidate_snapshot=1

EXEC sp_startpublication_snapshot
     @publication = @PubName

    此后，使用allow_anonymous和instant_sync添加的文章只需要：

    sp_refreshsubscriptions
sp_startpublication_snapshot
    • 0

相关问题