SQL Server Performance Forum – Threads Archive
SET SHOWPLAN from ADO Application?
Im trying to write an application that is able to retrieve the showplan output for an arbitrary query. I make three seperate ADO calls, one to set showplan on, one to run the query, and then supposedly a third to turn showplan off again.I am under the assumption the scope of showplan setting is at the connection level? When I execute the 2nd ADO call, it does not return. I am not sure if this is a sudden server problem Im having (I have another recent post where USE pubs was hanging), or if I am trying to do something that ADO is not capable of. Im using an ADOQuery for all three statements, the first and last being ExecSQL method, and the 2nd (the actual query to profile) is using a simple Open method. Can anyone please advise how I can do this. Or just as good would be how I can get showplan output for a dynamic bit of SQL. Thanks!
Look for SHOWPLAN topic under books online, you may get information required. HTH Satya SKJ
As I understand it, SET SHOWPLAN does not return a result set. It will return a message like PRINT or RAISERROR. So check the ADO error collection and you will probably find the SHOWPLAN output there. /Argyle
Thanks for the answers. I had already read BOL (always my first port of call before posting), but this did not help matters.
I agree that SHOWPLAN returns output via messages of some sort (not recordset), but my problem is not retrieving the results, but that the query never returns to my application. My ADO code is similar to this… SQL.Clear;
SQL.Add(‘set showplan_text on’);
ExecSQL; SQL.Clear;
SQL.Add(‘select top 10 * from NEW_RH’);
try
Open; <—– *******
while (not eof) do After executing the asterisked line, control does not return to my application, and it must be terminated. An sql profile shows no activity on that connection, and so I believe this to be a problem with ADO perhaps. Im pinning my hopes on finding a way to get showplan results back to my program, but when faced with a problem of this nature Im not too sure how to proceed. The quoted code is actually Delphi, and Ive used both ADOStoredProc and ADOQuery objects with the same dire results. Thanks for any advice!
]]>