Syntax error with MS SQL Server

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create any delta script.
2. Generate the DbDeploy output sql
3. Run the output sql via the Ant sql target.

What is the expected output? What do you see instead?
The script should run without error.
I'm getting an error due to incorrect syntax
"com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax
near 'GO'."

My sql script starts with the lines:

BEGIN TRANSACTION
GO

This works fine when not run via JDBC (in SQL Server Management Studio
Express).

What version of the product are you using? On what operating system?
3.0M1
SQL Server JDBC4 driver. (Not sure exactly which version of the driver).

Original issue reported on code.google.com by kevin.st...@gmail.com on 7 Oct 2009 at 4:54

Attachments:

• sqljdbc4.jar

[GoogleCodeExporter]

Arrgh!  My attempts to clarify how dbdeploy is using transactions has once more 
done
more harm than good.

I'll remove all the begin transaction stuff from the templates, I think.

Original comment by gtack...@googlemail.com on 11 Oct 2009 at 4:50

• Changed state: Accepted
• Added labels: Milestone-Release3.0M2
• Removed labels: ****

[GoogleCodeExporter]

Fixed in r123

Original comment by gtack...@googlemail.com on 11 Oct 2009 at 6:03

• Changed state: Done
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

There are still GO statements being output in the generated script, after the 
insert
into the changelog and after the final COMMIT. 

Original comment by kevin.st...@gmail.com on 14 Oct 2009 at 10:22

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

My colegue and I had this same problem, and the GO always seemed to be the 
problem.

After much cursing this is the MSSQL template file we came up with, and it 
works great (SQL Server 2008)

mssql_apply.flt 
----------------------------------------------------------------------
[#ftl]
[#-- @ftlvariable name="changeLogTableName" type="java.lang.String" --]
[#-- @ftlvariable name="scripts" 
type="java.util.List<com.dbdeploy.scripts.ChangeScript>" --]
[#list scripts as script]

-- START CHANGE SCRIPT ${script}
BEGIN TRANSACTION
${script.content}
COMMIT
BEGIN TRANSACTION
INSERT INTO ${changeLogTableName} (change_number, complete_dt, applied_by, 
description)
 VALUES (${script.id}, getdate(), user_name(), '${script.description}')
COMMIT

-- END CHANGE SCRIPT ${script}

[/#list]

----------------------------------------------------------------------
mssql_undo.flt
----------------------------------------------------------------------

[#ftl]
[#-- @ftlvariable name="changeLogTableName" type="java.lang.String" --]
[#-- @ftlvariable name="scripts" 
type="java.util.List<com.dbdeploy.scripts.ChangeScript>" --]
[#list scripts as script]

-- START UNDO OF CHANGE SCRIPT ${script}
BEGIN TRANSACTION;
${script.undoContent}
COMMIT;
BEGIN TRANSACTION;
DELETE FROM ${changeLogTableName} WHERE change_number = ${script.id}
COMMIT;

-- END UNDO OF CHANGE SCRIPT ${script}

[/#list]
----------------------------------------------------------------------

Original comment by jpo...@gmail.com on 14 Sep 2010 at 8:22

• Added labels: ****
• Removed labels: ****