I am working on a PowerShell function to use in automating execution of Data Compare from within a SQL Server job. I am doing this by dynamically building the sqldatacompare.exe command line call, and everything seems to be working great except that I am getting differences on one particular text column with trailing spaces, even though I am defining the TrimTrailingSpaces option in the command line call.

Here is the full command line call being generated with database/server names removed

You can see the one identical row is completely filling the column, however the trailing space(s) on the left(source database) are being marked a difference when compared to the right (target database). The column is a VARCHAR(50) in both tables.

Is there a switch or option I am defining that is causing TrimTrailingSpaces to be ignored?

When I do the exact same compare in the GUI with the same options (Trim Trailing Spaces, etc.) it returns all rows as identical.

Here is the version returned by sqldatacompare.exe
Red Gate SQL Data Compare Command Line Utility V9.1.0.365

I've tested this with the same version, and the option appears to be working as expected. I tried seeing if the problem might be to do with /include switch or the /excludecolumns switch, but it seemed to still work for me.

Have you tried running the command directly from a command prompt and see if that makes any difference.

Would you be able to post the DDL for the table and I'll try it with the rows of data you posted.

I tried your idea of running the generated command manually in a regular command line environment and it actually printed that it was running with the mapping options and ignored the trailing spaces as expected.

After that I ran it manually in the powershell command line the same way and I noticed that it is only printing that it's using the first option specified after /Options: and not reading anything after the first comma.

I fixed it in the PowerShell script by separately declaring each option in its own switch like so:

With this command in PowerShell it's now behaving as expected. I imagine this is related to PowerShell not liking parsing the commas when passing the parameters in to the command. I tooled around with it more and found that wrapping the combined list in double quotes also works like so: