Do not pass GO

(This is a re-post from my work blog, but I thought I'd post it here for everyone else.)

ANT has a SQL task which allows you to execute SQL statements via JDBC which can be very handy in a number of projects. Currently I'm using this task to execute a directory of generated sql files, each of which defines a stored procedure for MS SQL Server. Here is what a typical one of these stored procdure SQL files looks like:

USE MyDatabase
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

IF (OBJECT_ID('dbo.usp_myproc') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.usp_myproc'), N'IsProcedure') = 1)

DROP PROCEDURE dbo.usp_myproc

GO

CREATE PROCEDURE dbo.usp_myproc
(
@param INT
)
AS
BEGIN
...
RETURN
END
GO

GRANT EXECUTE ON dbo.usp_myproc TO MyDatabaseUser

Pretty standard stuff. However on my first attempt to use the SQL task on these files resulted in the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'GO'.

This is what my ANT target looked like:

<target name="build-storedprocs">
<sql
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://SERVER:1433;databaseName=MyDatabase;integratedSecurity=true"
userid=""
password="">


<fileset dir="${sql.dir}">
< include name="*.sql" />
</fileset>

</sql>
</target>

After some quick googling and looking at the ANT docs I was able to figure out the issue. GO is actually not a T-SQL statement. According to the SQL Server Books Online:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

What I ended up having to do is set the delimiter attribute of the SQL task to "GO" and things worked as expected. Here is what the target ended up looking like:

<target name="build-storedprocs">
<sql
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://SERVER:1433;databaseName=MyDatabase;integratedSecurity=true"
userid=""
password=""
delimiter="GO">


<fileset dir="${sql.dir}">
< include name="*.sql" />
</fileset>

</sql>
</target>

Just something to keep in mind if you try to run your SQL Server scripts via ANT.

Comments
Jim Priest's Gravatar Good stuff! Added to my Ant wiki:
http://www.thecrumb.com/wiki/ant
# Posted By Jim Priest | 7/2/08 9:19 PM
Matt's Gravatar Thank you for this! I had been struggling with the 'go' issue for too long, and your solution did just the trick :D
# Posted By Matt | 6/16/11 2:53 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.