Different stored procedures depending on database schema

[maprado-inetum]

I have encountered this problem using version 1.2.0 of the DMS framework and the provider 'SqlSyncChangeTrackingProvider'.

Provisioning a table located in the default schema of the database (in this case 'dbo') using a 'CustomWhere' to filter the synchronization by a particular field, it is observed that the following stored procedures are created, including those necessary to select the changes by the configured filter.

On the other hand, if the table to be synchronized is in another schema of the database (such as the 'js' schema), when provisioning the table in the same way and with the same filter, the necessary stored procedures are not created to filter the changes by the configured parameter.

Any idea why this is happening?

I need to use the 'js' schema to provision all the procedures to be able to filter by the configured parameter.

[maprado-inetum]

Can you help me please?

[maprado-inetum]

Hi.
I have created a repository with a console project where I reproduce what I explain above.

https://github.com/maprado-inetum/DotmimSyncExamples

Importing first in SQL Server the two databases (ServerDatabase and ClientDatabase) and running the offered console, you can see how in the generated stored procedures are missing those that filter by the condition written in the 'CustomWhere'.

[Mimetis]

Hey @maprado-inetum
Thanks a lot to provide a sample, much appreciated !

Here is the sql setup I used (based on your backups):

USE [master]
GO 
-- Server database
if (exists (select * from sys.databases where name = 'ServerDatabase'))
Begin
	ALTER DATABASE [ServerDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ServerDatabase]
End
Create database [ServerDatabase]
Go
-- Client database. No need to create the schema, Dotmim.Sync will do
if (exists (select * from sys.databases where name = 'ClientDatabase'))
Begin
	ALTER DATABASE [ClientDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [ClientDatabase]
End
Create database [ClientDatabase]
GO
USE [ServerDatabase]
GO
CREATE SCHEMA [js]
GO
CREATE TABLE [js].[OnDemandJobs](
	[Id] [uniqueidentifier] NOT NULL PRIMARY KEY  DEFAULT (newsequentialid()),
	[AssetAlias] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[Server] [nvarchar](max) NULL,
	[User] [nvarchar](max) NULL,
	[Parameters] [nvarchar](max) NULL,
	[Executed] [bit] NOT NULL,
	[ExecutionDurationInSeconds] [int] NULL,
	[ExecutionTimeUtc] [datetime2](7) NULL,
	[ExecutionMessage] [nvarchar](max) NULL
)
GO

USE [ClientDatabase]
GO
CREATE SCHEMA [js]
GO
CREATE TABLE [js].[OnDemandJobs](
	[Id] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newsequentialid()),
	[AssetAlias] [nvarchar](max) NULL,
	[Name] [nvarchar](max) NULL,
	[Server] [nvarchar](max) NULL,
	[User] [nvarchar](max) NULL,
	[Parameters] [nvarchar](max) NULL,
	[Executed] [bit] NOT NULL,
	[ExecutionDurationInSeconds] [int] NULL,
	[ExecutionTimeUtc] [datetime2](7) NULL,
	[ExecutionMessage] [nvarchar](max) NULL
) 
GO
ALTER DATABASE [ServerDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON  
ALTER DATABASE [ServerDatabase] SET READ_COMMITTED_SNAPSHOT ON  
ALTER DATABASE [ServerDatabase] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)

ALTER DATABASE [ClientDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON  
ALTER DATABASE [ClientDatabase] SET READ_COMMITTED_SNAPSHOT ON  
ALTER DATABASE [ClientDatabase] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)

I didn't changed the code, as it's running correctly.

And, indeed, the stored procedures are not created, because the SetupFilter object should take, as parameters, the table name and the schema name:

SetupFilter onDemandJobsFilter = new("OnDemandJobs", "js");

That way, the stored procedures are correctly created:

USE [ServerDatabase]
GO
/****** Object:  StoredProcedure [js].[OnDemandJobs_OnDemandJobsSync_InstallationAliaschanges]    Script Date: 18/12/2024 09:28:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [js].[OnDemandJobs_OnDemandJobsSync_InstallationAliaschanges]
	@sync_min_timestamp bigint,
	@sync_scope_id uniqueidentifier,
	@InstallationAlias nvarchar (MAX)
AS
BEGIN
;WITH 
  [OnDemandJobs_tracking] AS (
	SELECT [CT].[Id], 
	CAST([CT].[SYS_CHANGE_CONTEXT] AS uniqueidentifier) AS [sync_update_scope_id],
	[CT].[SYS_CHANGE_VERSION] AS [sync_timestamp],
	CASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone],
	[CT].[SYS_CHANGE_COLUMNS] AS [sync_change_columns]
	FROM CHANGETABLE(CHANGES [js].[OnDemandJobs], @sync_min_timestamp) AS [CT]
	)
SELECT DISTINCT
	[side].[Id], 
	[base].[AssetAlias], 
	[base].[Name], 
	[base].[Server], 
	[base].[User], 
	[base].[Parameters], 
	[base].[Executed], 
	[base].[ExecutionDurationInSeconds], 
	[base].[ExecutionTimeUtc], 
	[base].[ExecutionMessage], 
	[side].[sync_row_is_tombstone],
	[side].[sync_update_scope_id]
FROM [js].[OnDemandJobs] [base]
RIGHT JOIN [OnDemandJobs_tracking] [side]ON [base].[Id] = [side].[Id]
WHERE (
(
  [base].[AssetAlias] = @InstallationAlias OR [side].[sync_row_is_tombstone] = 1
)
AND 
	[side].[sync_timestamp] > @sync_min_timestamp
	AND ([side].[sync_update_scope_id] <> @sync_scope_id OR [side].[sync_update_scope_id] IS NULL)
)

END

Let me know if it's resolving your issue.

[maprado-inetum]

Thank you for the contribution and the quick response, but I am very sorry to say that the procedures are not created for me:

• [js].[OnDemandJobs_OnDemandJobsSync_InstallationAliasinitialize]
• [js].[OnDemandJobs_OnDemandJobsSync_InstallationAliaschanges]

Could the problem lie in the SQL Server version? I use 14.0.3485.1

I have uploaded to the test repository the change to add the schema in the 'setupFilter' for you to see.
I have also replaced the .bak with the script you have shared.
Thanks

[Mimetis]

that's incorrect:

 string tableName = $"js.OnDemandJobs";

// Filters
SetupFilter onDemandJobsFilter = new(tableName, "js");

Should be :

 string tableName = $"js.OnDemandJobs";

// Filters
SetupFilter onDemandJobsFilter = new("OnDemandJobs", "js");

Or even

string tableName = $"OnDemandJobs";
string schemaName = $"js";

// Filters
SetupFilter onDemandJobsFilter = new(tableName, schemaName);

[maprado-inetum]

It works! I have uploaded to the repository the change I made to make it work.
Thanks a lot!