Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Based on the responses here, this appears to be a bug in the calling application. –  Jon Seigel Oct 7 '13 at 16:55
    
I'm using ADD TRANDATA dbo.TestTable1 (The name of my table in the database) and that's in the ggsci application –  user Oct 7 '13 at 16:58
    
Sorry, I don't know anything about Oracle. The SQL Server part of things looks okay from what I can see (aside from the duplicated code that inserts into the temporary table, but that wouldn't cause this kind of error). –  Jon Seigel Oct 7 '13 at 17:01
    
Could it be that you're running into a permission issue disguised as a different error? Does the account that is connection to SQL Server have the ability to execute sys.sp_cdc_enable_db in the database? It requires membership in the sysadmin fixed server role –  billinkc Oct 7 '13 at 17:08
    
@billinkc, I added execute ability to the user account i'm using and it looks like it didn't help. But, I think I may have found the problem. –  user Oct 7 '13 at 17:23
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Yep, CDC is only supported on Enterprise edition. @billinkc must have been right that this error message was masking the real problem. –  Jon Seigel Oct 8 '13 at 16:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.