Top 10 SQL Compare Tips. Watch now.

XML Schema Collections always show difference when comparing from snapshot with version 12?

We are using version 12 to compare from snapshot to database and to update our databases.  It's working well except for XML Schema Collections, which are always showing up a differences and every update will drop and recreate the collection.  When we view the report it shows that the XML under source snapshot is all on one line; however, the XML under the target live database has each element on a new line.

Previously we are using version 11 and we didn't have this issue.  However, we were required to update to version 12 because we are migrating to SQL 2017 which is not supported by 11.  The only change we added to our scripts was to add "IgnoreSquareBrackets" and "/logLevel:v".

Is there anyway around this issue with version 12?  Our workaround would be to filter out the XSDs and do those manually because we have everything else working.

Here are the arguments we use to create the snapshot:
$AllArgs = @("/filter:$scp_path/SCP/$scp_name.scpf", "/snapshot1:$scp_path/sourceSchema.snp", "/Server2:$server_name", "/Database2:$database_name", "/report:$scp_path/report-preupdate-$scp_name-$env_name-$time.html", "/reportType:Interactive", "/scriptfile:$scp_path/PreupdateDatabase-report-$env_name-$time.sql", "/force", "/verbose", "/options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnorePermissions,IgnoreUsersPermissionsAndRoleMemberships,IgnoreSquareBrackets", "/Include:Identical", "/logLevel:v")

And here are the arguments we use to update:
$AllArgs = @("/filter:$scp_path/SCP/$scp_name.scpf", "/snapshot1:$scp_path/sourceSchema.snp", "/Server2:$server_name", "/Database2:$database_name", "/report:$scp_path/report-update-$env_name-$time.html", "/scriptfile:$scp_path/updateDatabase-report-$env_name-$time.sql", "/force", "/verbose", "/options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnorePermissions,IgnoreUsersPermissionsAndRoleMemberships,IgnoreSquareBrackets", "/synchronize", "/logLevel:v")

Tim
Tagged:

Answers

  • Eddie DEddie D Posts: 1,681 Rose Gold 5
    edited September 27, 2019 8:31AM
    Hi Tim, 
    thank you for your forum post.

    You are still using a much older version of the SQL Compare tool.  The last build of V12 was V12.4.12.5042 which dates back to August 2017.

    I am aware of several updates that occurred in regards to XML Schema Collection in the various V13 releases.

    I recommend that you upgrade to SQL Compare V14 and see if this latest version of tool either resolves or you still encounter the reported problem.

    I make the above request as any bug fix build will occur in the V14 branch as development on the V12 branch ceased in August 2017.

    You can download the latest SQL Compare V14 using this link.

    PLEASE NOTE: SQL Compare V14 now requires .NET Framework V4.7.2 and support for SQL 2005 has been dropped.  You can obtain the release notes here.

    You will be able to install SQL Compare V14 alongside the existing V12 installation.  You will need to activate your license, using your license key when the trail period expires.

    Many Thanks
    Eddie




    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • tprudencetprudence Posts: 5 New member

    Thanks for your response Eddie.  I understand we are on the older version; however, we were not encountering this issue before upgrading from 11 to 12.  I thought there might be something simple to get it working in version 12 but in the meantime we have implemented a simple workaround which is to implement a filter excluding all XML schema collection.

    My company already spends a lot on Red Gate licenses.  We have licenses for earlier version of SQL Toolbelt including SQL Compare 12, which is what I am using for this process.  We also have licenses for SQL Compare 13 standalone; however I was not able to get it working at all with that version because the sqlcompare.exe process crashes completely.  Sergio told me it's because I need to use the automation pack for 13, part of the toolbelt, but unfortunately it's not included in my license for 13.  At this point, I cannot justify the cost of acquiring additional licenses for this issue.  We will live with the workaround.

Sign In or Register to comment.