This discussion is archived
8 Replies Latest reply: Aug 28, 2013 6:15 PM by ChowTerence RSS

NEED HELP for ADD trandata issue

ChowTerence Newbie
Currently Being Moderated

hi all:

    I have a huge problem in my add trandata from ODBC data source on MSSQL Server 2008 R2 the log as follow:

 

GGSCI (LGDBA) 142> add trandata dbo.aspnet_sqlcachetablesforchangenotification

 

2013-08-23 15:14:43  WARNING OGG-01483  The key for table [QPBaoHuangDB.dbo.AspN

et_SqlCacheTablesForChangeNotification] contains one or more variable length col

umns.  These columns may not have their pre-images written to the transaction lo

g during updates.  Please use KEYCOLS to specify a key for Oracle GoldenGate to

use on this table.

 

2013-08-23 15:14:43  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 = o

bject_id(N'dbo.AspNet_SqlCacheTablesForChangeNotification'))

   AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = o

bject_id(N'dbo.AspNet_SqlCacheTablesForChangeNotification'))

BEGIN

DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id(N'dbo.AspNet_S

qlCacheTablesForChangeNotification') as sysname)

CREATE TABLE #ggsTabKeys (db sysname, name sysname, owner sysname, column_name s

ysname, key_seq int, pk_name sysname)

INSERT INTO #ggsTabKeys EXEC sp_pkeys 'AspNet_SqlCacheTablesForChangeNotificatio

n', '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.AspNet_SqlCacheTablesForChangeNotificat

ion')

    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.AspNet_SqlCacheTablesForChangeNotificat

ion')

    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, '')

       ,'&lt;','<'),'&gt;','>'),'&amp;','&')

execute sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'AspNet_SqlCacheTablesForChangeNotification'

  , @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 directly SQL;No cursors。.

 

2013-08-23 15:14:43  WARNING OGG-00782  Error in changing transaction logging fo

r table: 'dbo.AspNet_SqlCacheTablesForChangeNotification'.

ERROR: ODBC Error occurred. See event log for details..

 

GGSCI (LGDBA) 143>

 

can anyone help me plz?

  • 1. Re: NEED HELP for ADD trandata issue
    mb_ogg Journeyer
    Currently Being Moderated

    Hi, a few things that are required for TRANDATA for SQL SErver 2008.

     

    First, you have to be on Enterprise Edition.

    Second, the database must be in FULL recovery and your transaction log must have some free space available.  Check this with DBCC SQLPERF (logspace)

    Third, when you run ADD TRANDATA in GGSCI, if you're using a Windows Authenticated DSN with DBLOGIN, like DBLOGIN SOURCEDB <dsnname>, the connection to the database to run ADD TRANDATA is actually running it through your Windows login, so you need to make sure that you are a member of the SQL Server instance's sysadmin role.  If you are using a SQL Server authenticated DSN with DBLOGIN, like DBLOGIN SOURCEDB <dsnname> USERID <user> PASSWORD <password>, then you need to make sure that this SQL Server login is a member of the instance's sysadmin role.

     

    Let me know if all of the above is true and if you still get this error after checking.

  • 2. Re: NEED HELP for ADD trandata issue
    ChowTerence Newbie
    Currently Being Moderated

    Hi mb_ogg

     

    thanks for your reply.

    I have 16 database need to add trandata

    I already check my database Environment and did it as you say. only 3 database can add trandata table. To another database the problem still like that when i using a windows DSN authentication.

    can you help me plz?

  • 3. Re: NEED HELP for ADD trandata issue
    mb_ogg Journeyer
    Currently Being Moderated

    Can you check the compatability mode for the databases that TRANDATA is not working?  I'm curious if the compatability mode is SQL Server 2005 or other.

     

    And could you give me more details of what you mean by this statement: "To another database the problem still like that when i using a windows DSN authentication".  Are you using all Windows Authenticated DSN's and only a few work, or are you using some with SQL Server authentication and those work but the Windows Authenticated ones do not?

  • 4. Re: NEED HELP for ADD trandata issue
    ChowTerence Newbie
    Currently Being Moderated

    Hi mb_ogg

     

    I check my database compatibility mode and make sure that all compatible with SQL Server 2008

    the commander as follow:

    GGSCI (LGDBA) 3> dblogin sourcedb qpbaohuangdb userid gguser password Loogame2

     

    2013-08-27 09:51:54  INFO    OGG-03036  Database character set identified as win

    dows-936. Locale: zh_Hans_CN.

     

    2013-08-27 09:51:54  INFO    OGG-03037  Session character set identified as GBK.

     

    Successfully logged into database.

     

    GGSCI (LGDBA) 4> list table *

    dbo.AspNet_SqlCacheTablesForChangeNotification

    dbo.GameRoundRecord

    dbo.GameScoreInfo

    dbo.GameScoreLocker

     

    Found 4 tables matching list criteria.

     

    GGSCI (LGDBA) 5> add trandata dbo.AspNet_SqlCacheTablesForChangeNotification

     

    2013-08-27 09:53:10  WARNING OGG-01483  The key for table [QPBaoHuangDB.dbo.AspN

    et_SqlCacheTablesForChangeNotification] contains one or more variable length col

    umns.  These columns may not have their pre-images written to the transaction lo

    g during updates.  Please use KEYCOLS to specify a key for Oracle GoldenGate to

    use on this table.

     

    2013-08-27 09:53:10  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 = o

    bject_id(N'dbo.AspNet_SqlCacheTablesForChangeNotification'))

       AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = o

    bject_id(N'dbo.AspNet_SqlCacheTablesForChangeNotification'))

    BEGIN

    DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id(N'dbo.AspNet_S

    qlCacheTablesForChangeNotification') as sysname)

    CREATE TABLE #ggsTabKeys (db sysname, name sysname, owner sysname, column_name s

    ysname, key_seq int, pk_name sysname)

    INSERT INTO #ggsTabKeys EXEC sp_pkeys 'AspNet_SqlCacheTablesForChangeNotificatio

    n', '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.AspNet_SqlCacheTablesForChangeNotificat

    ion')

        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.AspNet_SqlCacheTablesForChangeNotificat

    ion')

        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, '')

           ,'&lt;','<'),'&gt;','>'),'&amp;','&')

    execute sys.sp_cdc_enable_table

        @source_schema = N'dbo'

      , @source_name = N'AspNet_SqlCacheTablesForChangeNotification'

      , @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 directly SQL;No cursors。.

     

    2013-08-27 09:53:10  WARNING OGG-00782  Error in changing transaction logging fo

    r table: 'dbo.AspNet_SqlCacheTablesForChangeNotification'.

    ERROR: ODBC Error occurred. See event log for details..

     

    GGSCI (LGDBA) 6>

  • 5. Re: NEED HELP for ADD trandata issue
    mb_ogg Journeyer
    Currently Being Moderated

    Hi, can you post the output of the following sql statements after running from your SQL Server instance?

     

     

    select @@version

    go

    sqlperf(logspace)

    go


  • 6. Re: NEED HELP for ADD trandata issue
    ChowTerence Newbie
    Currently Being Moderated

    Hi

     

    when i running SELECT @@version and DBCC SQLPERF(LOGSPACE) the results as follow:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

     

    Database Name    Log Size (MB)    Log Space Used (%)    Status

    QPBaoHuangDB    263.6797    2.147529    0

  • 7. Re: NEED HELP for ADD trandata issue
    mb_ogg Journeyer
    Currently Being Moderated

    Ok, I think I may have found the problem.  When you created this database, did you restore it from a backup from another instance?  If so, then the owner of the db from the instance it was restored from is either not present on the new instance, or the login ID's are not the same.  Regardless, can you chnage the owner of the database to something else, like 'sa', with EXEC sp_changedbowner 'sa' and then re-run ADD TRANDATA.  Make sure that the correct database is listed in the query dropdown when you run the SQL command.

  • 8. Re: NEED HELP for ADD trandata issue
    ChowTerence Newbie
    Currently Being Moderated

    LOL

    thank you very much mb_ogg

    its work when i change my database owner to sysadministrator

    thanks a lot

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points