SQLDataCompare can not create script for table with ntext

dominic99

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)

Brian Donahue

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.

dominic99

The table has index on column Version.
The table has only 1 row

Brian Donahue

Hello,

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

Thanks.

dominic99

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 Donahue

Sorry -- is this the script for [dbo].[tb_SharedConfig]? This seems to be a script for an entirely different table...

dominic99

Yes.. This is script for table tb_SharedConfig

I`m rename table (and all indexes)

tb_SCg = tb_SharedConfig

Brian Donahue

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.

dominic99

No. I use dll from my own application (we has developer license).
Field Config contain an XML file.

Brian Donahue

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));
}