SQL Script executed from resource throws errors on Entity Framework 6 Code First Migration

Question

In my project I'm using Entity Framework 6 Code First to connect with database. My solution contains two projects - DAL and Resources. DAL serves as Data Access Layer, while Resources is a library with, well, resources, like images or strings. In this project I've put 4 SQL Scripts - CREATE_VIEWS.SQL, DROP_VIEWS.SQL, CREATE_PROCS.SQL and DROP_PROCS.SQL.

In my first migration I want to execute CREATE_X.SQL scripts in Up() and DROP_X.SQL in Down(). Here's a part of my CREATE_VIEWS.SQL script:

---------------------------------------------------- 
--         Copyright © 2017 Jan Bońkowski         -- 
---------------------------------------------------- 
GO 
CREATE VIEW [dbo].[vwSomeView] 
AS 
SELECT  
    [dbo].[Activities].[ActivityId],
    ...
FROM [dbo].[Activities] 
INNER JOIN ... 

GO 
CREATE VIEW [dbo].[vwSomeView2] AS 
SELECT  
    [dbo].[Activities].[ActivityId],
    ... 
FROM [dbo].[Activities] 
INNER JOIN [dbo].[Teachers] ON ...
INNER JOIN [dbo].[ActivityPrototypes] ... ;

...
GO

And my CREATE_PROCS.SQL script

----------------------------------------------------
--         Copyright © 2017 Jan Bońkowski         --
----------------------------------------------------
GO
CREATE PROCEDURE [dbo].[spSomeProc1]
    @StudentId INT,
    @Result BIT OUT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @Count INT = 0
    SELECT 
        @Count = COUNT(*)
    FROM [dbo].[AspNetUsers]
    WHERE [dbo].[AspNetUsers].[Id] = @StudentId
    IF (@Count > 0)
        SET @Result = 1
    ELSE
        SET @Result = 0
END

GO
CREATE PROCEDURE [dbo].[spSomeProc2]
    @TeacherId INT,
    @Result BIT OUT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @Count INT = 0
    SELECT 
        @Count = COUNT(*)
    FROM ...
    IF (@Count > 0)
        SET @Result = 1
    ELSE
        SET @Result = 0
END

...
GO

Overall schematic of both scripts are:

GO
CREATE VIEW/PROC

GO
CREATE VIEW/PROC

etc

Those scripts are embedded in Resources project as a File resource with Build Action set to Content. In my migration I try to call them:

Sql(EnrollmentSystem.Resources.Properties.Resources.CREATE_VIEWS);
Sql(EnrollmentSystem.Resources.Properties.Resources.CREATE_PROC);

at the end of Up() method, but I receive errors after update-database:

Incorrect syntax near the keyword 'VIEW'
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO
'CREATE VIEW' must be the first statement in a query batch
Incorrect syntax near GO

I'd be very grateful if someone could help me with this issue. Thanks in advance for your time :)

@Sami where should I put the transaction and Try-catch structure? — Jan Bońkowski, Mar 04 '17 at 19:35
See [BEGIN TRANSACTION](https://msdn.microsoft.com/en-us/library/ms188929.aspx) and [TRY...CATCH](https://msdn.microsoft.com/en-us/library/ms175976.aspx) — Ilyes, Mar 04 '17 at 19:39
@Sami wrapped entire script in transaction and still nothing — Jan Bońkowski, Mar 04 '17 at 20:00
Basic issue is 'GO' is not a TSQL command, it is for tooling. See [here](http://stackoverflow.com/questions/25162187/entity-framework-how-to-create-sql-script-with-go-statements) for similar issue. — Steve Greene, Mar 04 '17 at 21:06

score 0 · Answer 1 · answered Mar 05 '17 at 16:22

0

Add a new, empty line after GO:

GO

CREATE VIEW [dbo].[vwSomeView] 
AS

answered Mar 05 '17 at 16:22

ErikEJ

40,951
5
75
115

SQL Script executed from resource throws errors on Entity Framework 6 Code First Migration

1 Answers1