Search code examples
sql-servervisual-studioentity-framework-6entity-framework-migrationsinvoke-sqlcmd

Script generated by Update-Database has syntax errors while a normal Update-Database works correctly

I have a .NET Framework 4.8 project with using Entity Framework 6.2.0 with many migrations. My problem is, when I remove my database from my local computer and run Update-Database the database is created successfully, but when I run Update-Database -Script command and save my SQL script and then run the SQL script with

Invoke-Sqlcmd -InputFile ".\build\artifacts\migration.sql" -ServerInstance "(localdb)\MSSQLLocalDB"

I get many syntax errors, such as

Invoke-Sqlcmd : 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

A RETURN statement with a return value cannot be used in this context.

Why do I get these errors? And how can I resolve them? Why does a normal update-database work correctly, but the SQL script has syntax errors?

I think adding 'GO' statements to Entity Framework migrations with some modification can resolve my problem.

Solution

  • As we know and as you can see SQL Server - Must be first statement in query batch - what and why?

    From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

    Rules for Using Batches

    The following rules apply to using batches:

    • CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
    • A table cannot be changed and then the new columns referenced in the same batch.
    • If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

    The problem is when we generate script instead of using Update-Database directly, all of migration want to be run in batching and cause some error when we have some handmade SQL Script in migrations that violate SQL rules.

    So to resolve this problem we have to add /**/ GO in begin of statements. ( thanks to Ivan Stus )

    Thanks to Larnu to help me to investigate the problem.