Migrations: Handle GO statements in SQL Server

[patricknolan]

I need to run a separate sql script just after the migration Up logic is executed.
The following sample works fine, except the script is creating quite a few Stored Procedures etc which requires the use of GO statements.

However, the MigrationBuilder.Sql does not seem to support the GO statement. I haven't tried splitting into multiple script files but I would rather not do this as I would require a lot.

Is this something which will be supported soon. What would be the best workaround for the moment?

private class SomeMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(File.ReadAllText(sqlFile));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    }
}

[smitpatel]

What is the error you are getting?

[patricknolan]

The error we are getting is below. I have also included below a sample script. The real script attempts to create about 80 separate stored procedures which is why we need the GO statements.

ERROR:

Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@id".
Must declare the scalar variable "@id".
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean openConnection, Boolean closeConnection) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues, Boolean manageConnection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at e5.Repository.Program.Main(String[] args) in D:\temp\e5.Repository\src\e5.Repository\Program.cs:line 18

SAMPLE SCRIPT

GO

CREATE PROCEDURE [dbo].[Enterprise_Delete]
(
    @Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ErrorNumber    INTEGER     -- the error code.
    DECLARE @ErrorMessage   NVARCHAR(100)   -- error message to return

    DELETE [Enterprise]
    WHERE ([Id] = @Id)

    SELECT @ErrorNumber  = @@ERROR
    IF (@ErrorNumber != 0)
    BEGIN
        SELECT @ErrorMessage = 'DELETE error on Enterprise table.  Id=' + CONVERT(NVARCHAR(36),@Id)
        GOTO ENDERROR
    END

    -- **************
    -- Error Handling
    -- **************
    GOTO ENDOK

    ENDERROR:
        BEGIN
            DECLARE @Source NVARCHAR(500)
            SET @Source = 'SQLServer:' + ' DB_ID=' + CONVERT(NVARCHAR(10),DB_ID()) + ' DB_NAME=' + DB_NAME()
            SET @Source = @Source + ' SProcName=' + OBJECT_NAME(@@PROCID) 
            SET @Source = @Source + ' ErrorNumber=' + CONVERT(NVARCHAR(10),@ErrorNumber) + ' ErrorMessage=' + @ErrorMessage 
            RAISERROR (@Source , 16, 1)
        END

    ENDOK:
        RETURN @ErrorNumber
END

GO

[smitpatel]

@patricknolan - Thanks for additional info. I tried running above script and I indeed hit error that Incorrect syntax near 'GO'.. When using MigrationBuilder.Sql method, EF appends ; & GO statements to SQL provided. If you remove the last GO from above SQL then migrations run successfully. There isn't anything specific migrations is doing apart from above which should prevent using GO in SQL.

[patricknolan]

Thanks for the response. I removed the last GO and it still fails with the following error. If I remove the first GO aswell it works. However, to create multiple stored procedures I need to include the GO statement.

Any ideas why it is working for you and not me?

Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@id".
Must declare the scalar variable "@id".
A RETURN statement with a return value cannot be used in this context.

[smitpatel]

@patricknolan - I got it running because I had removed starting GO also (seemed unnecessary) and was declaring only 1 procedure.
Following is the content of my Data.sql https://gist.github.com/smitpatel/16f67640c8ae1f3947d85d281e299a5a

Running following piece of code in console app also throws same exception.

var myConnection = new SqlConnection(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;");
myConnection.Open();
var command = new SqlCommand(File.ReadAllText("Data.sql"), myConnection);
command.ExecuteNonQuery();

Exception:

System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
Must declare the scalar variable "@Idd".
Must declare the scalar variable "@Idd".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at ModelApp.Program.Main(String[] args)

It seems that underlying SqlClient is not able to handle it. EF just passes content of sql after appending ; GO to sql client.

The same sql works fine if run directly into SqlServer Management Studio.

[patricknolan]

Do you know if there's an alternative recommended approach? I would have thought running a SQL script post the migration UP routine to create some stored procedures would be a fairly common requirement.

[bricelam]

GO is just tooling sugar for starting a new command. You should be able to work around this be splitting at the GO statements and putting the parts into their own Sql call.

We did this for you in EF6. We should consider adding it to EF Core.

[bricelam]

Note to implementer: Here is the code for handling utility statements in EF6.