Advanced Query + Create View

Advanced Query + Create View

  
Hi.

I want to create\update a view from an Advanced Query.
The idea is to have a timer that executes when published and updates all the views from advanced queries.

I have something like this, that works fine in the query editor of SQL Server Management Studio:

IF Object_ID('[dbo].[MY_VIEW]') IS NOT NULL 
        DROP VIEW [dbo].[MY_VIEW]
GO
 
CREATE VIEW [dbo].[MY_VIEW] 
AS 
  ... MY VIEW CODE ...
END


But, I always get erros when executing from the Advanced Query:
- Incorrect syntax near 'GO'
- 'Create View' must be the first statement in a query batch

Any ideas how to put this working?
The runtime database user does not have privileges to do this. You won't get it working...
Why you need to change the view? If the columns are changed, the view becomes useless.
And if you change both the view and all the view readers, probably you never needed the view in first place.
You can change a view and not change it's structure. You may need to add or change a condition in the where clause, or something else. 

I want to be sure that a view created\changed in the dev environment, is replicated to the other environments.
Since Outsystems does not create or manage views (http://www.outsystems.com/ideas/1019/native-support-for-database-views/) it was a way to achieve that with the help of the platform.
Sometimes we also end with the same entity with different prefix across all multiple environments. This could solve that problem, because the platform would create the view with the correct prefix on each environment.

Hello,
I was just thinking: can you create the view if it is actually the first action on the script? If so, you could check if the view exists in a different action in the flow, and decide to run a CREATE or UPDATE view before executing the Advanced Query.

Best regards,
Pedro Vieira
Pedro,

The runtime user does not have privilege to create objects in the database! When you publish the platform uses another user to create the tables corresponding to the entities. This second user has this privilege but the runtime user (the one used by the applications themselves) doesn't.

André, ok, but I would like to get this work myself also, for the sake of executing load tests.

(Parentesis: When you create load tests with Visual Studio you can parameterize your tests using a database. But when your parameters must be slightly different from the actual data stored in the database, you cannot change them easily in your tests (not without writing some code, at least), so the best is to to create views. I am totally against hacking too much the Outsystems platform, but in this case you really have to create views, which will be dependant on the table structure.)

I can see a few alternatives:
- Generate the view script and store it in a table, then have an SQL Server trigger/job to read the scripts from the table and execute them to create/update the views (using a specific user).
- Create the views in a different database, which is manually created and managed.

Carlos Henriques, a question: how exactly do you get the physical table names that you need in your views?

Best regards,
Pedro Vieira
Right now I'm creating all the views directly in the database, using the physical table names.
This has a problem, because if the entity has different physical table names across the environments (DEV, QA PROD), I need to make specific views for each one.

That's why the platform could be the solution, because it could translate automatically the entities into the corresponding physical table names, and dynamically generate my views on each environment. And at the same time guarantee that all the views are synced.
Hi Carlos,

Check out this component