Hi John

The exact statement is "There is an error in the sql code: The error message is "Object is Open""

The query is one written by Mike Weideli and works fine normally - it selects data based on entry date. It only when trying to select all data with this size of database (we have done this for smaller databases with no problem) that this error happens. It comes up after about 15 minutes of thinking time.

I have done a work around whereby I run one query to select data entered after a certain date and another that selects data before a certain date so we can do the full extraction we need in two parts.

Hi was there ever a fix for this? I have the same issues, same symptoms. The XML works well for smaller reports (date restricted) but fails on reporting across the whole database.

Best wishes,

Hi Mike,

As below - this works perfectly well on a small subset of data i.e. 2016 only, but on a larger subset i.e. 2000-2016 it fails. It worked fine last January on the complete NMRS database. The only software to have changed are MS updates to W2k8R2 and SQL 2008 plus of course R6 updates.

[code]
<?xml version = "1.0" ?>

<!--
                EMILY
                XML Report by Les Hill
                Version 1.0.0 - 13/1/2016
-->
<CustomReport
                title = "EMILY"
                menupath = "My Reports"
                description = "Returns nomenclature, exact abundance (else presence), life stage (excludes records where recorded life stage is not like user choice), specific date (only), grid reference (1km, 2km or 10km) and vice-county name."
>
<SQL>

DECLARE @GRIDSIZE INT

                <Where keytype = "Default">

SET @GRIDSIZE
<Condition field = "" operator = "equal" type = "OptionSet" name = "Square Size">
                <Option name = "1km" value = "1" />
                <Option name = "2km" value = "2" />
                <Option name = "10km" value = "0" />
</Condition>

                                SELECT TLI.LST_ITM_CODE AS [code]
                                ,ITN.ACTUAL_NAME
                                ,ITN.PREFERRED_NAME
                                ,ITN.COMMON_NAME
                                ,CASE WHEN TXD.ACCURACY =  'Exact' THEN TXD.DATA ELSE '0' END AS [Abundance]
                                ,MQ.SHORT_NAME AS [Life Stage]
                                ,dbo.LCReturnVagueDateShort(S.VAGUE_DATE_START, S.VAGUE_DATE_END, 'D') AS Date
                                ,dbo.LCRectifyGR(S.SPATIAL_REF, S.SPATIAL_REF_SYSTEM, @GRIDSIZE) AS [Grid Reference]
                                ,ALOC.ITEM_NAME AS [Vice-county]
                                FROM TAXON_OCCURRENCE AS TOCC
                                LEFT JOIN TAXON_DETERMINATION AS TDET ON TOCC.TAXON_OCCURRENCE_KEY = TDET.TAXON_OCCURRENCE_KEY AND TDET.PREFERRED = 1
                                LEFT JOIN TAXON_OCCURRENCE_DATA AS TXD ON TOCC.TAXON_OCCURRENCE_KEY = TXD.TAXON_OCCURRENCE_KEY
                                LEFT JOIN [SAMPLE] AS S ON TOCC.SAMPLE_KEY = S.SAMPLE_KEY
                                LEFT JOIN MEASUREMENT_QUALIFIER AS MQ ON TXD.MEASUREMENT_QUALIFIER_KEY = MQ.MEASUREMENT_QUALIFIER_KEY
                                LEFT JOIN INDEX_TAXON_NAME AS ITN ON ITN.TAXON_LIST_ITEM_KEY = TDET.TAXON_LIST_ITEM_KEY
                                LEFT JOIN TAXON_LIST_ITEM AS TLI ON ITN.TAXON_LIST_ITEM_KEY = TLI.TAXON_LIST_ITEM_KEY
                                LEFT JOIN LOCATION AS L ON S.LOCATION_KEY  =  L.LOCATION_KEY
                                LEFT JOIN Sample_Admin_Areas SAA ON SAA.Sample_Key = S.Sample_Key
                                LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = S.Survey_Event_Key
                                LEFT JOIN Admin_Area ASam ON ASam.Admin_Area_Key = SAA.Admin_Area_Key
                                LEFT JOIN Location LSurv ON Survey_Event.Location_Key = LSurv.Location_Key
                                LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key
                                LEFT JOIN Location_Admin_Areas LAA ON LAA.Location_Key = LSurv.Location_Key
                                LEFT JOIN Admin_Area ALOC ON ALOC.Admin_Area_Key = LAA.Admin_Area_Key
                                WHERE <Condition field = "dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE, 'Y')" operator = "greater than equal" type = "Number" name = "Start Year" />
                                AND <Condition field = "dbo.LCReturnDate(S.VAGUE_DATE_START, S.VAGUE_DATE_TYPE, 'Y')" operator = "less than equal" type = "Number" name = "End Year" />
                                AND <Condition field = "MQ.SHORT_NAME" operator = "like" type = "Text" name = "Life stage (like)" />
                                AND <Condition field = "TOCC.VERIFIED" operator = "not equal" type = "OptionSet" name = "Failed/Pending Verification" ><Option name = "Include" value = "3" /><Option name = "Exclude" value = "1" /></Condition>
                                AND <Condition field = "TOCC.CONFIDENTIAL" operator = "less than" type = "OptionSet" name = "Confidential" ><Option name = "Include" value = "2" /><Option name = "Exclude" value = "1" /></Condition>
                                AND <Condition field = "TOCC.CHECKED" operator = "greater than" type = "OptionSet" name = "Unchecked Records" ><Option name = "Include" value = "-1" /><Option name = "Exclude" value = "0" /></Condition>
                                AND <Condition field = "TOCC.ZERO_ABUNDANCE" operator = "less than" type = "OptionSet" name = "Zero Abundance" ><Option name = "Include" value = "2" /><Option name = "Exclude" value = "1" /></Condition>
                                AND S.VAGUE_DATE_TYPE = 'D'
                                ORDER BY ITN.SORT_ORDER

                </Where>
</SQL>
</CustomReport>
[/code]

Hi Mike, I applied the timeout change and it crashed out after about 45 minutes. Shall I increase the timeout or go for the '0' option?

BW

I believe you may well be right. I'm running some similar code I found via the Management Studio which may be the report I ran previously (I honestly can't remember!) This does split the JOINS between INNER and LEFT and may well be the version that worked. I'll advise either way.