Hi Experts,
I am not able to use the the create or alter command for views in FSM.
I believe the following SQL is valid and yet throws an error
create or alter view request_temp_view as
select * from request 
Best answer by SAMLK
View original
CREATE OR ALTER not working in FSM?
Userlevel 6
+26
Hi
Try the following
IF OBJECT_ID('dbo.request_temp_view', 'V') IS NOT NULL
DROP VIEW dbo.request_temp_view
GO
CREATE VIEW request_temp_view AS
SELECT * FROM REQUESTCheers!
Userlevel 6
+26
Hey
Run each script seperatly.
To make it simple
DROP VIEW request_temp_viewThen
CREATE VIEW request_temp_view AS
SELECT * FROM REQUESTCheers!
Thanks. yes, running separately works. although, I am trying to use a “create or alter” type of statement to get this to work in one go. To give some context, when we create views and the corresponding metadata for it, we can save the view script as following
So when we deliver to another environment, if the sql against this Table Script is present it automatically creates the view. The issue I have is when we make changes to an existing view and update the table script with a new sql. Now, “Create VIEW” no longer works becasue the target environment already has the view, and thus our changes don’t get automatically deployed upon delivery.
This is why I was exploring the option of using “CREATE OR ALTER” so that it would create if the view is not there or alter it if it were there.
Userlevel 6
+26
Hey
I think you are looking for create or replace
CREATE OR REPLACE VIEW request_temp_view AS
SELECT * FROM REQUESTCheers!
Userlevel 4
+12
Hi
Try out this query on your SQL query tool so that the same query must work on the package deployment.
IF OBJECT_ID('c_data_view') IS NULL
BEGIN
EXEC('CREATE VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END
ELSE
BEGIN
EXEC('ALTER VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
ENDmake sure the package deploying user has the EXECUTEDBQUERY, EXECUTEDBEDIT functions enabled under the role
Hi
Try out this query on your SQL query tool so that the same query must work on the package deployment.
IF OBJECT_ID('c_data_view') IS NULL
BEGIN
EXEC('CREATE VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
END
ELSE
BEGIN
EXEC('ALTER VIEW c_data_view AS SELECT TOP 10 * FROM REQUEST')
ENDmake sure the package deploying user has the EXECUTEDBQUERY, EXECUTEDBEDIT functions enabled under the role
Thanks mate. This works
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.