Error when trying to execute a command in sql server

Question

I am using SQL Server 2014 and working on creating views from all the user database on to a Test database. The generated sql statement works fine when run from SSMS but when the execute command tries to run it I get the following error message.

SQL Query:

DECLARE @SQL NVARCHAR(MAX),
        @QueryStmt NVARCHAR(MAX)

    SET @SQL = ''

 SELECT @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
        SELECT @QueryStmt = ''USE TestDWH ''+ CHAR(13) + CHAR(10) + ''GO'' + CHAR(13) + CHAR(10) + ''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''''TestDWH.'' + s.name + ''.'' + o.name + '''''')  AND TYPE IN (''''V'''')) DROP VIEW '' + s.name + ''.'' + o.name +  CHAR(13) + CHAR(10) + ''GO'' + CHAR(13) + CHAR(10) + m.[definition] + CHAR(13) + CHAR(10) + ''GO'' 
        FROM sys.objects o JOIN sys.sql_modules m ON m.object_id = o.object_id AND o.[type] = ''V'' JOIN sys.schemas s ON s.schema_id = o.schema_id'
        FROM sys.databases 
  WHERE [database_id] > 4 

EXECUTE sp_executesql @SQL, N'@QueryStmt NVARCHAR(MAX) OUT', @QueryStmt OUT
EXECUTE(@QueryStmt)

Generated Query:

USE TestDWH
GO
IF OBJECT_ID('TestDWH.dbo.Test_vw_View1') IS NOT NULL DROP VIEW dbo.Test_vw_View1
GO

CREATE VIEW dbo.Test_vw_View1
AS

SELECT [Id]
      ,[Description]
  FROM [dbo].[Test_Table1]

GO

Error Message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 5
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 84
Incorrect syntax near 'GO'.

GO is not strictly T-SQL (therefore it can't be executed by dynamic SQL). See remarks in this link: msdn.microsoft.com/en-us/library/ms188037.aspx EDIT: to fix your query, I'd suggest using different batches of dynamic SQL separated by GO. e.g. @SQL = 'use ' + @myserver; execute myquery; — ZLK
– ZLK, Commented Aug 31, 2016 at 22:46
I replaced "GO" with ";" and still get "'CREATE VIEW' must be the first statement in a query batch." error. — ITHelpGuy
– ITHelpGuy, Commented Aug 31, 2016 at 22:50
I mean you should be breaking up your dynamic SQL and executing it in batches. Because a view does need to be the first in a batch. So you use your server first (that's the first batch). Then if the view exists in that server you drop it (that's the second batch). Then you create it (that's the third batch). They need to be done separately if you wish to use dynamic SQL. technically selecting the server and dropping the view can be done in the same batch, but not important. — ZLK
– ZLK, Commented Aug 31, 2016 at 22:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

In such cases I follow this logic:

DECLARE @cmdTbl TABLE(id INT IDENTITY,cmd NVARCHAR(MAX));

INSERT INTO @cmdTbl(cmd) VALUES(N'SELECT TOP 3 * FROM INFORMATION_SCHEMA.TABLES');
INSERT INTO @cmdTbl(cmd) VALUES(N'SELECT TOP 3 * FROM INFORMATION_SCHEMA.COLUMNS');

DECLARE @cmd VARCHAR(MAX);
DECLARE cur CURSOR FOR SELECT cmd FROM @cmdTbl ORDER BY id;
OPEN cur;

FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC(@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

First there is a declared table variable. You insert all your statements separately.

No `GO`!

Everywhere you want to set a GO just insert a new command.

The CURSOR will execute all statements in the order you inserted them.

In the first attempt comment-out the EXEC and copy all the PRINT-output into a new query window to check for syntax errors. Then execute the whole lot in one go...

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 31, 2016 at 23:10

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

Error when trying to execute a command in sql server

1 Answer 1

No `GO`!

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

No GO!

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

No `GO`!