Get Data Moving 1
Results 1 to 7 of 7

Thread: Error message

  1. #1
    iamhasib is offline Junior Member
    Join Date
    Dec 2014
    Posts
    7
    Rep Power
    0

    Error message

    Running SQL server to Redshift data load and one table failed with below error message. please shed lights..

    Table 'dbo.T' does not have supported data types
    Metadata Manager table definition cannot be found. Table ID: 86. Component ID: compass-sql
    Cannot retrieve source table (86) metadata
    Error encountered while FETCH-ing table's data.
    Endpoint is disconnected
    Error executing source loop
    Stream component failed at subtask 3, component st_3_compass-sql
    Stream component 'st_3_compass-sql' terminated
    Table 'dbo.T' does not have supported data types
    Unable to get table_def for capture table 86
    SQL Server source. Cannot init table workspace for iMetaDataTableid- '86'
    Table 'dbo.T' does not have supported data types
    Metadata Manager table definition cannot be found. Table ID: 86. Component ID: compass-sql
    Cannot retrieve source table (86) metadata

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    Quote Originally Posted by iamhasib View Post
    Running SQL server to Redshift data load and one table failed with below error message. please shed lights..

    Table 'dbo.T' does not have supported data types
    Metadata Manager table definition cannot be found. Table ID: 86. Component ID: compass-sql
    Cannot retrieve source table (86) metadata
    Error encountered while FETCH-ing table's data.
    Endpoint is disconnected
    Error executing source loop
    Stream component failed at subtask 3, component st_3_compass-sql
    Stream component 'st_3_compass-sql' terminated
    Table 'dbo.T' does not have supported data types
    Unable to get table_def for capture table 86
    SQL Server source. Cannot init table workspace for iMetaDataTableid- '86'
    Table 'dbo.T' does not have supported data types
    Metadata Manager table definition cannot be found. Table ID: 86. Component ID: compass-sql
    Cannot retrieve source table (86) metadata
    ===
    What is this table "Table 'dbo.T'" and that is the datatypes?

    Does this table datatype supported on Redshift?

  3. #3
    iamhasib is offline Junior Member
    Join Date
    Dec 2014
    Posts
    7
    Rep Power
    0
    One of the table failed is bill_costs, and the scehma is ..

    CREATE TABLE [dbo].[bill_costs] (
    [bill_costid] numeric(20) NULL ,
    [invoiced] int NULL ,
    [fkinvoiceno] int NULL ,
    [starttime] datetime2(7) NULL ,
    [fkclientid] int NULL ,
    [fkcalltypeid] int NULL ,
    [fkcalltypeid_wsale] int NULL ,
    [dailycalc] int NULL ,
    [fkphoneid] int NULL ,
    [fkowi_calldataid] bigint NULL ,
    [calculatedcost] float(53) NULL ,
    [totalled] int NOT NULL
    )
    ON [SECONDARY]
    WITH (DATA_COMPRESSION = PAGE)
    GO

    CREATE CLUSTERED INDEX [CIDX_BillCosts_BillCostID] ON [dbo].[bill_costs]
    ([bill_costid] ASC)
    WITH (
    DATA_COMPRESSION = PAGE
    )
    ON [SECONDARY]
    GO

    CREATE INDEX [IDX_BillCosts_StartTime] ON [dbo].[bill_costs]
    ([starttime] ASC)
    INCLUDE ([bill_costid], [fkclientid], [fkphoneid], [calculatedcost])
    WITH (
    DATA_COMPRESSION = PAGE
    )
    ON [SECONDARY]
    GO

    CREATE INDEX [IDX_BillCosts_fkClientID_fkPhoneID] ON [dbo].[bill_costs]
    ([fkclientid] ASC, [fkphoneid] ASC)
    INCLUDE ([fkinvoiceno], [fkcalltypeid], [fkowi_calldataid], [calculatedcost])
    WITH (
    DATA_COMPRESSION = PAGE
    )
    ON [SECONDARY]
    GO

    CREATE INDEX [IDX_BillCosts_fkowiCallDataID_BillCostID_fkPhoneID _CalculatedCost_itemisationReport] ON [dbo].[bill_costs]
    ([fkowi_calldataid] ASC, [bill_costid] ASC, [fkphoneid] ASC, [calculatedcost] ASC)
    INCLUDE ([fkcalltypeid], [fkcalltypeid_wsale])
    WITH (
    DATA_COMPRESSION = PAGE
    )
    ON [SECONDARY]
    GO

  4. #4
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    I have tested with a simple table like your:

    USE [demo2]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[supportdata_test](
    [bill_costid] [numeric](20, 0) NULL,
    [invoiced] [int] NULL,
    [fkinvoiceno] [int] NULL,
    [starttime] [datetime2](7) NULL,
    [fkclientid] [int] NULL,
    [fkcalltypeid] [int] NULL,
    [fkcalltypeid_wsale] [int] NULL,
    [dailycalc] [int] NULL,
    [fkphoneid] [int] NULL,
    [fkowi_calldataid] [bigint] NULL,
    [calculatedcost] [float] NULL,
    [totalled] [int] NOT NULL
    ) ON [PRIMARY]

    GO
    ====
    And Replication without out issue.

    Therefore, it could be something with the SQL / Table, could you provide more information about your SQL, version, cluster, etc?

    Could you try a simple table from my testing, does it work for you?

  5. #5
    iamhasib is offline Junior Member
    Join Date
    Dec 2014
    Posts
    7
    Rep Power
    0
    Do you think it could be that the field holds null date value?

  6. #6
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    I tested with null value for the date record and it work.

    Therefore, it could be something else, does your date have '0000-00-00 00:00:00', if so this is not supported on Redshift.

    Then you would use transformation to convert '0000-00-00 00:00:00' to a timestamp that Redshift support.

    Below is an example to transform the Timestamp:

    if you have a column date_time, you would have a transform for replace
    replace($date_time,'0000-00-00 00:00:00','1971-01-01 00:00:00')

    Notes that you could change the '1971-01-01 00:00:00' to more current date and time, it does not need to be 1971.....
    Last edited by stevenguyen; 04-02-2015 at 10:07 AM.

  7. #7
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    If you really need the values to be NULL which is valid in Redshift.

    you can try the below:

    nullif($FIELD,value), in this case it would be:

    nullif($mycol,'0000-00-00 00:00:00')

    This will return NULL if $mycol equals the value you specified.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •