Revisiting this post I made a couple of years ago, I find I again need to try and get a stored procedure working as a plain table definition. The reason for having to try a stored procedure is that I need “OPTION (RECOMPILE)” on the end of my MSSQL statement and that messes up the query in DA, hence wrapping in a stored procedure.
Using the workaround in the previous topic, the definition works and the stored procedure executes correctly from within schema modeler :-
However, I never bottomed out how to successfully call that table from the client side. My call at the moment is as part of a dataset fill :-
RemoteDataAdapterR.Fill(cTagDataSet, ['TabRF002','TabRF003','TabRF004','TabRF005','TabRF006','TagAnalysis','TagSales','NodeData'], [lReq_TabRF002,lReq_TabRF003,lReq_TabRF004,lReq_TabRF005,lReq_TabRF006,lReq_Gen001,lReq_Gen002,lReq_Gen003]);
Where TabRF005 is defined as the stored procedure. The client side remains unchanged from the situation before I tried wrapping the statement in a stored procedure. The SQL has UNION statements which required it to be custom SQL in the first place within the schema model rather than on the client side.
When I execute the above data adapter fill (using a simple
select * from TabRF005 as the SQL behind the TableRequestInfoV6 structure, I get the following :-
Looking at SQL server profiler, no preparation or execution of the SQL is taking place, so I’m assuming this is still buried in the DA architecture. I could probably code around this with a custom method on the server, but I’d prefer not to break my client code to that extent if possible, especially when all I’m effectively trying to do is add “OPTION (RECOMPILE)” onto an existing working SQL statement.
I’m currently using version 22.214.171.1243 of DA. Am I missing something obvious ?