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:
See Question&Answers more detail:osMsg 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'.