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.