使用ANT SQL任务调用时，sql脚本执行失败 [英] sql script execution fails when called using ANT SQL task

问题描述

有一个SQL文件，其中包含一些Transact SQL语句和一些普通表查询，如下所示:

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 

当我直接在数据库上执行此脚本时，它可以正常工作.通过ANT SQL任务调用相同的脚本时，它将失败，并显示以下错误:

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)

这是Ant任务:

<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>

为什么从ANT SQL调用脚本时失败?

Why is the script failing when called from ANT SQL?

推荐答案

花了很多时间之后，我终于能够解决此问题.通过ANT SQL任务调用任何Transact SQL语句时，;"不应指定分隔符.从SQL Management Studio或sqlcmd执行SQL语句时，拥有这些定界符不会引起任何问题. 另外，正如上面的评论所指出的那样，通过ANT SQL运行脚本时，GO语句也是不可接受的.

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.

这篇关于使用ANT SQL任务调用时，sql脚本执行失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！