How to retrieve additional text

RayMond · Post by **RayMond** » Sun 01 Jan 2006 07:58

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

Ikar · Post by **Ikar** » Tue 03 Jan 2006 16:00

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

RayMond · Post by **RayMond** » Tue 03 Jan 2006 17:01

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

RayMond · Post by **RayMond** » Tue 03 Jan 2006 17:10

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

Ikar · Post by **Ikar** » Fri 06 Jan 2006 12:19

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.

RayMond · Post by **RayMond** » Fri 06 Jan 2006 16:17

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

Ikar · Post by **Ikar** » Tue 10 Jan 2006 10:51

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.

RayMond · Post by **RayMond** » Wed 11 Jan 2006 18:26

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

Ikar · Post by **Ikar** » Tue 17 Jan 2006 15:16

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.

RayMond · Post by **RayMond** » Wed 18 Jan 2006 10:56

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