Unable to sync with a unique identifier primary key and a separate identity column

[mattpawson]

Hi,

I am unable to sync a table in SQL Server that uses a unique identifier as the primary key but also has an identity column.

Steps to recreate:

Database creation:

USE [master] GO /****** Object: Database [SyncServer] Script Date: 5/16/2024 12:54:29 AM ******/ CREATE DATABASE [SyncServer] CONTAINMENT = NONE ON PRIMARY ( NAME = N'SyncServer', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SyncServer.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'SyncServer_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SyncServer_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) WITH CATALOG_COLLATION = DATABASE_DEFAULT GO ALTER DATABASE [SyncServer] SET COMPATIBILITY_LEVEL = 150 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [SyncServer].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [SyncServer] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [SyncServer] SET ANSI_NULLS OFF GO ALTER DATABASE [SyncServer] SET ANSI_PADDING OFF GO ALTER DATABASE [SyncServer] SET ANSI_WARNINGS OFF GO ALTER DATABASE [SyncServer] SET ARITHABORT OFF GO ALTER DATABASE [SyncServer] SET AUTO_CLOSE OFF GO ALTER DATABASE [SyncServer] SET AUTO_SHRINK OFF GO ALTER DATABASE [SyncServer] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [SyncServer] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [SyncServer] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [SyncServer] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [SyncServer] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [SyncServer] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [SyncServer] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [SyncServer] SET DISABLE_BROKER GO ALTER DATABASE [SyncServer] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [SyncServer] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [SyncServer] SET TRUSTWORTHY OFF GO ALTER DATABASE [SyncServer] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [SyncServer] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [SyncServer] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [SyncServer] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [SyncServer] SET RECOVERY SIMPLE GO ALTER DATABASE [SyncServer] SET MULTI_USER GO ALTER DATABASE [SyncServer] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [SyncServer] SET DB_CHAINING OFF GO ALTER DATABASE [SyncServer] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [SyncServer] SET TARGET_RECOVERY_TIME = 60 SECONDS GO ALTER DATABASE [SyncServer] SET DELAYED_DURABILITY = DISABLED GO ALTER DATABASE [SyncServer] SET ACCELERATED_DATABASE_RECOVERY = OFF GO ALTER DATABASE [SyncServer] SET QUERY_STORE = OFF GO USE [SyncServer] GO /****** Object: UserDefinedTableType [dbo].[Customers_customers_BulkType] Script Date: 5/16/2024 12:54:30 AM ******/ CREATE TYPE [dbo].[Customers_customers_BulkType] AS TABLE( [Id] [uniqueidentifier] NOT NULL, [Reference] [int] NULL, [Name] [nvarchar](200) NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ) GO /****** Object: Table [dbo].[Customers] Script Date: 5/16/2024 12:54:30 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customers]( [Id] [uniqueidentifier] NOT NULL, [Reference] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](200) NOT NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[Customers] ON GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'9a2ce174-8784-4b3b-8ea0-55ab81d94795', 2, N'Customer #2') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'74a18309-609e-465f-8c87-59f1b722cb58', 6, N'Customer #6') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'ad2c66a3-9968-4ef6-a7eb-5a62b6e4d5ae', 1, N'Customer #1') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'2312a178-47af-4074-886c-71b96f1a405a', 7, N'Customer #7') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'b0b61dd0-7cb7-4235-a0b3-7491a2a68b89', 5, N'Customer #5') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'7cf0febc-5ca7-4e54-abd0-83c8a095a80d', 8, N'Customer #8') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'e79ecd82-3550-4990-bb45-9b080c53c775', 9, N'Customer #9') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'32e9b482-b9a6-4fcd-924b-e7298b392a37', 4, N'Customer #4') GO INSERT [dbo].[Customers] ([Id], [Reference], [Name]) VALUES (N'e6328759-451a-4690-ad74-fea050839939', 3, N'Customer #3') GO SET IDENTITY_INSERT [dbo].[Customers] OFF GO USE [master] GO ALTER DATABASE [SyncServer] SET READ_WRITE GO

Code:
using Dotmim.Sync; using Dotmim.Sync.Enumerations; using Dotmim.Sync.SqlServer; var serverConnectionString = "SERVER_CONNECTION_STRING"; var clientConnectionString = "CLIENT_CONNECTION_STRING"; var serverProvider = new SqlSyncProvider(serverConnectionString); var clientProvider = new SqlSyncProvider(clientConnectionString); var setupCustomers = new SyncSetup( "Customers" ); var progress = new SynchronousProgress<ProgressArgs>(s => Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}") ); var syncOptions = new SyncOptions { ErrorResolutionPolicy = ErrorResolution.ContinueOnError }; var agent = new SyncAgent(clientProvider, serverProvider); result = await agent.SynchronizeAsync("customers", setupCustomers, progress); Console.WriteLine(result); Console.WriteLine("End");

The exception thrown is:

Dotmim.Sync.SyncException: '[InternalApplyChangesAsync]..Error on table [Customers]: Procedure or function 'Customers_customers_update' expects parameter '@reference', which was not supplied.. Row:[Sync state]:ApplyModifiedFailed, [Id]:9a2ce174-8784-4b3b-8ea0-55ab81d94795, [Name]:Customer #2. ApplyType:Modified'

It looks like it's not selecting the reference column in the data.

[Mimetis]

DMS does not work if you have an auto increment column that is not a primary key

[mattpawson]

Any way round this? I tried removing the auto increment from the column to re-add in later but it still didn't work.

[Mimetis]

you can probably workaround, I guess, but you need to remove auto inc (or make this auto inc a primary key)