【OGG】SqlServer同步源端增加trandata报错解决

好久没有更新博客，最近整理整理最近碰到的问题。

 

Sqlserver 2008使用ogg进行数据同步，在添加trandata时报错，添加trandata失败，报错如下：

2016-03-28 13:45:14  WARNING OGG-00552  Database operation failed: SQLExecDirect error:

EXECUTE sys.sp_cdc_enable_db

if 0 = (select st.is_tracked_by_cdc from sys.tables as st where st.object_id = object_id(N'[dbo].[cdrsync]'))

   AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = object_id(N'[dbo].[cdrsync]'))

BEGIN

DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id(N'[dbo].[cdrsync]') as sysname)

CREATE TABLE #ggsTabKeys (db sysname, name sysname, owner sysname, column_name sysname, key_seq int, pk_name sysname)

INSERT INTO #ggsTabKeys EXEC sp_pkeys 'cdrsync', 'dbo'

IF 0 = (SELECT COUNT(*) FROM #ggsTabKeys)

BEGIN

    INSERT INTO #ggsTabKeys

    SELECT TOP (1) DB_NAME(), '', '', name, 1, '' FROM sys.columns sc

    WHERE sc.object_id = OBJECT_ID(N'[dbo].[cdrsync]')

    AND is_computed = 0

    AND max_length > 0

    ORDER BY max_length

END

IF 0 = (select COUNT(*) from #ggsTabKeys)

BEGIN

    INSERT INTO #ggsTabKeys

    SELECT TOP (1) DB_NAME(), '', '', name, 1, '' FROM sys.columns sc

    WHERE sc.object_id = OBJECT_ID(N'[dbo].[cdrsync]')

    AND is_computed = 0

    AND max_length > 0

    ORDER BY max_length

END

DECLARE @cols NVARCHAR(max)

SELECT  @cols =

REPLACE(REPLACE(REPLACE(

STUFF(( SELECT

         ',' + QUOTENAME( t.column_name)

        FROM    #ggsTabKeys AS t

        FOR XML PATH('')

       ), 1, 1, '')

       ,'<','<'),'>','>'),'&','&')

execute sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'cdrsync'

  , @role_name = NULL

  , @captured_column_list = @cols

  , @capture_instance = @capture_instance

IF EXISTS(SELECT OBJECT_ID('tempdb..#ggsTabKeys'))

BEGIN

  DROP TABLE #ggsTabKeys

END

end

 

 

消息 22832，级别 16，状态 1，过程 sp_cdc_enable_table_internal，第 623 行

无法更新元数据来指示已对表 [dbo].[cdrsync] 启用了变更数据捕获。执行命令 '[sys].[sp_cdc_add_job] @job_type = N'capture'' 时失败。返回的错误为 22836: '无法更新数据库 CDRTEST 的元数据来指示已添加某变更数据捕获作业。执行命令 'sp_add_jobstep_internal' 时失败。返回的错误为 14234: '指定的 '@server' 无效(有效值由 sp_helpserver 返回)。'。请使用此操作和错误来确定失败的原因并重新提交请求。'。请使用此操作和错误来确定失败的原因并重新提交请求。

 

原因：SqlServer安装后修改了主机名，导致以下两个语句结果不一致

SELECT * FROM master.dbo.sysservers

SELECT SERVERPROPERTY('ServerName')

 

修复方法：

IF serverproperty('servername')<>@@servername    

  BEGIN  

  DECLARE  @server SYSNAME  

  SET   @server=@@servername      

  EXEC  sp_dropserver @server=@server    

  SET   @server=cast(serverproperty('servername') AS SYSNAME)   

  EXEC  sp_addserver @server=@server,@local='LOCAL'     

  END  

  ELSE  

    PRINT '实例名与主机名一致，无需修改！'