This project has moved. For the latest updates, please go here.

"Incorrect syntax near 'GO'" error when executing in MSBuild on TeamCity

Nov 20, 2013 at 6:00 PM
I'm getting the exception below on my TeamCity server when I execute the following command in MSBuild against a SQL Server 2012 Web database:

<DbDeploy DatabaseType="mssql"
ConnectionString="User=$(User);Password=$(Password);Data Source=$(SqlServerName);Initial Catalog=$(DatabaseName);"
RootDirectory="$(ScriptDirectory)"
OutputFile="$(OutputFileName)"
UndoFile="$(UndoFileName)"
Recursive="true"/>

The ScriptsList.txt is generated before the exception is thrown and contains the two test scripts I have created:

This file is autogenerated and will be replaced on each run of DatabaseDeploy. You should not change the contents.
This file reflects all of the scripts that were found in this directory and if recursion is on, child directories. These are the scripts that will be included in the deployment script.

The order in which the scripts will be applied is the order in which they appear in this file.

C:\BuildAgent\work\700df50f30b3154c\src\Database\Securities\Scripts\DeployedScripts\v1.0\001 Add extended property comment on datbase.sql

C:\BuildAgent\work\700df50f30b3154c\src\Database\Securities\Scripts\DeployedScripts\v1.0\002 Create schema access.sql

The exception:

[DbDeploy] C:\BuildAgent\work\700df50f30b3154c\build\Database\Common\db.targets(58, 3): error MSB4018: The "DbDeploy" task failed unexpectedly.
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Veracity.Utilities.DatabaseDeploy.Database.DatabaseInstances.SqlServer.SqlServerDatabaseService.ExecuteScript(String scriptFileName, DbParameter[] parameters) in c:\Source\DbDeploy.Net2\trunk\Veracity.Utilities.DatabaseDeploy\Database\DatabaseInstances\SqlServer\SqlServerDatabaseService.cs:line 106
at Veracity.Utilities.DatabaseDeploy.Database.DatabaseInstances.DatabaseServiceBase.EnsureChangelogExists() in c:\Source\DbDeploy.Net2\trunk\Veracity.Utilities.DatabaseDeploy\Database\DatabaseInstances\DatabaseServiceBase.cs:line 235
at Veracity.Utilities.DatabaseDeploy.Database.DatabaseInstances.DatabaseServiceBase.GetAppliedChanges() in c:\Source\DbDeploy.Net2\trunk\Veracity.Utilities.DatabaseDeploy\Database\DatabaseInstances\DatabaseServiceBase.cs:line 131
at Veracity.Utilities.DatabaseDeploy.DeploymentService.BuildDeploymentScript() in c:\Source\DbDeploy.Net2\trunk\Veracity.Utilities.DatabaseDeploy\DeploymentService.cs:line 168
at Veracity.Utilities.DatabaseDeploy.BuildTasks.DbDeploy.Execute() in c:\Source\DbDeploy.Net2\trunk\Veracity.Utilities.DatabaseDeploy\BuildTasks\DbDeploy.cs:line 192
at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__20.MoveNext()

ClientConnectionId:f170fbd7-09f7-4e94-bcc2-c405398aeb97

__This works fine on a local development machine running SQL Server 2012 Express.

Any help much appreciated!__
Coordinator
Dec 16, 2013 at 10:44 PM
Sorry for the delay. I've been at client sites and haven't had much time to do anything. I'm looking into this now.
Coordinator
Jan 8, 2014 at 10:38 PM
O.k.--found what's causing this. Edit the EnsureChangeLogExists.sql script and make it look like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[changelog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[changelog](
    [change_number] [int] NOT NULL,
    [complete_dt] [datetime] NULL,
    [applied_by] [varchar](100) NOT NULL,
    [description] [varchar](500) NOT NULL,
 CONSTRAINT [Pkchangelog] PRIMARY KEY CLUSTERED 
(
    [change_number] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
And the problem will go away.
Coordinator
Jan 8, 2014 at 10:49 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.