Failed validating database state error

gjcradle

Hi,

I've hit an issue with trying to get our main database to automatically build and deploy. We've got this process working with some of our smaller databases but this is the critical one.

The annoying thing is that this was working but now it's not. This is the relevant section from the TeamCity build log:

[09:03:04][Validating database state] Inserting static data SQL into deployment script
[09:03:05][Validating database state] Deploying changes (from DB1 to DB2)
[09:03:09][Validating database state] Error: Synchronization of 'Scripts.state' and
[09:03:09][Validating database state] 'np:\.pipeLOCALDB#62A32E84 sqlquery.sqlCI_18e6d8d9-0e13-43df-8b12-6efbcd3ef
[09:03:09][Validating database state] 9dd' failed: Cannot find either column "dbo" or the user-defined function or
[09:03:09][Validating database state] aggregate "dbo.ConCat", or the name is ambiguous. Error executing the following
[09:03:09][Validating database state] SQL: CREATE VIEW dbo.Funder1PDFView AS SELECT TitleName + ' ' + FirstName + ' ' +LastName AS CustName, MaidenName AS CustMaiden, PlaceOfBirth AS
[09:03:09][Validating database state] CustBirthCounty, MaritalStatu...
[09:03:09][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: [url=http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare'[/url]]
[09:03:09][Validating database state] FINISHED WITH ERROR: Validating database state
[09:03:12][Step 1/1] ##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: [url=http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare'[/url]]

This is the line that invokes SQL Compare:

Starting: C:TeamCityuildAgentpluginssqlci-teamcity-agentassetsSCSQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:TeamCityuildAgent empuildTmp4w5clsoc.oaqdbstate" /Options="Default,IgnoreUsers" /server2="np:\.pipeLOCALDB#62A32E84 sqlquery" /database2="sqlCI_18e6d8d9-0e13-43df-8b12-6efbcd3ef9dd" /synchronize /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseMigrationsV2"

The only thing I can see that's changed is that I've upgraded from the automation pack to the latest version. That means the version of SQL compare has changed from V11.1.4.44 (working) to V11.2.3.12 (not working).

Should I revert back to the previous version of SQL Compare? If so, what's the best way to do that?

Thanks.

Rob C

Hi,

Unfortunately this is a known bug with reference SC-7923. Because your function has the same name as a system function, it is not being read from the scripts folder.

You could rename the function to something that isn't a system function, for instance ConCat2, and it would be picked up by the Compare engine. Obviously that's more difficult if it requires a lot of changes.

If you'd like to downgrade Compare to 11.1.4.44, the URL is here - ftp://support.red-gate.com/patches/SQLC ... 1.4.44.exe - you'll have to install it as a standalone product and then copy the relevant files across from the C:Program Files (x86)Red GateSQL Compare 11 directory to your sqlCISC directory. As a precaution, I'd make sure that you backup the original files first.

Sorry.

gjcradle

Hi Robert,

Thanks for the reply.

Although it will probably be more work I shall look into either changing the view to use the standard CONCAT function or rename our internal one. I'd rather not have old versions of software installed.

Rob C

Yesterday's release of the DLM Automation Suite included the latest version of the SQL Compare engine which contains a bugfix for this problem. Here's a download link if you need it.