ogg源端mysql添加trandata_【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 '實例名與主機名一致，無需修改！'