Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

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

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

Comments

Projects
None yet
2 participants
Contributor

kieranmaine commented Aug 17, 2012

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.

Owner

tommarien commented Jun 5, 2013

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

Contributor

kieranmaine commented Jun 7, 2013

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

Owner

tommarien commented Jun 7, 2013

@kieranmaine thanks ,looking forward to it :)

Contributor

kieranmaine commented Jun 9, 2013

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?

Owner

tommarien commented Jun 10, 2013

@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 :)

@tommarien tommarien closed this Jun 10, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment