Failure during validating db state, ignoring filter file

alanna

Hello,
I have TeamCity configured to run a SQL CI build of a database using a filter file. We'd previously done an evaluation of all these products on a different server - everything was working fine and the filter file I'd configured was being used. The main purpose of the filter file is to ignore a series of views that would otherwise be deemed invalid (after a db is deployed, we have a script that runs that updates all of the views in question).

I recently moved everything over to a new server (basically just backed everything up and restored it to the new server) and now it appears as though the filter file is being ignored. The TeamCity Build Agent and Server services are set to run under the Local System account. I have tried putting these filter files into the root of the D drive, but have also tried putting them into the TeamCity folder as well. In both situations, the build fails during database validation, complaining about an invalid view. This is one of the views that the filter should be excluding.

The filter file is located here: D:TeamCitySQLCompareFiltersmainDatabaseExclusions.scpf - I have verified that it is there and that it is supposed to filter out views with a name that starts with 'rv'. I have confirmed that the Local System account should have access to it (as it's not being specifically denied!)

Corresponding information from the filter file:

<View version="1">
        <Include>False</Include>
        <Expression>(@NAME LIKE 'rv%') OR (@NAME LIKE 'RawAgentData%') OR (@NAME LIKE 'vw%') OR (@NAME = 'vThresholdList') OR (@NAME LIKE '%ETL%') OR (@NAME LIKE 'DataLoader_%')</Expression>
      </View>

Corresponding entry from the build log:

[09:23:54][Step 1/3] Validating database state (34s)
[09:23:54][Validating database state] STARTING: Validating database state
[09:23:54][Validating database state] Starting: D:TeamCityuildAgentpluginssqlci-teamcity-agentassetsSCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="D:TeamCityuildAgent	empuildTmpkh0hgoap.paedbstate" /filter="D:TeamCitySQLCompareFiltersmainDatabaseExclusions.scpf" /server2="np:\.pipeLOCALDB#9911E7E7	sqlquery" /database2="sqlCI_52b238b4-e8cb-4104-8754-8312723695a6" /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2"
[09:23:55][Validating database state] SQL Compare: activated, edition: professional, serial number: 610-010-247793-2398
[09:23:56][Validating database state] SQL Compare Command Line V11.2.0.22
[09:23:56][Validating database state] ==============================================================================
[09:23:56][Validating database state] Copyright Copyright ¸ 1999 - 2015 Red Gate Software Ltd
[09:23:56][Validating database state] 
[09:23:56][Validating database state] Registering data sources
[09:24:15][Validating database state] Creating mappings
[09:24:16][Validating database state] Comparing
[09:24:16][Validating database state] 
[09:24:16][Validating database state] Applying Command Line Items
[09:24:16][Validating database state] 
[09:24:23][Validating database state] Checking for identical databases
[09:24:23][Validating database state] Creating SQL
[09:24:25][Validating database state] Inserting static data SQL into deployment script
[09:24:25][Validating database state] Deploying changes (from DB1 to DB2)
[09:24:28][Validating database state] Error: Synchronization of 'Scripts.state' and
[09:24:28][Validating database state] 'np:\.pipeLOCALDB#9911E7E7	sqlquery.sqlCI_52b238b4-e8cb-4104-8754-831272369
[09:24:28][Validating database state] 5a6' failed: Invalid object name 'Compas_Scorecard.dbo.Scorecard'. Error
[09:24:28][Validating database state] executing the following SQL: CREATE VIEW [dbo].[rv_ScorecardDisplayTemplates] AS
[09:24:28][Validating database state] SELECT S.[ScorecardID], T.* FROM Compas_Scorecard.[dbo].[Scorecard] S INNER JOIN
[09:24:28][Validating database state] Compas_Scorecard.[dbo].[DefinitionVersion] V ON S.[DefinitionVersio...
[09:24:29][Validating database state] ##teamcity[buildStatus status='FAILURE' text='Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']
[09:24:29][Validating database state] FINISHED WITH ERROR: Validating database state
[09:24:33][Step 1/3] ##teamcity[buildStatus status='FAILURE' text='Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']
[09:24:36][Step 1/3] Process exited with code 32
[09:24:36][Step 1/3] Step Build Main Database (Red Gate SQL CI Build) failed

Does anyone have any suggestions on what else I should check or what I can do to resolve this?

Thanks.

alanna

Just in case anyone else experiences a similar issue, I had to change the additional parameters configured for the TC build from:
/filter:D:TeamCitySQLCompareFiltersmainDatabaseExclusions.scpf

To:
/filter:D:TeamCitySQLCompareFiltersevaluationDatabaseExclusions.scpf /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2,-IncludeDependencies"

Essentially, I list all the default options that it had been using (I don't know if this is exactly necessary or not, but have left it that way for now), and make sure to put -IncludeDependencies in there to specifically exclude that option.

I'm unsure as to why this was working before and stopped when we moved everything to a new server, but at least it's working again!