SQLDataCompare can not create script for table with ntext

dominic99dominic99 Posts: 6
edited December 7, 2005 11:10AM in SQL Data Compare Previous Versions
In ntext field xml file is placed
Structure of table:
Version int 4 - primary key, identity
Config ntext 16 - not null
IpAdress varchar 100 - not null
UserId smallint 2 - not null
DateCreated datetime 8 - null

Index (clustered) on column Version (order Descending)

The following exception received:
RedGate.SQLDataCompare.Engine.SqlProviderException: Cannot uniquely identify row in table '[dbo].[tb_SharedConfig]' because there is no primary key or unique index defined on it. Migration script generation failed.
at RedGate.SQLDataCompare.Engine.SqlProvider.WhereClause(TableComparisonSetting table, Row r)
at RedGate.SQLDataCompare.Engine.SqlProvider.AddValueToString(StringBuilder values, Object value, String tableName, String columnName, TableComparisonSetting table, Row row, Boolean& largeData, StringBuilder largeSQL, SqlField field)
at RedGate.SQLDataCompare.Engine.SqlProvider.WriteAddSQL(ExecutionBlock block, SmallFields fields, TableComparisonSetting table, StringBuilder largeSQL, StringBuilder values, String tableName, String fieldNames, Row row)
at RedGate.SQLDataCompare.Engine.SqlProvider.WriteAddSQL(ExecutionBlock block, ComparisonSession session, TableDifference summary, ResultsStore rs, ResultsStoreType resultsStoreType, Boolean runOnTwo, Int64 recordCount, SelectionDelegate select, Int64& recordPosition)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, SelectionDelegate select, Boolean runOnTwo)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, Boolean runOnTwo)
at dbupdate.BackupDB.CreateBackup(String serverName, String databaseName, Boolean integratedSecurity, String username, String password, String backupName, String backupDirectory)

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Thanks for your post. This is certainly interesting. Data Compare cannot update tables BLOB column if there is no unique index on the table. Data Compare seems to have given the green light to these tables for comparison, but it falls over when generating the script.

    Is there a possibility that the table is corrupted somehow and there are duplicate values in the primary key column? This would explain why DC allowed the table to be compared, but cannot generate a script for it.
  • The table has index on column Version.
    The table has only 1 row :(
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    would you mind sending a SQL script of the table, including the indexes and keys?

    Thanks.
  • This is SQL script for table
    CREATE TABLE [dbo].[tb_SCg] (
    [Version] [int] IDENTITY (1, 1) NOT NULL ,
    [Config] [ntext] COLLATE Latin1_General_CI_AI NOT NULL ,
    [IpAddress] [varchar] (100) COLLATE Latin1_General_CI_AI NOT NULL ,
    [UserId] [smallint] NOT NULL ,
    [DateCreated] [datetime] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tb_SCg] WITH NOCHECK ADD
    CONSTRAINT [PK_tb_SCg] PRIMARY KEY CLUSTERED
    (
    [Version] DESC
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tb_SCg] ADD
    CONSTRAINT [DF_tb_SCg_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
    GO
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Sorry -- is this the script for [dbo].[tb_SharedConfig]? This seems to be a script for an entirely different table...
  • Yes.. :) This is script for table tb_SharedConfig

    I`m rename table (and all indexes)

    tb_SCg = tb_SharedConfig
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Is this a SQL Toolkit application? Is it comparing two databases using a saved SDC project file? Because if you are using a saved project and making schema changes on the tables, the settings in the project file will become invalid and you will get this kind of error.

    We'll probably need a bit more information about this project and its' code to make any other suggestions if this isn't the problem.
  • No. I use dll from my own application (we has developer license).
    Field Config contain an XML file.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Exactly. You are not using the RedGate.SQLDataCompare.UI.exe, you are using RedGate.SQLDataCompare.Engine.dll in your own application called 'dbupdate'.

    Using the dll directly allows you to specify the primary key fields directly in your code. It's possible that the columns that you have chosen as the primary key do not contain unique data. You can specify the column used as the basis of a comparison in the TableComparisonSetting, for instance:
    TableComparisonSettings settings=new TableComparisonSettings();
    
    foreach (Table table in commonTables)
    {
    	settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields));
    }
    
This discussion has been closed.