8 Replies Latest reply: Aug 28, 2013 8:15 PM by ChowTerence RSS

    NEED HELP for ADD trandata issue

    ChowTerence

      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
          Mackbell-Oracle

          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

            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
              Mackbell-Oracle

              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

                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
                  Mackbell-Oracle

                  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

                    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
                      Mackbell-Oracle

                      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

                        LOL

                        thank you very much mb_ogg

                        its work when i change my database owner to sysadministrator

                        thanks a lot