I am trying to deploy db changes using Liquibase and getting the below error on one of the stored procedure code.
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)
The sp looks like:
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
And the changeset looks like:
<?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>
It looks like you use the <sql>
or <sqlFile>
refactoring tag. They automatically split your queries on ;
and GO
. That's not what you want in this case.
You can either set the attribute splitStatements
to false
or use the <createProcedure>
refactoring tag instead.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With