This is a known issue on all versions of SQL Server, but may be worse in SQL Server 2012. I am not sure what will be involved in fixing this in Recorder 6. The WHERE clause needs to be added to the SQL used in the query on the temp table #Report.

The issues (No 99 in Mantis - our error reporting system is as follows).   
When producing very large reports they do not necessarily come out in the specified sort order. The number of records where this occurs depends on the system in use and may vary from one run of a report to another on the same system. This seems to be because recorder sorts the records when generating the temporary report table on the assumption the when this table is used in the final report the records will come out in this order. Microsoft do not guarantee this. To fix the sort order needs to be applied in the final query which generates the report and not at the stage when the temporary table.

It is also mentioned in the XML Report Guide. 

Large datasets. Records are not sorted by the order by clause when large datasets are returned. I suspect the problem is one of timing and that the point at which it occurs is dependent of machine configuration, memory etc. I find that it occurs at about 60,000 records. Nothing seesm to fix this with any certainty, but I have found that adding a WAITFOR statement at the end of the report sometimes helps. I suspect that this allows some internal process to complete.

WATFOR DELAY '00:01'&#59;  (this is equivalent to WAITFOR DELAY '00:01'; a one minute delay. )

Thanks for the reply Mike. Not sure I can make sense of it though. You say "The WHERE clause needs to be added to the SQL used in the query on the temp table #Report." My xml has no such table and I can't work out to what where clause you refer.

Guessing that you meant the final query which selects all the output records (from #MAIN_RESULTS), I have added an ORDER BY SORT_ORDER clause, but this has no effect on the output.

As for the WAITFOR (and I am not very clear whether you mean me to use WAITFOR DELAY '00&#58;01'&#59; or WAITFOR DELAY '00:01';, but the result is the same in either case), you say 'at the end of the report', but where precisely? If placed after the last select query there is a delay, but the results are still unsorted. If I put it after the final </Where> it causes an error when the report is run.

It is worth drawing attention to the fact that we are not talking a huge dataset here, although I have only noticed the problem in connection with our reserves, which might be expected to have more records than most sites. Still the initial query probably only returns one or two thousand records at most and this number is reduced greatly by the DISTINCT grouping of the query to no more than a few hundred results in the output.

I have tried removing the DISTINCT keyword from the main query & adding it to the final select query, this produces a different output, but still not well sorted (with or without the delay).

I can confirm though that the same query executed on our old SQL 2000 system sorts correctly, so whatever the problem is, it is exacerbated by SQL 2012.

I don't dispute however that we may yet have a problem of poor network timing or server operation with regard to our 2012 installation, as mentioned elsewhere.

There is a solution for this as far as XML reports are concerned, implemented in version 6.22 of Recorder.  See CCN 99 in the release notes.

For details see http://www.recorder6.info/WebHelpR6XML/

The new tag  OutputTableOrderby  replaces the Orderby tag , however the Orderby tag is retained for backwards compatibility so it is necessary to change  the report. If you were not using  Orderby, but relying on Order By in the your SQL then you will need to add an OutputTableOrderby tag (removing the sort in your SQL might speed things up a bit). 
.  The field you want to sort on will need to be in the report, but you can stop it showing using the Column tag (see example) . Note that  the sort SQL required the column name as used in the report with no table or table alias. 

<OutputTableOrderby name="by Taxonomic Sort Order" SQL="Order By sort_order"/>
<OutputTableOrderby name="by Scientific Name" SQL="Order By Actual_name"/>

If there is just one entry for OutputTableOrderby the sort will default to this without offering any option.

I attach a report which implements the new method.

That worked a treat thanks, although there is a small error in the web help file. On the sort order page the example code includes the line:

< OutputTableOrderby ="by Surname " SQL="Order By Surname" />

Which should I think read

< OutputTableOrderby name="by Surname " SQL="Order By Surname" />

Mike, not sure is this is one for you or for Chris. The sort order works fine now, for the most part, however we have noticed a few anomalies.

I have just been passed 3 separate outputs from an xml report which were sorted taxonomically using ITN.SORT_ORDER

In each of them there is a single non-flowering plant species which has ended up in amongst the flowering plants.

The examples I have are
Cystopteris fragilis (Fern)
Juniperus communis (Conifer)
Syntrichia virescens (Moss)

The sort is correct, according to the SORT_ORDER, so there must be a problem with the way the sort order field is populated.

I have no idea whether there is a wider problem, but I suspect there may be. A quick inspection seems to show a significant overlap between the ranges of the various taxon groups' sort orders.

Can you clarify for me Mike,

I was assuming that the SORT_ORDER field provided complete taxonomic ordering on its own, without additional reference to the taxon group order field, from taxon group.

If that is the case, why this (from your spreadsheet)?
Mentha pulegium (Flowering plant) 0001071C09090P0A00000000000000
Juniperus communis (Conifer) 0001071F0301060D03000000000000
Spiranthes spiralis (Flowering plant) 0001071F0408010G03000000000000

I feel like I am missing something fundamental here.

They shouldn't be out of order. I will take a more detailed look. Sort order shouldn't need the Taxon Group.

The flowering plant problem is being caused by the NHM having two different systems  for classifying plants.  I don't think I can fix in Recorder, because I can only tweak the order within that defined by the hierarchy. I will raise the issue with Chris.

I think perhaps there is something else going on with your dictionary as it doesn't look like Juniperus communis should be that far out of order.

Hi Rob

Run this query then rebuild Index_Taxon_Name (not necessary to do the other indexes). You will find that this has a major effect on the sort order of the flowering plants. If you think it is helpful I will include it in the next dictionary upgrade.

Update Organism set Weight = 8 where Organism_Key =  'NBNORG0000113032'