Executing CREATE & ALTER VIEW from SQLCMD -Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Executing CREATE & ALTER VIEW from SQLCMD -Problem

Author

Topic

anonymous2009
Starting Member

8 Posts

Posted - 2012-05-05 : 17:26:25

Hello,
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!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-05 : 20:52:46

You cannot wrap the CREATE VIEW statement in a try catch block. Create view statement must be the first statement in a batch.

Are you trying to create a view from the sqlcmd? If you can describe what the problem you are trying to solve is, people on the forum may be able to offer possible solutions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources