sqlCI errors

SteveGTR

On my 100th attempt to build our database using the SQL Automation and TFS build processing. Okay, 100 is an exaggeration, but I've performed countless attempts to appease the sqlCI.exe program without achieving a successful build.

After fixing some issues with a windows user specified in the baseline database and a server reference in a stored procedure, I get the following error:

Build started 10/25/2013 5:07:48 PM.
Project "C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj" on node 1 (default targets).
sqlCI:
Starting: (echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:." "/databaseServer:(local)" "/databaseName:MergePageCI" "/databaseIntegratedAuthentication" "/packageId:MergePage" "/packageVersion:20131025.12" "/outputFolder:\\eerepagedevweb1\deploymentfeed") | cmd.exe
(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:." "/databaseServer:(local)" "/databaseName:MergePageCI" "/databaseIntegratedAuthentication" "/packageId:MergePage" "/packageVersion:20131025.12" "/outputFolder:\\eerepagedevweb1\deploymentfeed") | cmd.exe
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Builds\1\PageDatabases\MergePage\Sources\MergePage>set REDGATE_SEND_ERROR_REPORTS=true

C:\Builds\1\PageDatabases\MergePage\Sources\MergePage>set RUNNING_IN_MSBUILD=true

C:\Builds\1\PageDatabases\MergePage\Sources\MergePage>sqlCI.exe "/scriptsFolder:." "/databaseServer:(local)" "/databaseName:MergePageCI" "/databaseIntegratedAuthentication" "/packageId:MergePage" "/packageVersion:20131025.12" "/outputFolder:\\eerepagedevweb1\deploymentfeed"
sqlCI.exe -- Red Gate's SQL Continuous Integration v1.0.1.546

STARTING: Updating database
Starting: C:\Program Files\Red Gate\SQL Automation Pack 1\sqlCI\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:\Documents and Settings\Steve.PAGE\Local Settings\Temp\3jneq5qx.3om\db\state" /server2="(local)" /database2="MergePageCI" /synchronize
SQL Compare: trial has expired
Automation License: in trial, expires 10/30/2013 5:37:58 PM
SQL Compare Command Line V10.5.0.378
==============================================================================
Copyright c Red Gate Software Ltd 1999-2013

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Retrieving migration scripts
Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
Error: Synchronization of 'Scripts.state' and '(local).MergePageCI' failed:
Column name or number of supplied values does not match table definition.
FINISHED WITH ERROR: Updating database

EXEC : sqlCI error occurred: RedGate.SQLCI.Engine.Error: Updating database failed with exit code 126. For generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare [C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj]
A detailed error report will be sent to Red Gate.
These reports go into our issue tracking system, and help us fix bugs.
For information about what's contained in the report, see: http://redg.at/RY3TiS
Sending error report to Red Gate: Preparing Report...
Sending error report to Red Gate: Connecting to Server...
Sending error report to Red Gate: Transferring...
Sending error report to Red Gate: Finished. Our reference number is: {a5ef16b6-e50a-4200-8e9a-29bcdb053fe8}

C:\Builds\1\PageDatabases\MergePage\Sources\MergePage>
C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.targets(174,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:." "/databaseServer:(local)" "/databaseName:MergePageCI" "/databaseIntegratedAuthentication" "/packageId:MergePage" "/packageVersion:20131025.12" "/outputFolder:\\eerepagedevweb1\deploymentfeed") | cmd.exe" exited with code -1. [C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj]
Done Building Project "C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj" (default targets) -- FAILED.

Build FAILED.

"C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj" (default target) (1) ->
(sqlCI target) ->
EXEC : sqlCI error occurred: RedGate.SQLCI.Engine.Error: Updating database failed with exit code 126. For generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare [C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj]
C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.targets(174,5): error MSB3073: The command "(echo set REDGATE_SEND_ERROR_REPORTS=true & echo set RUNNING_IN_MSBUILD=true & echo sqlCI.exe "/scriptsFolder:." "/databaseServer:(local)" "/databaseName:MergePageCI" "/databaseIntegratedAuthentication" "/packageId:MergePage" "/packageVersion:20131025.12" "/outputFolder:\\eerepagedevweb1\deploymentfeed") | cmd.exe" exited with code -1. [C:\Builds\1\PageDatabases\MergePage\Sources\MergePage\sqlCI.proj]

0 Warning(s)
2 Error(s)

Time Elapsed 00:00:40.87

I'm not even sure where to begin trying to fix this. This is very frustrating as the SSMS snap-in utility will publish a database with the latest changes with no problems.

Here's the steps:

1) Create CI database on local agent machine using the following script in SSMS:

CREATE DATABASE MergePageCI
GO

USE MergePageCI
GO

ALTER DATABASE MergePageCI SET TRUSTWORTHY ON

EXEC sp_configure 'clr_enabled',1
RECONFIGURE
GO

2) Queue a build and get the above error.

3) I queue another build without clearing the CI database and this time I get a different error:

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Retrieving migration scripts
Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
Error: Synchronization of 'Scripts.state' and '(local).MergePageCI' failed:
Ambiguous column name 'ActivityNumber'. Ambiguous column name
'ActivityRevisionNumber'.
FINISHED WITH ERROR: Updating database

4) Repeating step 3 and now another error:

Registering data sources
Creating mappings
Comparing

Applying Command Line Items

Retrieving migration scripts
Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
Error: Synchronization of 'Scripts.state' and '(local).MergePageCI' failed:
Column name or number of supplied values does not match table definition.
FINISHED WITH ERROR: Updating database

I'm not sure where to go from here.

SteveGTR

Just noticed that the SQL Compare license has expired, but I have till next Wednesday for SQL Automation Pack. Could this be the problem?

justin.caldicott

Sorry to hear you're having a hard time with this. I'd love to have a catch up with you to discuss your experiences so we can make this all easier, once we've got you up and running.

I don't think licensing is the problem, as it's using the Automation Pack license, also the error message is very specific.

The error suggests an incorrectly built insert statement. It is unlikely this is a problem with the underlying SQL Compare engine. Peter Peart made a suggestion to a SQL Compare user that will apply here too:
http://www.red-gate.com/MessageBoard/vi ... hp?p=44937

Also, it's worth pointing out that if your primary aim is to create database packages for Deployment Manager, then you do not need to use the sqlCI functionality that keeps a database up-to-date. (This is where it's failing at the moment.) Not specifying databaseServer, databaseName and databaseIntegratedAuthentication arguments will mean it does not try to keep a database up-to-date. This is something you can add at a later date if wanted.

For more detailed command line reference (that you probably already have open), see here: https://www.red-gate.com/supportcenter/ ... d_in_sqlCI

Keep us posted on your progress, and feel free to ask any more questions.

SteveGTR

Yes!!! My primary reason for performing this build process is to create a database package for the Deployment Manager. What is the automated method of doing this if not through your SQL Automation pack?

We use Visual Studio with TFS as the source control repository.

This whole process is maddeningly frustrating to simply create a nuget database package automatically for the deployment manager...

justin.caldicott

Steve,

Do you want to drop me an email to Justin.caldicott@red-gate.com with some times that would work for a call?

Did any of my suggestions help you progress this?

Thanks,

Justin

SteveGTR

That would be excellent! I'll shoot off an email now.