Geek asks and answers

Executing CREATE VIEW and ALTER VIEW from SQLCMD

I am 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 Content:

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 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 can not I create a view and change the view from sqlcmd, while I can change the table? When I comment on the CREATE VIEW and ALTER VIEW statements, the script runs fine.

Thank!

+3
source share
1 answer

According to the manual:

CREATE VIEW must be the first statement in the query package.

, , , CREATE VIEW , :

CREATE VIEW issue illustration

"" Incorrect syntax near keyword 'SELECT', CREATE VIEW, , , CREATE VIEW, SELECT.

ALTER VIEW.

, CREATE VIEW / ALTER VIEW ( GO), BEGIN TRY ... BEGIN CATCH .

, , , EXEC(…), EXEC sp_executesql …, - , :

…
 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     
…
+6

More articles:


All Articles