Oracle GoldenGate add trandata errors

Question

I am trying to add trandata to GoldenGate from SQL Server 2008 r2. I am getting an error stating

. ODBC error: SQLSTATE 01000 native database error 16954. [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor.

2013-10-07 12:04:05 WARNING OGG-00782 Error in changing transaction logging for table: 'dbo.TestTable1'. ERROR: ODBC Error occurred. See event log for details..

This is the event log message:

2013-10-07 12:04:02  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for ODBC:  GGSCI command (horeth): ADD TRANDATA dbo.TestTable1.
2013-10-07 12:04:05  WARNING OGG-00552  Oracle GoldenGate Command Interpreter for ODBC:  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('dbo.TestTable1'))    AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = object_id('dbo.TestTable1')) BEGIN DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id('dbo.TestTable1') 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 'TestTable1', '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('dbo.TestTable1')     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('dbo.TestTable1')     AND is_computed = 0      AND max_length > 0     ORDER BY max_length END DECLARE @cols NVARCHAR(max) SELECT  @cols = 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'TestTable1'   , @role_name = NULL   , @captured_column_list = @cols   , @capture_instance = @capture_instance IF EXISTS(SELECT OBJECT_ID('tempdb..#ggsTabKeys')) BEGIN   DROP TABLE #ggsTabKeys END end . ODBC error: SQLSTATE 01000 native database error 16954. [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor.
2013-10-07 12:04:05  WARNING OGG-00782  Oracle GoldenGate Command Interpreter for ODBC:  Error in changing transaction logging for table: 'dbo.TestTable1'.

Here is the full error message from the ggsci app:

GGSCI (DSI-THINK) 3> ADD TRANDATA dbo.TestTable1

2013-10-07 12:04:05  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('dbo.TestTable1'))
   AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = object_id('dbo.TestTable1'))
BEGIN
DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id('dbo.TestTable1') as sysname)
CREATE TABLE #ggsTabKeys (db sysname, name sysname, owner sysname, column_name sysname, key_seq int, pk_name sysna
me)
INSERT INTO #ggsTabKeys EXEC sp_pkeys 'TestTable1', '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('dbo.TestTable1')
    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('dbo.TestTable1')
    AND is_computed = 0
    AND max_length > 0
    ORDER BY max_length
END
DECLARE @cols NVARCHAR(max)
SELECT  @cols = 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'TestTable1'
  , @role_name = NULL
  , @captured_column_list = @cols
  , @capture_instance = @capture_instance
IF EXISTS(SELECT OBJECT_ID('tempdb..#ggsTabKeys'))
BEGIN
  DROP TABLE #ggsTabKeys
END
end

. ODBC error: SQLSTATE 01000 native database error 16954. [Microsoft][SQL Server Native Client 10.0][SQL Server]Ex
ecuting SQL directly; no cursor.

2013-10-07 12:04:05  WARNING OGG-00782  Error in changing transaction logging for table: 'dbo.TestTable1'.
ERROR: ODBC Error occurred. See event log for details..

GGSCI (DSI-THINK) 4>

I've set up an ODBC using SQL Server Native Client 10.0 & another ODBC using SQL Server Native Client 11.0 and tried them both and got the same error.

Answer 1

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.