問題描述
有一個 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 腳本執行失敗的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！