Data Compare - generated sync script contains err

georhillgeorhill Posts: 20
The sync script that is generated by the deployment wizard contains an error when there is a where clause specified on the table during comparison.
Here is the where clause that I specified when I compared the app_code table: "where type = 'prog_degree'"

Here is part of the generated script from the deployment wizard:
-- Update 17 rows in [dbo].[APP_CODE]
UPDATE [dbo].[APP_CODE] SET [OPTION_1]='Level_2', [OPTION_2]='20', [OPTION_3]='ASSOC', [OPTION_4]='Assoc or Tech Level' WHERE [SUBSYSTEM]='CUS' AND [TYPE]='PROG_DEGREE' AND [code]='ASSOC' AND (where type = 'prog_degree')
UPDATE [dbo].[APP_CODE] SET [OPTION_1]='Level_3', [OPTION_2]='32', [OPTION_3]='BA_BS', [OPTION_4]='Bachelor Level' WHERE [SUBSYSTEM]='CUS' AND [TYPE]='PROG_DEGREE' AND [code]='BA' AND (where type = 'prog_degree')

The last "and" clause causes a syntax error in SSMS.


  • James BJames B Posts: 1,124 Silver 4
    When you specify a where clause in SQL Data Compare, don't include the "where" keyword. So in your case it would just be " type = 'prog_degree'"

    This is because there's already a where clause added internally by the software so it's working on the right record.
    Systems Software Engineer

    Redgate Software

  • Got it! Thanks for the clarification.
Sign In or Register to comment.