sql script execution fails when called using ANT S

问题:

There is a SQL file which contains some Transact SQL statements in it and some plain table queries as follows:

IF NOT EXISTS (SELECT * FROM [dbo].[SYSTEM_PROPERTIES] WHERE SYS_PROP = 'ABC')
BEGIN
DECLARE @SYS_PROP_ID INT;
INSERT INTO SYSTEM_PROPERTIES (...,....,...) values ('...','...','...');
SELECT    -------;
INSERT INTO ------;
END
GO

IF EXISTS (SELECT * FROM [dbo].[TEMPLATE] WHERE TPL_NAME='....' )
UPDATE [dbo].[TEMPLATE] SET [...] = 'Y' WHERE TPL_NAME='.....'
GO 

When I execute this script directly on the database, it works fine. When the same script is called through an ANT SQL task it fails with the following error:

 com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'INT'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

This is the Ant task:

<sql   driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"   url="---------"
userid="--"   password="---"  keepformat="true" print="true" >
 <classpath>
    <pathelement location="/lib/sqljdbc4.jar"/>
    <pathelement location="/lib/ojdbc14.jar"/>
 </classpath>
<transaction  src="${dbscript.location}/dbscript.sql"/>
</sql>

Why is the script failing when called from ANT SQL?

回答1:

After spending a lot of time, I finally was able to resolve the issue. When calling any Transact SQL statements through an ANT SQL task, the ";" delimiters should NOT be specified. Having these delimiters will not cause any problem when executing the SQL statements from SQL Management studio or sqlcmd. Also, as pointed out in the comments above, GO statement is also not acceptable when running the script through ANT SQL.

回答2:

SQL Ant task supports the following attributes which allow you to use semicolon in your script, and actually let you run complex scripts such as PL/SQL or Stored Procedures:

• delimiter
• delimitertype

Use them together in your task like this:

<sql ...
    delimiter="/"
    delimitertype="row"
    ... 
/>

In this example any ';' will be replaced by '/'

For more details check ANT docs here