SQL Data Compare Cannot uniquely identify row in table in command line

DJackman

I have a hosted database in AWS that I want to keep a backup copy of on premise. 

I don't own the AWS service so don't have any admin rights on this, just a database connection and the owners have given their consent to me taking a daily backup copy.

I have a SQL Compare and SQL Data Compare project and these both work fine in the GUI and complete without any errors.

If I use Task Scheduler to run this from a batch file at the command line with administrator privileges the SQL Data Compare project fails consistently with

"C:\Program Files (x86)\Red Gate\SQL Data Compare 14\SQLDataCompare.exe" /project:D:\*********\LiveDataCompare.scp /sync /logLevel:Verbose

SQL Data Compare Command Line V14.0.0.12866
======================================================================================================================
Copyright Copyright © Red Gate Software Ltd 2019

SQL Data Compare: activated, edition professional
Registering databases
Mapping
Comparing databases
Generating SQL scripts
Error: Cannot uniquely identify row in table '[dbo].[menu]' because there is no primary key or unique index defined
on it. Output script generation failed.
Cannot uniquely identify row in table '[dbo].[menu]' because there is no primary key or unique index defined on it. Output script generation failed.
RedGate.Legacy.CommandLine.FatalExecutionException
   at RedGate.SQLToolsCommandLine.FatalExecutionExceptionHelper.Throw(ExitCodes exitCode, Exception e)
   at RedGate.SQLDataCompare.CommandLine.DataCommandProcessor.ProcessThreadException(Exception e)
   at RedGate.SQLToolsCommandLine.CommandProcessor.ProcessThreadException()
   at RedGate.SQLToolsCommandLine.CommandProcessor.RunComparison()
   at RedGate.SQLToolsCommandLine.CommandProcessor.ProcessCommands()
   at RedGate.SQLToolsCommandLine.CommandProcessor.Run()

There is no primary key on that table, and I can't alter the source database. 

I have checked at the time this runs and errors and there is no duplicate row in that table or any ntext fields, and running the project through the GUI it completes fine.

This is the structure of that table

CREATE TABLE [dbo].[menu](
    [code] [VARCHAR](50) NOT NULL,
    [cat] [VARCHAR](12) NOT NULL,
    [ncat] [VARCHAR](32) NOT NULL,
    [staging] [NUMERIC](1, 0) NULL,
    [bln] [VARCHAR](32) NULL,
    [old] [VARCHAR](32) NULL,
    [version] [VARCHAR](8) NULL,
    [versionn] [NUMERIC](6, 0) NULL,
    [created] [DATETIME] NULL,
    [updated] [DATETIME] NULL,
    [status] [NUMERIC](2, 0) NULL,
    [message] [VARCHAR](255) NULL
) 

Tianjiao_Li

Hi, 

You can specify comparison key on command line:

https://documentation.red-gate.com/sdc/using-the-command-line/command-line-syntax/switches-used-in-the-command-line#Switchesusedinthecommandline-/ComparisonKeys:%3Ctableorviewnameasregularexpression%3E:%3Cindexname%3E

To specify a comparison key that's not an index, use the GUI to set up and save a project with the settings you require. You can then use that project from the command line with the /Project switch.

DJackman

Thanks for this, has been working without fault now since applying the comparison key