Skip to content
This repository

Outputted file pollutes Stored Procs with "INSERT INTO VersionInfo" statement if batch separator not used #292

Closed
kieranmaine opened this Issue August 17, 2012 · 5 comments

2 participants

Kieran Tom Marien
Kieran

If a migration includes an CREATE/ALTER STORED PROCEDURE statement inside Execute.Sql without a batch separator, when the migration is generated using --output and --preview the generated SQL will append the INSERT INTO VersionInfo table statement to the bottom of stored procedure.

For example the migration:

[Migration(201208170000)]
    public class Test_Create_SP : Migration
    {
        public override void Up()
        {
            Execute.Sql(@"
                CREATE PROCEDURE [dbo].[_Test_SP]
                AS
                BEGIN
                    SELECT * FROM VersionInfo
                END");
        }

        public override void Down() { Execute.Sql(@"DROP PROCEDURE [dbo].[_Test_SP]"); }
    }

Generates this SQL:

/* Using Database sqlserver and Connection String Server=10.200.0.66; Database=just-eat_uk; User Id=just-eat_uk_front; Password=un!t3dk!n6d0m; */
/* Beginning Transaction */
/* PREVIEW-ONLY MODE ========================================================= */

/* 201208170000: Test_Create_SP migrating ==================================== */

/* ExecuteSqlStatement 
                CREATE PROCEDURE [dbo].[_Test_SP]
                AS
                BEGIN
                    SELECT * FROM VersionInfo
                END */

                CREATE PROCEDURE [dbo].[_Test_SP]
                AS
                BEGIN
                    SELECT * FROM VersionInfo
                END

/* 201208170000: Test_Create_SP migrated */

INSERT INTO [dbo].[VersionInfo] ([Version], [AppliedOn]) VALUES (201208170000, '2012-08-17T08:51:50')
/* Committing Transaction */
/* Task completed. */

After the migration has been applied, the first time the stored proc is run it returns the correct result set and also inserts into the VersionInfo table. The second time the stored proc is run it attempts to insert into the VersionInfo table again and throws the following error:

Msg 2601, Level 14, State 1, Procedure _Test_SP, Line 22
Cannot insert duplicate key row in object 'dbo.VersionInfo' with unique index 'UC_Version'.

If a batch separator (GO in the case of SQL Server) is inserted before the INSERT INTO VersionInfo statement this resolves the issue.

Tom Marien
Collaborator

@kieranmaine any chance you could create a pull request for this ?

Kieran

@tommarien I'll take a look at this at the weekend.

Tom Marien
Collaborator

@kieranmaine thanks ,looking forward to it :)

Kieran

Hi Tom. I've taken a look at this and realised a fix was never written. When this bug caused an issue we were using a process where we manually ran generated scripts against production DBs. To resolve the issue we changed our processes and used the Console runner to execute our migrations against the production DBs.

Equally we could have just manually inserted a GO statement at the end of the stored procedure.

In hindsight I think this issue is more user error than a bug and can be deleted. Thoughts?

Tom Marien
Collaborator

@kieranmaine thanks for taking a look at it. The fileprocessor still needs a lot of work, but i agree with your proposed solution. I'll close the issue :)

Tom Marien tommarien closed this June 09, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.