Issues running ALTER PROCEDURE statements in T-SQL mod scripts

JohnnyC3PO

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?

Mark R

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?

JohnnyC3PO

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

Mark R

Thanks. I've confirmed this is a bug in SQL Clone. I'll post back here when we've made any progress.

Mark R

Hi, we have a fix for this, and will be releasing it next week.