Stored procedures continuously being detected as different
jerfer
Posts: 3 New member
I'm migrating a TeamCity/Octo pipeline that has been running smoothly for 2 years to a new set of servers and in doing so am upgrading from DLM Automation 1 to DLM Automation 2. I use the Redgate TeamCity/Octo templates to compare a db scripts folder against a target database as part of the process.
During the upgrade I hit an issue where the post-deploy update check would always fail because of it detecting differences between the database scripts and the target database. I narrowed this down to 6 objects that appear to cause issues with sqlcompare or my comparison options.
2 of the objects were tables that had triggers that were defined without wrapping square brackets on their names, adding the "IgnoreSquareBrackets" sql compare option caused these items to than be treated as identical after a deployment.
However there are 4 sprocs that are continuously flagged as different. I've viewed the "Changes.html" that gets created as part of the deployment process but the diff for these objects does not highlight any lines as different/changed. I also tried using the SqlCompare UI app to see if that would show any differences, and while it does flag the 4 objects as different, the diff it does not highlight any lines in the comparison to indicate what difference its detecting. A common theme for these 4 objects is that they contain considerable amounts of formatted dynamic sql. I tried turning on the IgnoreWhitespace comparison option as well as every other comparison option and in all cases these 4 objects are flagged as different. We have other objects that contain dynamic sql that do not have this issue.
Any assistance in troubleshooting what might be causing these objects to be continuously flagged as different would be appreciated. I can update the version of the object in the scripts folder or include additional options in the comparison, etc.
During the upgrade I hit an issue where the post-deploy update check would always fail because of it detecting differences between the database scripts and the target database. I narrowed this down to 6 objects that appear to cause issues with sqlcompare or my comparison options.
2 of the objects were tables that had triggers that were defined without wrapping square brackets on their names, adding the "IgnoreSquareBrackets" sql compare option caused these items to than be treated as identical after a deployment.
However there are 4 sprocs that are continuously flagged as different. I've viewed the "Changes.html" that gets created as part of the deployment process but the diff for these objects does not highlight any lines as different/changed. I also tried using the SqlCompare UI app to see if that would show any differences, and while it does flag the 4 objects as different, the diff it does not highlight any lines in the comparison to indicate what difference its detecting. A common theme for these 4 objects is that they contain considerable amounts of formatted dynamic sql. I tried turning on the IgnoreWhitespace comparison option as well as every other comparison option and in all cases these 4 objects are flagged as different. We have other objects that contain dynamic sql that do not have this issue.
Any assistance in troubleshooting what might be causing these objects to be continuously flagged as different would be appreciated. I can update the version of the object in the scripts folder or include additional options in the comparison, etc.
Answers
Thanks for your post.
This looks like something that a support engineer should look in to for you so if you have a support contract and don't receive any help from the community here then please send us a ticket regarding this issue.
Kind regards
Richard Lynch.
Redgate Software