Schema Comparison Detects Differences for Identical Stored Procedures

Question

I am using the Visual Studio 2012 schema comparison tool to diff an actual database with a Microsoft SQL Server DAC Package File (dacpac). The expectation is that the database and the file are "equal". However, the schema comparison reports differences in the BodyScript property of three stored procedures, all of which contain lots of dynamic SQL. What's frustrating, though, is that in the actual diff of the object definitions, there are no apparent differences between the definitions of the stored procedures. Furthermore, we've copied the scripts into a number of different diff tools to look for whitespace-type differences, but it appears that the two scripts are binary identical. We've played around with numerous schema comparison options (ignore whitespace, ignore keyword casing, etc.), but nothing seems to fix this problem.

In researching this issue, I've come across similar problems posted on Microsoft Connect and also here on StackOverflow, but it appears that these issues have yet to be resolved. Does anyone have any suggestions for other ways to address this problem?

score 10 · Accepted Answer · answered Jul 16 '15 at 18:14

It appears that this problem is caused by new line characters in string literals.

This is bad:

DECLARE @badQuery NVARCHAR(MAX) = N'SELECT
                                      *
                                    FROM
                                      [dbo].[MyRadTable]';

This is good:

DECLARE @goodQuery NVARCHAR(MAX) = N'SELECT '+
                                      '* ' +
                                    'FROM ' +
                                      '[dbo].[MyRadTable]';

Schema Comparison Detects Differences for Identical Stored Procedures

1 Answers1