Devart Forums

How can I retrieve the text that is generated when I use the

SET STATISTICS IO ON

option when running my queries? I tried retrieveing it from the connection's OnInfoMessage and OnError events but failed. Thanks.

Ray Mond

You should use TMSQuery.OpenNext with property FetchAll set to True. The message must be available in MSConnection.OnInfoMessage event handler.

Now this is strange. I have a TMSQuery with the following SQL:

SET STATISTICS IO ON
SELECT * FROM pubs..authors

I can get the statistics on the OnInfoMessage event only if I set FetchAll to False, and call the OpenNext method on the TMSQuery.

Is there another option to set? I need FetchAll to be true, or should I just enter a large number in the FetchRows property? Thanks.

Ray Mond

I should also add that OpenNext actually returns False because there is actually no 2nd dataset. So I guess the message is not attached to the 1st dataset, but is additional data attached to a phantom 2nd dataset, but requires FetchAll = False to retrieve?

Ray Mond

We have thoroughly checked the problem. The source of it is in OLEDB. Unfortunately this functionality is insufficiently described by Microsoft and we haven't find any keys to avoid the problem.

Thanks for taking the time to look into this. I also checked the SQL Server results using a packet sniffer, and I do see the statistics returned together with the results with FetchAll = True. But somehow, I just can't get at it....

Could you pls elaborate on what FetchAll does? Does the server perform the entire query, cache the results, then only return part of the results to the client, or does it act similar to the FASTFIRSTROWS hint? I need to know if setting FetchAll affects the query plan in any way. On my tests, it doesn't seem to. Thanks.

Ray Mond

FetchAll = True gets all data from server to client at once, FetchAll = False doesn't. This is no differences in OLE DB or server settings.

> if setting FetchAll affects the query plan in any way.
> On my tests, it doesn't seem to.

Difference affects only execution of second statement if the first is still underfetched.

Thanks, it looks like I need to use FetchAll = False. After some experimentation, it looks like I also need to ensure I set FetchRows to a value large enough to retrieve the entire dataset at once, otherwise I can't retrieve the info message. Are there any adverse effects to setting FetchRows = 10000 all the time?

Does any of your other products offer a way to get the info message easily from SQL Server? Thanks.

Ray Mond

We peformed a deeper testing once more. We are sure that this is OLEDB problem. There is a way to avoid it. However, there are few caveats with the approach. For example, quantity of rows in query should not be divisible by FetchRows property.
This solution will work with any values of FetchAll property. We recommend setting FetchRows property to value, which is greater than record count in selection, if selection is moderate-sized.
This capability will be available in nearest build.

Thanks so much for taking an in-depth look at this. Looking forward to the next build.