Running SQL script doesn't respect GO batch terminators

[sam-piper-lendus]

I'm trying to use an Azure/sql-action@v2.2.1 step in my workflow file to deploy an EF Core migrations script generated in the previous build stage, but it's failing because the GO batch separation lines in the script aren't being respected.

Here's the relevant parts of the pipeline file:

    - name: Login to Azure
      uses: Azure/login@v1
      with:
        client-id: ${{ secrets.AZURE_CLIENT_ID }}
        tenant-id: ${{ secrets.AZURE_TENANT_ID }}
        subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

    - name: Run SQL Migration Scripts
      uses: Azure/sql-action@v2.2.1
      with:        
        connection-string: ${{ secrets.SQL_CONNECTION_STRING }}
        path: migrations/migrations.sql

And the SQL script, which is generated entirely by the EF Core tools:

SET QUOTED_IDENTIFIER ON;
GO
BEGIN TRANSACTION;
GO
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240416042743_InitialSample'
)
BEGIN
    CREATE TABLE [Sample] (
        [Id] uniqueidentifier NOT NULL,
        [Name] nvarchar(50) NOT NULL,
        [CreatedOn] datetimeoffset NOT NULL,
        [ModifiedOn] datetimeoffset NOT NULL,
        [Version] rowversion NOT NULL,
        CONSTRAINT [PK_Sample] PRIMARY KEY ([Id])
    );
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240416042743_InitialSample'
)
BEGIN
    EXEC('
                    CREATE FUNCTION AtTimeZone
                    (
                        @Value datetimeoffset,
                        @TimeZoneId nvarchar(100)
                    )
                    RETURNS datetimeoffset
                    AS
                    BEGIN
                        DECLARE @Result datetimeoffset
                        SELECT @Result = @Value AT TIME ZONE @TimeZoneId
                        RETURN @Result
                    END');
END;
GO

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240416042743_InitialSample'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20240416042743_InitialSample', N'8.0.4');
END;
GO

COMMIT;
GO

The log output from execution is this:

Msg 102, Level 1[5](https://github.com/admin-clicklend/ClickLend/actions/runs/8714201808/job/23904161637#step:9:5), State 1, Server lendus-ae-dev-sql, Line 1
Incorrect syntax near ''.
Msg 208, Level 16, State 1, Server lendus-ae-dev-sql, Line 1
Invalid object name '__EFMigrationsHistory'.
Msg 208, Level 1[6](https://github.com/admin-clicklend/ClickLend/actions/runs/8714201808/job/23904161637#step:9:7), State 1, Server lendus-ae-dev-sql, Line 1
Invalid object name '__EFMigrationsHistory'.
Msg 208, Level 16, State 1, Server lendus-ae-dev-sql, Line 1
Invalid object name '__EFMigrationsHistory'.
Successfully executed SQL file on target database.

If GO statements in the script were working as expected then these errors wouldn't occur and the script should run fine.

Is there a way to configure the step so that batch termination using GO works?

[sam-piper-lendus]

I found a solution to this issue, which may help others using EF Core 8.

I updated the build stage in my pipeline to script the SQL file directly using dotnet tools, which produced slightly different output than the previous wrapper task I was using to generate SQL, steps below:

      - name: Install EF Core Tools
        run: dotnet tool install --global dotnet-ef

      - name: Generate SQL Migration Script
        run: dotnet ef migrations script --idempotent --context SqlDbContext --output ../migrations/migrations.sql
        working-directory: ./apps/project-containing-db-context

The latest EF tooling appears to generate a SQL script that works correctly with go-sqlcmd.

Closing as the issue was resolved without requiring any changes to sql-action usage.