Liquibase：近语法错误;

Question

我想使用Liquibase部署数据库更改并获取存储过程代码之一的下面的错误。

SET NOCOUNT ON: Incorrect syntax near 'ON'. 
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62) 
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104) 
    at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1075) 
    at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1059) 
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317) 
    ... 5 more 
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'ON'. 
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676) 
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575) 
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154) 
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:649) 
    at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92) 
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55) 

SP的样子：

CREATE PROCEDURE [dbo].[TestSP] 
    AS 
    BEGIN 

     SET NOCOUNT ON; 


     SELECT Col1, Col2 
     FROM dbo.[Table1] 

    END 
    GO 
GRANT EXECUTE ON [dbo].[testsp] TO [SomeRole] 
GO 

而变更的样子：

<?xml version="1.0" encoding="utf-8"?> 
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd"> 

    <changeSet id="6a07201a-133e-4613-b4cf-15becbf53baf" author="Nadeem"> 

    <sqlFile path="./Creation Scripts/Stored Procedures/testsp.sql" /> 
    <rollback> 
     <sqlFile path="./Update Scripts/Stored Procedures/testsp.Rollback.sql" /> 
    </rollback> 
    </changeSet> 
</databaseChangeLog> 

Answer 1

它看起来像您使用<sql>或<sqlFile>重构标签。他们自动将您的查询分为;和GO。这不是你想要的。

您可以将属性splitStatements设置为false，也可以改为使用<createProcedure>重构标记。