# Cannot create stored procedures in SQL Server

**URL:** <https://forum.liquibase.org/t/cannot-create-stored-procedures-in-sql-server/843>\
**Category:** General Discussion\
**Created:** [October 6, 2010, 2:01pm UTC](https://forum.liquibase.org/t/cannot-create-stored-procedures-in-sql-server/843 "2010-10-06T14:01:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nickbrown\_lb](https://avatars.discourse-cdn.com/v4/letter/n/ac91a4/32.png) [@nickbrown\_lb](https://forum.liquibase.org/u/nickbrown_lb)\
**Post date:** [October 6, 2010, 2:01pm UTC](https://forum.liquibase.org/t/cannot-create-stored-procedures-in-sql-server/843/1 "2010-10-06T14:01:00Z")

</div>

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&nbsp; ID = 1  
GO

---

<div class="post-metadata">

**Author:** ![taranenko](https://avatars.discourse-cdn.com/v4/letter/t/ee7513/32.png) [@taranenko](https://forum.liquibase.org/u/taranenko)\
**Post date:** [October 6, 2010, 2:01pm UTC](https://forum.liquibase.org/t/cannot-create-stored-procedures-in-sql-server/843/2 "2010-10-06T14:01:00Z")

</div>

Nick,

try

CREATE PROCEDURE [dbo].[test\_procedure] AS SELECT 1

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

&nbsp; &nbsp; &nbsp; &nbsp; \<![CDATA[ if object\_id( 'dbo.test\_procedure', 'P') is not null &nbsp; &nbsp; drop procedure dbo.test\_procedure GO

CREATE PROCEDURE [dbo].[test\_procedure]  
AS  
SELECT 1  
]]\>

or
&nbsp; &nbsp; &nbsp; &nbsp; 

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

Cheers, Oleg

---

<div class="post-metadata">

**Author:** ![nickbrown\_lb](https://avatars.discourse-cdn.com/v4/letter/n/ac91a4/32.png) [@nickbrown\_lb](https://forum.liquibase.org/u/nickbrown_lb)\
**Post date:** [October 6, 2010, 2:01pm UTC](https://forum.liquibase.org/t/cannot-create-stored-procedures-in-sql-server/843/3 "2010-10-06T14:01:00Z")

</div>

Thanks Oleg. That’s exactly what I needed.

Cheers,  
Nick