Special-character table names break Dotmim Sync: any fixes or alternatives?

[parsie21]

Hi everyone,

I’m a bachelor’s student on my first internship, and I’ve been assigned a project to synchronize two databases. The results will form the basis of my thesis.

After reading the Dotmim Sync (DMS) documentation, I built a test environment with the classic AdventureWorks databases. The code compiles, and synchronization finishes successfully.

The problems start when I switch to the company’s production databases. The sync fails with the runtime error shown below. My colleagues and I believe the issue is caused by table and column names that include special characters—such as hyphens (-) or spaces—which DMS doesn’t seem to handle. Renaming these objects isn’t an option because it would break the company’s existing application.

Using a dummy database for my thesis wouldn’t bother me, but it would be a waste for the company if we can’t use the real data.

Questions

1. Is there a way to make Dotmim Sync work with table/column names that contain spaces or special characters?
2. If not, can you recommend any alternative libraries? (Please don’t suggest Sync Framework; that’s what the company is moving away from.)
3. Could creating views with compliant names and syncing against those, instead of the underlying tables, solve the problem?

Runtime error

Dotmim.Sync.SyncException: 
    [InternalProvisionServerAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:False
    ..[InternalProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:False
    ..[InternalCreateStoredProceduresAsync].Table: ana_Clienti.Overwrite:False
    ..[InternalCreateStoredProcedureAsync].Table: ana_Clienti.StoredProcedure: UpdateRow
    ..Incorrect syntax near '-'.
Incorrect syntax near 'nvarchar'.

I'm quite stupid so be kind. Thanks in advance for any guidance!

[Mimetis]

Spaces should work as expected, but not sure about the hyphens (-)
Do you have a simple example you can share that breaks ?

[parsie21]

Hi,

I’ve just arrived home and don’t have access to the company PC (until Monday), so I cannot dig into every detail right now. That said, the table triggering the error contains a column named E-mail, and my colleagues and I suspect the hyphen is what is breaking the synchronization. They also warned me there may be other columns with special characters—accented letters such as è or à, and possibly more.

Unfortunately, the database is old, undocumented, and filled with columns like these, and the company understandably can’t rename them because it would break the existing application. So, renaming is not an option.

My supervisor asked whether we could create SQL Server views with “clean” column names and point Dotmim Sync at those instead. Is that approach supported? If not, could you suggest an alternative workaround or solutions?

If you would like to have more precise informations, let me know.

Thank you very much for your help.

[Mimetis]

Views won't work
The best I can do is to "fix" these issues if it's possible
The best thing to do is to list the special characters you have in your databases, either in table names and columns names.
Once we have that list, I will create a sample table that have all these specific characters and will try to add the feature in DMS

Let's see if it can work

[parsie21]

Thanks for the quick reply. I’ll go over this with my company tutor on Monday and get back to you.

[parsie21]

Views won't work. The best I can do is to “fix” these issues if it’s possible. The most useful thing is to list the special characters currently present in the database—both in table names and column names. Once we have that list, I’ll create a sample table containing all of them and attempt to add the feature in DMS.

Let’s see if it can work.

Hi, as promised I ran a script and extracted all the special / non-alphanumeric characters used in the table and column names. Below is the list with their common names:

Character	Name
(space)	space
%	percent sign
'	apostrophe / single quote
-	hyphen
.	period (dot)
à	a with grave
ì	i with grave
ò	o with grave
ù	u with grave
è	e with grave
é	e with acute

Example of the column names containing those characters:

Thanks again for your support—if you need any more details I’d be glad to help, and I look forward to hearing from you soon.
@Mimetis

[parsie21]

@Mimetis

Hello,
Sorry to bother you again, but I’ve encountered a new issue related to a column name, and I was hoping to get your input.
The column that's causing the problem is:

[Identificativo reso S.F.]

Exception Message

Dotmim.Sync.SyncException: '[ProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:True.. [InternalProvisionServerAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:True.. [InternalProvisionAsync].Provision:TrackingTable, StoredProcedures, Triggers.Overwrite:True.. [InternalCreateStoredProceduresAsync].Table:ven_Cassa.Overwrite:True.. [InternalCreateStoredProcedureAsync].Table:ven_Cassa.StoredProcedure:BulkUpdateRows.. Invalid column name 'F'. Invalid column name 'F'.'

From the error, it seems like DMS is misinterpreting the column name — possibly treating "S.F." as two separate identifiers instead of part of a single column name.

Do you think this issue can be resolved by adjusting something in the DMS configuration or code, without having to rename the column?

Thanks in advance for your help!

[Mimetis]

I've made the test

        [Fact]
        public void ColumnParser_Parse_WhiteSpecials_Double_Dot_Characters_ShouldWork()
        {
            var columnParser = new ObjectParser("[Identificativo reso S.F.]", '[', ']');

            Assert.Equal("Identificativo reso S.F.", columnParser.ObjectName);
            Assert.Equal("", columnParser.OwnerName);
            Assert.Equal("[Identificativo reso S.F.]", columnParser.QuotedShortName);
            Assert.Equal("Identificativo_reso_S_F_", columnParser.NormalizedShortName);
        }

And it seems it's working fine

Did you try without the last "." ? Something like [Identificativo reso S.F]

[parsie21]

Nope, in my configuration i only give the tables names that need to be synced without focusing on the columns names. Furthermore i am not going to change my code right now cause i'm at the end of my internship and i'm focusing only on the documentation.

Anyway, the software house i'm working in seems interested in using what i've created during my time with them, so i thought i could ask you for an advice on how to manage this issue in the future.
From what i've understood, you suggest to put [Identificativo reso S.F] instead of [Identificativo reso S.F.] during setup configuration, where the only difference is that we eliminate the last dot at the end of the column name.
Correct me if wrong.

As of now, i'll just leave this table out of the setup.

Thank you once again for your support and for your fantastic work

[parsie21]

I tried to use this [Identificativo S.F] instead of [Identificativo S.F.] as you said but it still doesn't work

At least i can synchronize the whole table (without the [Identificativo S.F.] column). Still, that field is quite important so they'll have to find a solution at some point at some point if they'll use my solution

[Mimetis]

That's weird, seems it's working for me
I'll double check tomorrow