Is it possible to capture the text of an RPC SELECT statement from within the view which it calls? I have tried using dm_exec_cached_plans, dm_exec_query_stats and dm_exec_sql_text but query plans don't seem to be cached until after they are executed. Even dm_exec_query_memory_grants is unavailable until processing is completed.
I need to somehow get the entire query that is currently being executed. I believe dm_exec_requests only returns the current statement, not the full request. Is there some way to tap into sp_prepexec? I am also looking into Extended Events as a possible solution. Is there anything else I can do?
If that is a one time thing, either extended events or simple profiler/server-side trace should do it. Simply run profiler and monitor the server you run against, execute the view, then it should capture everything that goes through (make sure you select all the options in profiler eg. TSQL-SQL:StmtCompleted, TSQL-SQLStmtStarting, SP:SP:StmtStarting, SP:SP:StmtCompleted) and any other option you require to check.
No, I was trying to write a UDF which, when used in a view, could return information gleaned from the full text of the client's request. It would always be executed. This is explained more fully at the SO link I provided.
Since this is apparently is an untenable idea, I have devised another, less elegant work-around by restructuring the view itself. There is a performance penalty but the downstream error is avoided. My fundamental problem is the way the client application generates its SQL statements and there is nothing I do about that -- so, users will just have to accept whatever limitations may result.