Altering several stored procedures/views/UDF in one migration fails

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a migration-file
2. Use 2 "Execute()" statements to alter 2 different stored procedures
3. Run the migration

What is the expected output? What do you see instead?
I expect both procedures to be altered. Instead I get "Incorrect syntax
near the keyword 'PROCEDURE'."

What version of the product are you using? On what operating system?
Subsonic 2.1 on Vista32

Stack trace:
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[]
arguments, SignatureStruct& sig, MethodAttributes methodAttributes,
RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[]
arguments, Signature sig, MethodAttributes methodAttributes,
RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags
invokeAttr, Binder binder, Object[] parameters, CultureInfo culture,
Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags
invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at SubSonic.CodeRunner.RunAndExecute(ICodeLanguage lang, String
sourceCode, String methodName, Object[] parameters) in
C:\svn\subsonicproject\trunk\SubSonic.Migrations\CodeRunner.cs:line 95
   at SubSonic.Migrations.Migrator.ExecuteMigrationCode(String
migrationFile) in
C:\svn\subsonicproject\trunk\SubSonic.Migrations\Migrator.cs:line 163
   at SubSonic.Migrations.Migrator.Migrate() in
C:\svn\subsonicproject\trunk\SubSonic.Migrations\Migrator.cs:line 127

Original issue reported on code.google.com by powerpo...@gmail.com on 1 Apr 2009 at 6:20

[GoogleCodeExporter]

This doesn't make sense to me - I can't repro as described.

Original comment by robcon...@gmail.com on 9 Apr 2009 at 3:29

[GoogleCodeExporter]

Closing - please re-open when you have more details.

Original comment by robcon...@gmail.com on 11 Apr 2009 at 1:43

• Changed state: WontFix

[GoogleCodeExporter]

This migration fails for me on SQL Server 2005 Express Edition:
Message in output window:
"There was an error running migration (034_Test):  Incorrect syntax near the 
keyword 'PROCEDURE'."

namespace DataAccess.Migrations
{
    public class Migration034 : Migration
    {
        public override void Up()
        {
            Execute(@"
CREATE PROCEDURE sp_testBug1
AS
BEGIN
    SELECT 1 + 1 as SUM1
END

CREATE PROCEDURE sp_testBug2
AS
BEGIN
    SELECT 2 + 2 as SUM2
END

");
        }

        public override void Down() { }
    }
}

Original comment by powerpo...@gmail.com on 16 Apr 2009 at 9:32

[GoogleCodeExporter]

You have either to use 2 Excute Statements:
Execute(@"CREATE ...");
Execute(@"CREATE ...");

or put a ";" between
END

CREATE

it should read:
END;

CREATE

otherwise the dbms thinks it is one statement and that failes

Original comment by j.steinblock@gmail.com on 16 Apr 2009 at 10:39

[GoogleCodeExporter]

I tried with the semicolon after "END", but no go. I also tried with 2 inserts, 
but i 
still get the same error message:

public override void Up()
{
    Execute(@"
        CREATE PROCEDURE sp_testBug1
        AS
        BEGIN
            SELECT 1 + 1 as SUM1
        END;"
    );
    Execute(@"
        CREATE PROCEDURE sp_testBug2
        AS
        BEGIN
            SELECT 2 + 2 as SUM2
        END;
    ");
}

Is this working OK for everyone else? Is it just me?

Original comment by powerpo...@gmail.com on 16 Apr 2009 at 2:13