Executing CREATE VIEW & ALTER VIEW from SQLCMD

sqlcmd, t-sql

Solution

As per the manual:

The CREATE VIEW must be the first statement in a query batch.

Although, to tell the truth, that statement is rather misleading, because in actual fact `CREATE VIEW` must be the only statement in the batch, as you can ascertain for yourself from this illustration of a very simple test:

The error message in the Messages pane says `Incorrect syntax near keyword 'SELECT'`, but if you hover over the underscored `CREATE VIEW` statement, a hint message appears that reveals that you can't put anything neither before `CREATE VIEW` nor after its `SELECT` statement.

And it's precisely the same issue with `ALTER VIEW`.

So, you can have a `CREATE VIEW` and/or an `ALTER VIEW` statement(s) perform within a transaction (by delimiting them with `GO` keywords), but you will not be able to use `BEGIN TRY ... BEGIN CATCH` to catch exceptions raised by those statements.

Unless, as Aaron Bertrand correctly reminds me, you execute those statements as dynamic queries, using either `EXEC(…)` or `EXEC sp_executesql …`, something like this, perhaps:

…
 BEGIN TRY
   EXEC sp_executesql N'CREATE VIEW [dbo].[Test_View]
   AS SELECT * from Sample_table';   

   EXEC sp_executesql N'ALTER VIEW [dbo].[Sample_View]   
   AS SELECT * FROM table_9';        

   ALTER TABLE [Sample_Table_2] ADD Col_4 VARCHAR(20);

 END TRY 
 BEGIN CATCH     
…

Problem

I'm trying to execute a sql file with the following contents using sql cmd. ``` sqlcmd -S localhost\dbInstance -i Sample.sql -v filepath="C:\Sql\" ``` Sample.sql contents: ``` USE Sample_db GO BEGIN BEGIN TRANSACTION; BEGIN TRY CREATE VIEW [dbo].[Test_View] AS SELECT * from Sample_table; ALTER VIEW [dbo].[Sample_View] AS SELECT * FROM table_9; ALTER TABLE [Sample_Table_2] ADD Col_4 VARCHAR(20); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END GO ``` When I execute the sqlcmd, it throws the following error: ``` C:\Sql>sqlcmd -S localhost\dbInstance -i Sample.sql -v filepath="C:\Sql\" Changed database context to 'Sample_db'. Msg 156, Level 15, State 1, Server localhost\dbInstance, Line 5 Incorrect syntax near the keyword 'VIEW'. ``` Question: Why am I not able to create view and alter view from sqlcmd, while I'm able to alter table? When I comment out the CREATE VIEW and ALTER VIEW statement, the script executed fine. Thanks!

Original source