执行 CREATE VIEW &从 SQLCMD 更改视图 [英] Executing CREATE VIEW & ALTER VIEW from SQLCMD

问题描述

我正在尝试使用 sql cmd 执行包含以下内容的 sql 文件.

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\"

示例.sql 内容:

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

当我执行 sqlcmd 时，它抛出以下错误:

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'.

问题:为什么我无法从 sqlcmd 创建视图和更改视图，而我可以更改表?当我注释掉 CREATE VIEW 和 ALTER 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.

谢谢！

推荐答案

根据手册:

CREATE VIEW 必须是查询批处理中的第一条语句.

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

虽然说实话，那句话是有误导性的，因为实际上CREATE VIEW一定是批处理中的唯一语句，你可以确定自己从这个非常简单的测试插图中:

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:

Messages 窗格中的错误消息显示 Incorrect syntax near keyword 'SELECT'，但如果您将鼠标悬停在带下划线的 CREATE VIEW 语句上，会出现一条提示消息，表明您不能在 CREATE VIEW 之前或它的 SELECT 语句之后放置任何内容.

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.

这与 ALTER VIEW 完全相同.

因此，您可以在事务中执行CREATE VIEW 和/或ALTER VIEW 语句(通过分隔它们)使用 GO 关键字)，但您将无法使用 BEGIN TRY ... BEGIN CATCH 来捕获由这些语句引发的异常.

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.

除非，正如 Aaron Bertrand 正确提醒我的那样，您将这些语句作为动态查询执行，使用 EXEC(...) 或 EXEC sp_executesql ...，也许是这样的:

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     
…

这篇关于执行 CREATE VIEW &从 SQLCMD 更改视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！