使用 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屋！