Stored Procedures Not Deleting During Deprovision in Dotmim.Sync

[nicolazreinh]

Description:
I am encountering an issue where, after performing a schema migration and deprovisioning the old metadata, the new metadata is not being updated correctly. I have followed the process to deprovision, drop, and provision metadata, but the old stored procedures and triggers seem to remain, and the changes are not reflected as expected.

Here is the process I am following for schema migration:

1. Apply the update script on the client side.
2. Remove all metadata from the client side.
3. Update the database on the server side, which includes:
   • Updating the database schema.
   • Regenerating the scope info to reflect the new metadata.
   • Deprovisioning the old metadata.
   • Provisioning again with the new schema.
4. Sync the database on the client side to apply the changes.

I am also calling the following methods to remove metadata:

await agent.LocalOrchestrator.DropAllAsync();
var deprovisionScopeStoredProcedures = await agent.LocalOrchestrator.DeprovisionAsync(syncScope, SyncProvision.StoredProcedures);
var deprovisionScopeInfo = await agent.LocalOrchestrator.DeprovisionAsync(syncScope, SyncProvision.ScopeInfo);
var deprovisionScopeInfoClient = await agent.LocalOrchestrator.DeprovisionAsync(syncScope, SyncProvision.ScopeInfoClient);
var deprovisionTriggers = await agent.LocalOrchestrator.DeprovisionAsync(syncScope, SyncProvision.Triggers);
var deprovisionScopeTrackingTable = await agent.LocalOrchestrator.DeprovisionAsync(syncScope, SyncProvision.TrackingTable);

Despite following these steps, the metadata, especially the stored procedures, is not updating correctly, and I encounter the following error when syncing:

Procedure or function tbl_example_dbotestexample_update has too many arguments specified.

Steps to Reproduce:

1. Update the database schema.
2. Deprovision and provision metadata using the process above.
3. Attempt to sync the database and observe that old metadata is still present, causing the sync to fail with the error mentioned.

Expected Behavior:
The old metadata should be removed, and new metadata (such as stored procedures and triggers) should be correctly provisioned after a schema update, allowing sync to proceed without errors.

Actual Behavior:
The metadata does not update as expected after deprovisioning and provisioning, and old stored procedures and triggers remain, resulting in sync errors.

Question:
Am I missing any steps or doing something wrong in this process? Is there a different approach I should be using to ensure the metadata is correctly updated after a schema change?

Additional Context:
We are using SQL Server and leveraging change tracking for client-server synchronization. The goal is to ensure that after any database schema updates, the metadata is regenerated properly.

Environment:

• Dotmim.Sync version: 1.0.2
• Database provider: SQL Server
• Synchronization mode: Web Architecture
• Using Change Tracking

[Mimetis]

await agent.LocalOrchestrator.DropAllAsync(); should remove everything.
Is it the case ?

Once you have called this method, do you still have some stored procedures in the database ?

If yes, are these non deleted procedures related to a scope name that does not exists in the scope table ?

[nicolazreinh]

I still see the stored procedures after calling that method. Yes, there are scopes for the stored procedures.

[Mimetis]

I think the scope name record is not any more in the scope table.
That's why the stored procedures are not deleted

DropAllAsync takes all the scope name records and make a drop routine for each scope
If the scope is not in table, then it's not dropped

The question is "Why the scope record has been dropped, and not the stored procedures"
I don't know

[nicolazreinh]

I may not need the DropAllAsync in the other approach.