Schema comparison reveals differences for identical stored procedures

Question

I use the Visual Studio 2012 schema comparison tool to distinguish between the actual database and the Microsoft SQL Server DAC ( dacpac) package file . The database and file are expected to be "equal." However, schema comparisons report differences in the property of the BodyScriptthree stored procedures, all of which contain many dynamic SQL. However, it is frustrating that, with the actual delineation of object definitions, there are no obvious differences between the definitions of stored procedures. In addition, we copied the scripts into a number of different diff tools to look for differences in the types of spaces, but it seems that the two scripts are identical to the binary ones. We played with numerous options for comparing schemes (ignore spaces, ignore keyword caching, etc.), but nothing seems to fix this problem.

While investigating this problem, I ran into similar problems published on Microsoft Connect , as well as here at https://stackoverflow.com/a/3/23823/ ... , but it seems these problems have not been resolved yet. Does anyone have any suggestions on other ways to solve this problem?

Answer 1

This problem appears to be caused by new string characters in string literals.

This is bad:

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

It's good:

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