1

I'm trying to run some raw SQL against a LocalDb instance:

dbContext.Database.Migrate();
dbContext.Database.ExecuteSqlRaw(sql);

Where the sql is read from this file:

/****** Object:  StoredProcedure [dbo].[aspnet_AnyDataInTables]    Script Date: 02/02/2021 22:11:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables] AS' 
END
GO
ALTER PROCEDURE [dbo].[aspnet_AnyDataInTables]
    @TablesToCheck int
AS
BEGIN
    -- Check Membership table if (@TablesToCheck & 1) is set
    IF ((@TablesToCheck & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
        BEGIN
            SELECT N'aspnet_Membership'
            RETURN
        END
    END
[...abbreviated...]

All this script does is create the stored procs required by the SQL Membership provider. I generated the script from an existing database.

This script runs fine when run from SQL Server Management Studio against the same LocalDb instance but it throws a SqlException when run from code:

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near ')'.
Must declare the scalar variable "@TablesToCheck".
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Must declare the scalar variable "@ApplicationId".
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@Feature".
[...abbreviated...]

How do I get it to run without errors?

Using EF Core 3.1.3.

Sergey Slepov
  • 1,861
  • 13
  • 33
  • 3
    `GO` isn't a Transact-SQL operator; it's a "Utility statement" for IDEs and CLIs to denote that the statements need to be run in separate batches. If you're trying to run this in an application that doesn't understand them then you'll get these errors. I doubt your EF application understand it, and is passing the `GO` statements as part of a single batch, rather than separating your file into many batches, and executing them in sequence. – Thom A Feb 03 '21 at 13:55
  • @Larnu Simple as that! Thanks. How do I get it to run though? I tried removing the GO's and got: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. – Sergey Slepov Feb 03 '21 at 13:59
  • You'll need to separate them into batches; unsurprisingly. The problem is you're expected EF to run a SQL file like it's an IDE/CLI that respects utility statements; it isn't. Asking how to get EF to treat `GO` as a batch separator is a completely different question (and not one I can answer). – Thom A Feb 03 '21 at 14:00
  • You have to split script by 'GO' to N scripts and run them separately one by one. – Svyatoslav Danyliv Feb 03 '21 at 14:01
  • Let me try that @SvyatoslavDanyliv – Sergey Slepov Feb 03 '21 at 14:02
  • If I split by 'GO' then SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER OFF will go into separate batches. Is that OK or do I need to prepend them to each batch? @Larnu – Sergey Slepov Feb 03 '21 at 14:04
  • If you need to ensure those settings are used, then yes, you'll need to append those command to each session. – Thom A Feb 03 '21 at 14:06

1 Answers1

3

As @Lamu pointed out, GO isn't a Transact-SQL operator. So the obvious solution would be to split the script into smaller batches:

string[] batches = sql.Split(new [] {"\nGO"}, StringSplitOptions.None);
foreach (string batch in batches)
{
    dbContext.Database.ExecuteSqlRaw(batch);
}
Sergey Slepov
  • 1,861
  • 13
  • 33