Key and Certificate script files fail comparison

weswinkler

Using the suggestion from another post in this forum (Compare schema between TFS Project with Database), I made a comparison between the "Scheme Object" file directory in my local TFS source tree against a deployed database.

SQL Compare 9 (v9.0.0.79) complains about each file which includes a CREATE CERTIFICATE, CREATE SYMMETRIC KEY, or CREATE MASTER KEY command. The issue cited by SQL Compare is "Non-schema statement was ignored."

Each of these files parses correctly within SSMS.

An example:

CREATE CERTIFICATE [CertificateBackupCertificate]
    AUTHORIZATION [dbo]
    WITH SUBJECT = N'Certificate Backup Certificate', 
    START_DATE = N'7/27/2008 10:39:34 AM', 
    EXPIRY_DATE = N'12/31/2010 12:00:00 AM'
    ACTIVE FOR BEGIN_DIALOG = ON;

Is this a bug?

Chris Auckland

Thanks for your post.

SQL Compare will only successfully compare SQL Scripts that were created by SQL Compare.

I believe SQL Compare is expecting to find the CREATE CERTIFICATE statements in their own file, and finding them as part of another object is causing the parsing error.

weswinkler

Each certificate is defined in its own file, not as part of another object.

The files reside in subdirectories like \Schema Objects\Database Level Objects\Security\Symmetric Keys,
\Schema Objects\Database Level Objects\Security\Certificates, and \Schema Objects\Database Level Objects\Security\Master Keys. The example cites the entire contents of one such file.

Other files for database roles are successfully parsed from \Schema Objects\Database Level Objects\Security\Roles\Database Roles. All other SQL objects have their defining files in a directory structure under \Schema Objects\Schemas\dbo.

Chris Auckland

Were the scripts created by SQL Compare?