May 5, 2012 at 3:24 pm
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!
May 5, 2012 at 4:52 pm
The problem isn't with SQLCMD. It's that the DDL CREATE VIEW and ALTER VIEW must be the only statement in a batch. And that is not the case. I think the only way to wrap a CREATE VIEW in a try/catch is to use dynamic SQL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply