Data Compare 9 command line ignoring TrimTrailingSpaces

swelsh

Hi,

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

sqldatacompare.exe /v /s1:server1 /db1:database1 /s2:server2 /db2:database2  /include:Identical  /include:table:cbsa /excludeColumns:cbsa:row_created /options:IgnoreSpaces,IncludeTimestamps,IncludeIdentities,DisableKeys,OutputComments,TrimTrailingSpaces /Export:C:\swelsh\RedGate\Export

This is the contents of Results Summary.csv from the export switch
"Object Type","Object Owner","Object Name","database1 Only","Different","database2 Only","Object Name","Object Owner","All Identical"
"Table","dbo","cbsa","0","983","0","cbsa","dbo","1"


And this is the first two rows of the table compare results

"Type","code_k","type_s","type_t","title_s","title_t","level_s","level_t","status_s","status_t"
"#d","35620","CBSA","CBSA","New York-Northern New Jersey-Long Island, NY-NJ-PA","New York-Northern New Jersey-Long Island, NY-NJ-PA","Metropolitan Statistical Area","Metropolitan Statistical Area","1","1"
"#b","10020","CBSA","CBSA","Abbeville, LA ","Abbeville, LA","Micropolitan Statistical Area","Micropolitan Statistical Area","2","2"

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

Chris Auckland

Thanks for your post.

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.

swelsh

Chris,

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:

sqldatacompare.exe /v /s1:server1 /db1:database1 /s2:server2 /db2:database2  /include:Identical  /include:table:cbsa /excludeColumns:cbsa:row_created /options:IgnoreSpaces /options:IncludeTimestamps /options:IncludeIdentities /options:DisableKeys /options:OutputComments /options:TrimTrailingSpaces /Export:I:\Shawn\RedGate\Export

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:

sqldatacompare.exe /v /s1:server1 /db1:database1 /s2:server2 /db2:database2  /include:Identical  /include:table:cbsa /excludeColumns:cbsa:row_created /options:"IgnoreSpaces,IncludeTimestamps,IncludeIdentities,DisableKeys,OutputComments,TrimTrailingSpaces" /Export:C:\swelsh\RedGate\Export

Thanks for suggesting I try the regular command line as it lead me to my solution, even it wasn't actually RedGate related.

Chris Auckland

Sorry for the delay, I missed your last update. Thankfully you were just letting me know you solved the problem and didn't have any other questions.

That's interesting that powershell will ignore the other options if they're not wrapped in quotes. I'll have to remember that.

Thanks for letting us know how you fixed it.