CODEWITHSUNDEEP
sqlsql-server
Allan F
Allan F

Reputation: 2298

SQL Server 2012 message 'CREATE VIEW' must be the first statement in a query batch

If I run a script as per below, I get an error:

Msg 111, Level 15, State 1, Line 5
'CREATE VIEW' must be the first statement in a query batch

Is this a SQL Server 2012 thing? i.e. have Microsoft improved this in newer versions of SQL Server ?

I can get script to work fine by doing steps separately ..

USE [SAASReports]
GO

/****** Object:  View [Clinical].[Patient_Clinical_Records_Rpt_View]    Script Date: 03/04/2019 09:36:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]
--ALTER VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]

CREATE VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]
AS
    SELECT
        pcr.[Case Number],
        pcr.[Centre],
        region.DirectorateName AS [Metro Country],
        subr.SubregionName AS [Region],
        st.stationname AS [Station],
        pcr.[Call Sign]
;

Upvotes: 0

Views: 91

Answers (1)

DarkRob
DarkRob

Reputation: 3833

It is good practice to add GO after one successful batch completion of your query in sql

    USE [SAASReports]
    GO

    /****** Object:  View [Clinical].[Patient_Clinical_Records_Rpt_View]    Script Date: 03/04/2019 09:36:03 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    DROP VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]
    --ALTER VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]

    GO

    CREATE VIEW [Clinical].[Patient_Clinical_Records_Rpt_View]
    AS
        SELECT
            pcr.[Case Number],
            pcr.[Centre],
            region.DirectorateName AS [Metro Country],
            subr.SubregionName AS [Region],
            st.stationname AS [Station],
            pcr.[Call Sign]
    ;

    GO

Although it is not required but you may also add GO in the last.

Upvotes: 2

Related Questions