Issues running ALTER PROCEDURE statements in T-SQL mod scripts

I'm trying to incorporate a SQL script into the image creation process within SQL Clone, however I cannot seem to run an ALTER PROCEDURE statement as we get the following error:

System.Data.SqlClient.SqlError: Incorrect syntax near the keyword 'PROCEDURE'.

Note that the use case is that our backups from production include stored procedures that have connections to production linked servers, and we need to replace these in our image to ensure they do not get cloned.

Without resorting to wrapping the whole procedure as a string and executing it as a statement, is there a way to run this?
Are there also any rules about using GO statements or comments, like in SQL Cmd mode?

Tagged:

Answers

  • Hmm, that's odd. There's no reason ALTER PROCEDURE shouldn't work. Could you share the modification script you're using? (you can send it to support@red-gate.com if you don't want it public.)

    GO and comments are supported - scripts should work just the same as they do in SSMS.

    Does the script you upload to SQL Clone work if you run it in SSMS against a restore of the same backup you're using to create the image?
    Software developer
    Redgate Software
  • JohnnyC3POJohnnyC3PO Posts: 5 New member
    The script we're using is one that we've been using for years in automated deployment scripts, and we've just replaced the variables with the specific settings we need for this script. The basic structure is as follows:

    ALTER PROCEDURE [dbo].[sp_Name]
    (
        @ID INT,    
        @SRC_ID INT
    )
    AS

    SET NOCOUNT ON

    DECLARE
        @name VARCHAR(300),
        @code INT

    --Contents of sproc here
    BEGIN

    END

    SET NOCOUNT OFF

    GO
  • Thanks. I've confirmed this is a bug in SQL Clone. I'll post back here when we've made any progress.
    Software developer
    Redgate Software
  • Hi, we have a fix for this, and will be releasing it next week.
    Software developer
    Redgate Software
Sign In or Register to comment.