MODEL: Change stored procedure used to access data on SQL Server.
Hello,
I have been testing Pyramid. In a model, we can obtain data from a SQL Server from a table or executing a stored procedure. If I want to use another table, we can modify the model and select another table but I don't see how I can change the origin if it is a stored procedure.
How can I do it? Do I have to create a new model?
Thanks.
6 replies
-
Hi Francisco Javier Pío Barbuzano ,
I'm not sure I understand your question.
If you have a model, connected to SQL, showing a table and a Stored Procedure, you can always edit the model and make changes to it. (add remove tables.
The below image shows how you would connect to a Stored Procedure.
To get back to viewing the SQL table, select the SQL source, the tables should be listed on the right.
-
Hello Yakov,
Sorry. I had to give you more details.
The issue I see is when you check "Direct Query Data Source" in the SQL Server properties. I used the wizard to add only 1 SQL Server connection and then I couldn't change the query.
I'm going to test unchecking that option and using a SQL Query, but probably the application should avoid to create this configuration via wizard if you will not be able to change it later...
Thank you so much.
-
Well. Not using "Direct Query Data Source" config I have been able to change the stored procedure name. Thanks!
-
While it is possible to build models by extracting data from a SQL database using a stored procedure, we do not support Direct Query using stored procedures.
Good you found the solution yourself, but your model would never have worked using stored procedures in direct query mode.
If it is essential to do so, one workaround is to create a View in the RDMS based on the Stored Procedure.
Ian
-
Ian Macdonald But, it worked ;-). The problem was when I tried to change the name of the SP in Pyramid and I couldn't.
Best Regards.
-
Hmmm....
I don't quite understand how you connected a stored procedure SQL exec call using Custom SQL block if the Database selected had the Direct Query checked, as that removes all data flow connectors on the Source block.
Could you show me the data flow diagram using stored procedures and direct query mode?
Thanks,
Ian