Cannot create stored procedures in SQL Server

General Discussion
1

Hi.

I can’t seem to get Liquibase creating stored procedures on my database. I’ve created a simple stored procedure in a changelog as follows:

CREATE PROCEDURE [dbo].[test_procedure] AS SELECT 1; GO

Liquibase validates the changelog fine, the status command reports that 1 changeset has not been applied, but when I run the update command, I get the following error:

Migration Failed: Error executing SQL CREATE PROCEDURE [dbo].[test_procedure]
AS
SELECT 1;
GO

However, if I run updateSQL and paste it’s output into a script in SQL Server Management Studio, the script runs fine, the procedure is created and the databasechangelog table is updated.

Obviously, I’d prefer that the regular update command would work, and I’m grateful for any advice.

Thanks,
Nick Brown

Software versions and other details:

Liquibase 1.9.5
Java: 1.6.0_21
OS: Windows SBS2003
Database: MS SQL Server 2005 Express
JDBC driver: MS SQL Server Type 4 JDBC driver, V3.0

I’m running Liquibase from the command line as follows:

java -jar “D:\scratch\liquibase-1.9.5.jar” --url=“jdbc:sqlserver://myserver:1433;databaseName=mydatabase” --classpath=“D:\scratch\sqljdbc4.jar” --username=xxx --password=xxx --changeLogFile=“D:\scratch\proctest.xml” updateSQL

Full output from the updateSQL command:

– *********************************************************************
– Update Database Script
– *********************************************************************
– Change Log: D:\scratch\proctest.xml
– Ran at: 06/10/10 14:49
– Against: xxx@jdbc:sqlserver://myserver:1433;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=mydatabase;applicationName=Microsoft SQL Server JDBC Driver;
– LiquiBase version: 1.9.5
– *********************************************************************

SELECT COUNT(*) FROM [DATABASECHANGELOGLOCK] WHERE ID=1
GO

SELECT COUNT(*) FROM [DATABASECHANGELOGLOCK] WHERE ID=1
GO

– Changeset D:\scratch\proctest.xml::test_procedure::nickbrown::(MD5Sum: b5faa8a4874f7953a71520bd3b0d46)
CREATE PROCEDURE [dbo].[test_procedure]
AS
SELECT 1;
GO
GO

INSERT INTO [DATABASECHANGELOG] ([DATEEXECUTED], [AUTHOR], [LIQUIBASE], [DESCRIPTION], [COMMENTS], [MD5SUM], [ID], [FILENAME]) VALUES (GETDATE(), ‘nickbrown’, ‘1.9.5’, ‘Create Procedure’, ‘’, ‘b5faa8a4874f7953a71520bd3b0d46’, ‘test_procedure’, ‘D:\scratch\proctest.xml’)
GO

– Release Database Lock
UPDATE [DATABASECHANGELOGLOCK] SET [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL, [LOCKED] = 0 WHERE  ID = 1
GO

2

Nick,

try

    CREATE PROCEDURE [dbo].[test_procedure] AS SELECT 1

If you want to put more than one sql statements in one script use

            <![CDATA[ if object_id( 'dbo.test_procedure', 'P') is not null     drop procedure dbo.test_procedure GO

    CREATE PROCEDURE [dbo].[test_procedure]
    AS
    SELECT 1
    ]]>

or
           

note the endDelimiter=“GO” attribute. default delimiter for liquibase is ;

Cheers, Oleg

3

Thanks Oleg. That’s exactly what I needed.

Cheers,
Nick