0

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
sorosh_sabz
  • 2,356
  • 2
  • 32
  • 53
  • @Larnu My problem is this script generate automatically with `Update-Database -Script` but generated script has many many syntax error. I cannot resolve one by one – sorosh_sabz May 07 '21 at 13:44
  • 1
    Most likely `sqlcmd` is running some statements prior to the script, but not starting a new batch. Most like adding `SET ANSI_NULLS ON;\r\nGO\r\nSET QUOTED_IDENTIFIER ON;\r\nGO\r\n` (where `\r\n` represents a carriage return and line break) at the start of your script will fix the problem. Without the contents of `.\build\artifacts\migration.sql` this is difficult to suggest much more though. – Thom A May 07 '21 at 13:45
  • Ok I read https://dba.stackexchange.com/a/34661 and https://stackoverflow.com/q/54765112/1539100 , How to say Update-Database to add GO in scripts? – sorosh_sabz May 07 '21 at 14:08

1 Answers1

1

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.

sorosh_sabz
  • 2,356
  • 2
  • 32
  • 53