Error Deploying Stored Procedure

William Fields — Thu, 14 Nov 2013 13:33:55 GMT

I'm getting the following error when trying to deploy to a new database:

X 11/14/2013 11:53:51 AM -> An error occurred while creating the stored procedure [dbo].[CreateConstraint].
X 11/14/2013 11:53:51 AM -> SqlException
X 11/14/2013 11:53:51 AM ->   Incorrect syntax near 'GO'.

Below is the code for the SP (copied from the create procedure section of the DDT), but I can copy/paste into SSMS and the code runs without error.

???

-- Add the parameters for the stored procedure here

@p_cTableName VARCHAR(100) = '',
@p_cConstraintName VARCHAR(4000) = '',
@p_cConstraintExp VARCHAR(4000) = ''

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))
BEGIN
    EXEC ('ALTER TABLE ' + @p_cTableName + ' DROP CONSTRAINT ' + @p_cConstraintName)
END

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))
BEGIN
    EXEC ('ALTER TABLE ' + @p_cTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @p_cConstraintName + ' CHECK (' + @p_cConstraintExp + ')')
END

IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))
BEGIN
EXEC ('ALTER TABLE ' + @p_cTableName + ' CHECK CONSTRAINT ' + @p_cConstraintName)
END
END
GO

RE: Error Deploying Stored Procedure

Trent L. Taylor — Thu, 14 Nov 2013 13:33:55 GMT

LOL...yes, the GO a batch separator and is actually only something you will use within SSMS. You can even change this through the Options under the Query Exection. This has gotten me before too, welcome to the club :)

RE: Error Deploying Stored Procedure

William Fields — Thu, 14 Nov 2013 13:24:10 GMT

Nevermind...

I removed the GO and all is well.

StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Error Deploying Stored Procedure

Error Deploying Stored Procedure

RE: Error Deploying Stored Procedure

RE: Error Deploying Stored Procedure