Red Gate forums :: View topic - Data Compare 9 command line ignoring TrimTrailingSpaces
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Data Compare 9 command line ignoring TrimTrailingSpaces

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
swelsh



Joined: 13 Jan 2012
Posts: 9
Location: Palm Harbor, FL

PostPosted: Fri Jan 13, 2012 1:27 am    Post subject: Data Compare 9 command line ignoring TrimTrailingSpaces Reply with quote

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

Code:
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
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Fri Jan 13, 2012 4:43 pm    Post subject: Reply with quote

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.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
swelsh



Joined: 13 Jan 2012
Posts: 9
Location: Palm Harbor, FL

PostPosted: Fri Jan 13, 2012 6:43 pm    Post subject: Reply with quote

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:

Code:
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:

Code:
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. Very Happy
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Jan 17, 2012 6:33 pm    Post subject: Reply with quote

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.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group