Using multiple SQL statements in EF migration - how to use GO to run in SSMS?

Question

I am renaming some columns using EF6 migrations, and need to update several functions, views and stored procedures which use the columns. I want to add these as separate Sql() calls within different private methods so the overall migration is easier to read.

When I do this, I can run the migration using update-database without any problems, but if I generate scripts (using update-database -script) the script doesn't run fully because 'ALTER FUNCTION' must be the first statement in a query batch..

I have tried putting a GO at the end of each Sql() statement, but when EF generates the script it removes the GO. If I try with two of them (see below), EF gives the error The argument 'sql' cannot be null, empty or contain only white space.

ALTER FUNCTION myFunc
...
GO
GO

I want to be able to run the migration through EF, or by generating a script, and for both to work without needing to change any configuration or manually modify the script.

Tim · Accepted Answer · 2019-04-26 16:46:07Z

1

I found an odd fix to this problem:

GO
--any arbitrary comment here
GO

My guess is that EF will remove GO from the end of a Sql() call:

Sql(@"
--actual SQL here
GO");

Including the arbitrary comment seems to mean that only the final GO is removed, but the initial one is kept. This works for me regardless of whether I run the migration through EF or generate a script to run in SSMS.

answered Apr 26, 2019 at 16:46

Tim

5,9038 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rudazzle · Accepted Answer · 2023-05-15 14:08:38Z

0

I encountered this same issue and found that Tim's "Arbitrary Comment" does resolve it. Also, removing the extra GO from the SQL being executed works as well. I believe that underlying provider is parsing the SQL into blocks to execute based on the GO statements and when it reads the second repeating GO there is no preceeding SQL so the error occurs.

Error SQL Script

DROP PROCEDURE IF EXISTS [dbo].[GetData] 
GO 
GO 
ALTER PROCEDURE [dbo].GetData] .... <some SP code here>
GO

Functioning SQL Script

DROP PROCEDURE IF EXISTS [dbo].[GetData] 
GO 
ALTER PROCEDURE [dbo].GetData] .... <some SP code here>
GO

edited May 15, 2023 at 14:08

answered May 12, 2023 at 17:48

Rudazzle

4051 gold badge6 silver badges15 bronze badges

2 Comments

Tim Over a year ago

That sounds useful, thanks. Which GO did you remove? i.e. do you have Sql(@"--real SQL GO --arbitrary comment") or Sql(@"--real SQL --arbitrary comment GO")?

Rudazzle Over a year ago

@Tim I had something similar to this, DROP PROCEDURE IF EXISTS [dbo].[GetData] GO GO ALTER PROCEDURE [dbo].GetData] .... GO that was causing an error. I simply removed the second GO. My SQL file now looks like this and EF code first runs it without issues DROP PROCEDURE IF EXISTS [dbo].[GetData] GO ALTER PROCEDURE [dbo].GetData] .... GO

Collectives™ on Stack Overflow

Using multiple SQL statements in EF migration - how to use GO to run in SSMS?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related