SQL Server Performance Forum – Threads Archive
PLS HELP: ROBUST PLAN HINT?
This query was working fine until addtional data was populated in the database. Im using COLDFUSION 5 to deliver the results. Im not sure what I must do here? I would not know where to start. Please could you look at the query and tell me what I must do to the code? This very urgent !! Many Thanks Wayne Gibson Error Occurred While Processing RequestError Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
SQL = "SELECT TOP 100 PERCENT RC.intRiskCategoryID, RC.nvrRiskCategoryName, RC.bitHasDescription, RC.bitHasSubCategory, RC.bitMethodOptionUsed, RSC.bitIsVirtualRSC, RSC.intRiskSubCategoryID, RSC.bitIsVirtualRSC, RSC.nvrRiskSubCategoryName, RSC.ntxRiskSubCategoryDescription, RSC.intRiskSubCategoryImpact, RSC.intRiskSubCategoryProbability, RSC.bitHasOverallDescription, RSC.bitRiskSubCategoryWillBeAssessed, RSC.bitSubCategoryRisksHaveDetail, RSC.bitRiskManagerToProvideGuidance, IRA.intIndividualRiskAssessmentID, IRA.intIndividualRiskAssessmentNumber, IRA.chrIndividualRiskAssessmentAlpha, IRA.nvrIndividualRiskAssessmentHeading, IRA.ntxIndividualRiskAssessmentDetails, IRA.ntxIndividualRiskAssessmentDescription, IRA.ntxIndividualRiskAssessmentGuidance, IRA.bitIndividualRiskAssessmentNoRisk, IRA.bitIndividualRiskAssessmentIsPublished, ORSC.intOrganisationalRiskSubCategoryID, ISNULL(ORSC.intBusinessUnitID, ?) AS intBusinessUnitID, ISNULL(ORSC.intDivisionID, ?) AS intDivisionID, ISNULL(ORSC.intSubDivisionID, ?) AS intSubDivisionID, ORSC.nvrOrganisationalRiskSubCategoryName, ORSC.ntxOrganisationalRiskSubCategoryDescription, ORSC.intOrganisationalRiskSubCategoryImpact, ORSC.intOrganisationalRiskSubCategoryProbability, (ORSC.intOrganisationalRiskSubCategoryImpact * ORSC.intOrganisationalRiskSubCategoryProbability) AS UIP, ORSC.bitOrganisationalHasOverallDescription, ISNULL(ORSC.bitOrganisationalRiskSubCategoryWillBeAssessed, RSC.bitRiskSubCategoryWillBeAssessed) AS bitOrganisationalRiskSubCategoryWillBeAssessed, ORSC.bitOrganisationalSubCategoryRisksHaveDetail, ORSC.bitOrganisationalRiskManagerToProvideGuidance, ISNULL(OIRA.bitOrganisationalRiskSubCategoryCompleted, ORSC.bitOrganisationalRiskSubCategoryCompleted) AS bitOrganisationalRiskSubCategoryCompleted, OIRA.intOrganisationalIndividualRiskAssessmentID, ISNULL(OIRA.intBUID, ?) AS intBUID, ISNULL(OIRA.intDivID, ?) AS intDivID, ISNULL(OIRA.intSubDivID, ?) AS intSubDivID, OIRA.bitOrganisationalIndividualRiskAssessmentRiskRated, OIRA.intOrganisationalIndividualRiskAssessmentImpact, OIRA.intOrganisationalIndividualRiskAssessmentProbability, OIRA.intOrganisationalIndividualRiskAssessmentRating, (OIRA.intOrganisationalIndividualRiskAssessmentImpact * OIRA.intOrganisationalIndividualRiskAssessmentProbability) AS IP, OIRA.bitOrganisationalIndividualRiskAssessmentCompleted, bitOrganisationalIndividualRiskAssessmentRiskCompleted, ISNULL(CM.intControlMeasureID, ?) AS intControlMeasureID, CM.intControlMeasureResponsiblePerson, CM.intControlMeasureCheckFrequency, CM.dtmControlMeasureDateImplemented, CM.intControlMeasureControlEffectiveness, CM.ntxControlMeasureDescription, MP.intMonitoringPlanID, MP.intMonitoringPlanResponsiblePerson, MP.intMonitoringPlanCheckFrequency, MP.dtmMonitoringPlanDateStarted, MP.ntxMonitoringPlanDescription, ISNULL(ORM.intOutcomeResultOfMonitoringID, ?) AS intOutcomeResultOfMonitoringID, ISNULL(ORM.intOutcomeResultOfMonitoringOutcome, 0) AS intOutcomeResultOfMonitoringOutcome, ORM.ntxOutcomeResultOfMonitoringResults, ORM.dtmOutcomeResultOfMonitoringReviewDate, ORM.dtmOutcomeResultOfMonitoringDateMonitored, ISNULL(ORM.bitOutcomeResultOfMonitoringSignedOff, ?) AS bitOutcomeResultOfMonitoringSignedOff, ISNULL(ORM.intClassificationIssueAndResolutionClassification, ?) AS intClassificationIssueAndResolutionClassification, ISNULL(ORM.intClassificationIssueAndResolutionImportance, ?) AS intClassificationIssueAndResolutionImportance, ORM.ntxClassificationIssueAndResolutionRemedialActionRequired, ORM.dtmClassificationIssueAndResolutionDueDateOfResolution, ORM.dtmClassificationIssueAndResolutionActualResolutionDate, ISNULL(ORM.intClassificationIssueAndResolutionRMPersonToNotify, ?) AS intClassificationIssueAndResolutionRMPersonToNotify, ISNULL(ORM.intClassificationIssueAndResolutionResponsiblePerson, ?) AS intClassificationIssueAndResolutionResponsiblePerson, ISNULL(ORM.intClassificationIssueAndResolutionLossIncurred, ?) AS intClassificationIssueAndResolutionLossIncurred, ORM.mnyClassificationIssueAndResolutionAmount, ISNULL(ORM.bitClassificationIssueAndResolutionSignedOff, ?) AS bitClassificationIssueAndResolutionSignedOff FROM dbo.tblOutcomeResultOfMonitoring ORM RIGHT OUTER JOIN dbo.tblMonitoringPlans MP ON ORM.intMonitoringPlanID = MP.intMonitoringPlanID RIGHT OUTER JOIN dbo.tblControlMeasures CM ON MP.intControlMeasureID = CM.intControlMeasureID RIGHT OUTER JOIN dbo.tblOrganisationalIndividualRiskAssessment OIRA ON CM.intOrganisationalIndividualRiskAssessmentID = OIRA.intOrganisationalIndividualRiskAssessmentID RIGHT OUTER JOIN dbo.tblRiskCategory RC INNER JOIN dbo.tblRiskSubCategory RSC ON RC.intRiskCategoryID = RSC.intRiskCategoryID INNER JOIN dbo.tblOrganisationalRiskSubCategory ORSC ON RSC.intRiskSubCategoryID = ORSC.intRiskSubCategoryID INNER JOIN dbo.tblIndividualRiskAssessment IRA ON RSC.intRiskSubCategoryID = IRA.intRiskSubCategoryID ON OIRA.intIndividualRiskAssessmentID = IRA.intIndividualRiskAssessmentID AND OIRA.intOrganisationalRiskSubCategoryID = ORSC.intOrganisationalRiskSubCategoryID WHERE (RC.bitDeleted = ?) AND (RSC.bitDeleted = ?) AND (IRA.bitDeleted = ?) AND (ORSC.bitDeleted = ?) AND (OIRA.bitDeleted = ?) AND (CM.bitDeleted = ?) AND (MP.bitDeleted = ?) AND (ISNULL(ORM.bitIsDeleted, ?) = ?) AND (ISNULL(ORSC.intBusinessUnitID, ?) = ?) AND (ISNULL(ORSC.intDivisionID, ?) = ?) AND (ISNULL(ORSC.intSubDivisionID, ?) = ?) AND (IRA.bitIndividualRiskAssessmentIsPublished = ?) AND (IRA.bitIndividualRiskAssessmentNoRisk = ?) AND (ISNULL(OIRA.intBUID, ?) = ?) AND (ISNULL(OIRA.intDivID, ?) = ?) AND (ISNULL(OIRA.intSubDivID, ?) = ?)" Query Parameter Value(s) – Parameter #1 = 23 Parameter #2 = 55 Parameter #3 = 0 Parameter #4 = 23 Parameter #5 = 55 Parameter #6 = 0 Parameter #7 = 0 Parameter #8 = 0 Parameter #9 = 0 Parameter #10 = 0 Parameter #11 = 0 Parameter #12 = 0 Parameter #13 = 0 Parameter #14 = 0 Parameter #15 = 0 Parameter #16 = 0 Parameter #17 = 0 Parameter #18 = 0 Parameter #19 = 0 Parameter #20 = 0 Parameter #21 = 0 Parameter #22 = 0 Parameter #23 = 0 Parameter #24 = 0 Parameter #25 = 23 Parameter #26 = 23 Parameter #27 = 55 Parameter #28 = 55 Parameter #29 = 0 Parameter #30 = 0 Parameter #31 = 1 Parameter #32 = 0 Parameter #33 = 23 Parameter #34 = 23 Parameter #35 = 55 Parameter #36 = 55 Parameter #37 = 0 Parameter #38 = 0 Data Source = "AXIS"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (728:2) to (728:50) in the template file D:Inetpubwwwrootaxis(03_03_2003)_RevisedAppMonitoringResults.cfm.
Date/Time: 04/09/03 14:57:22
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Space Station; .NET CLR 1.0.3705)
Remote Address: 192.168.0.61
HTTP Referrer:http://axis.axissupport.co.za/App/AdminUsers.cfm
Query String: Frompage=/App/AdminUsers.cfm
If you look in SQL Server Books Online you will find that ROBUST PLAN is a query hint and should be entered last in your query by specifying the OPTION keyword. In your case
"SELECT …. AND (ISNULL(OIRA.intSubDivID, ?) = ?) OPTION (ROBUST PLAN)" /Argyle
This is a tip from my website: ROBUST PLAN: Tells the Query Optimizer to create a query execution plan that works for the maximum potential row size, even if this means that performance will be hurt. In rare cases, when columns have very wide VARCHAR columns, the Query Optimizer may create an execution plan that creates intermediate tables. If this happens, and if there are any internal operations that store and process rows in these tables, it is possible that the row size might exceed SQL Server’s maximum limit. If this happens, an error is generated and the query is not run. But if this hint is added, the Query Optimizer will not use any plan that could potentially cause this problem. Only use this hint if you have received an error message about exceeding the row size limit. And Argyle is correct about how to implement it. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
This is a tip from my website: ROBUST PLAN: Tells the Query Optimizer to create a query execution plan that works for the maximum potential row size, even if this means that performance will be hurt. In rare cases, when columns have very wide VARCHAR columns, the Query Optimizer may create an execution plan that creates intermediate tables. If this happens, and if there are any internal operations that store and process rows in these tables, it is possible that the row size might exceed SQL Server’s maximum limit. If this happens, an error is generated and the query is not run. But if this hint is added, the Query Optimizer will not use any plan that could potentially cause this problem. Only use this hint if you have received an error message about exceeding the row size limit. And Argyle is correct about how to implement it. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I should have known there was a tip on this site [<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />/Argyle
Thank you so much for your quick response. Im not too clued up with this. Can you not perhaps look at the code and show me how to implement please?<br /><br /><b>This is the code before the query runs:</b><br /><br /><cfquery name="GetCM_MP_Items" datasource="Axis"><br />SELECT TOP 100 PERCENT RC.intRiskCategoryID, RC.nvrRiskCategoryName, RC.bitHasDescription,<br />RC.bitHasSubCategory, RC.bitMethodOptionUsed, RSC.bitIsVirtualRSC, RSC.intRiskSubCategoryID,<br />RSC.bitIsVirtualRSC, RSC.nvrRiskSubCategoryName, RSC.ntxRiskSubCategoryDescription,<br />RSC.intRiskSubCategoryImpact, RSC.intRiskSubCategoryProbability, RSC.bitHasOverallDescription,<br />RSC.bitRiskSubCategoryWillBeAssessed, RSC.bitSubCategoryRisksHaveDetail,<br />RSC.bitRiskManagerToProvideGuidance, IRA.intIndividualRiskAssessmentID,<br />IRA.intIndividualRiskAssessmentNumber, IRA.chrIndividualRiskAssessmentAlpha,<br />IRA.nvrIndividualRiskAssessmentHeading, IRA.ntxIndividualRiskAssessmentDetails,<br />IRA.ntxIndividualRiskAssessmentDescription, IRA.ntxIndividualRiskAssessmentGuidance,<br />IRA.bitIndividualRiskAssessmentNoRisk, IRA.bitIndividualRiskAssessmentIsPublished,<br />ORSC.intOrganisationalRiskSubCategoryID,<br />ISNULL(ORSC.intBusinessUnitID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> AS intBusinessUnitID,<br />ISNULL(ORSC.intDivisionID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intDivisionID,<br />ISNULL(ORSC.intSubDivisionID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intSubDivisionID,<br />ORSC.nvrOrganisationalRiskSubCategoryName, ORSC.ntxOrganisationalRiskSubCategoryDescription,<br />ORSC.intOrganisationalRiskSubCategoryImpact, ORSC.intOrganisationalRiskSubCategoryProbability,<br />(ORSC.intOrganisationalRiskSubCategoryImpact * ORSC.intOrganisationalRiskSubCategoryProbability) AS UIP,<br />ORSC.bitOrganisationalHasOverallDescription,<br />ISNULL(ORSC.bitOrganisationalRiskSubCategoryWillBeAssessed, RSC.bitRiskSubCategoryWillBeAssessed) AS bitOrganisationalRiskSubCategoryWillBeAssessed,<br />ORSC.bitOrganisationalSubCategoryRisksHaveDetail, ORSC.bitOrganisationalRiskManagerToProvideGuidance,<br />ISNULL(OIRA.bitOrganisationalRiskSubCategoryCompleted, ORSC.bitOrganisationalRiskSubCategoryCompleted) AS bitOrganisationalRiskSubCategoryCompleted,<br />OIRA.intOrganisationalIndividualRiskAssessmentID,<br />ISNULL(OIRA.intBUID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intBUID,<br />ISNULL(OIRA.intDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intDivID,<br />ISNULL(OIRA.intSubDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intSubDivID,<br />OIRA.bitOrganisationalIndividualRiskAssessmentRiskRated, OIRA.intOrganisationalIndividualRiskAssessmentImpact,<br />OIRA.intOrganisationalIndividualRiskAssessmentProbability, OIRA.intOrganisationalIndividualRiskAssessmentRating,<br />(OIRA.intOrganisationalIndividualRiskAssessmentImpact * OIRA.intOrganisationalIndividualRiskAssessmentProbability) AS IP,<br />OIRA.bitOrganisationalIndividualRiskAssessmentCompleted, bitOrganisationalIndividualRiskAssessmentRiskCompleted,<br />ISNULL(CM.intControlMeasureID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intControlMeasureID,<br />CM.intControlMeasureResponsiblePerson, CM.intControlMeasureCheckFrequency, CM.dtmControlMeasureDateImplemented,<br />CM.intControlMeasureControlEffectiveness, CM.ntxControlMeasureDescription, MP.intMonitoringPlanID,<br />MP.intMonitoringPlanResponsiblePerson, MP.intMonitoringPlanCheckFrequency,<br />MP.dtmMonitoringPlanDateStarted, MP.ntxMonitoringPlanDescription,<br />ISNULL(ORM.intOutcomeResultOfMonitoringID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intOutcomeResultOfMonitoringID, <br />ISNULL(ORM.intOutcomeResultOfMonitoringOutcome, 0) AS intOutcomeResultOfMonitoringOutcome, <br />ORM.ntxOutcomeResultOfMonitoringResults, ORM.dtmOutcomeResultOfMonitoringReviewDate, ORM.dtmOutcomeResultOfMonitoringDateMonitored,<br />ISNULL(ORM.bitOutcomeResultOfMonitoringSignedOff, <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS bitOutcomeResultOfMonitoringSignedOff,<br />ISNULL(ORM.intClassificationIssueAndResolutionClassification, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intClassificationIssueAndResolutionClassification,<br />ISNULL(ORM.intClassificationIssueAndResolutionImportance, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intClassificationIssueAndResolutionImportance,<br />ORM.ntxClassificationIssueAndResolutionRemedialActionRequired, ORM.dtmClassificationIssueAndResolutionDueDateOfResolution,<br />ORM.dtmClassificationIssueAndResolutionActualResolutionDate,<br />ISNULL(ORM.intClassificationIssueAndResolutionRMPersonToNotify, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intClassificationIssueAndResolutionRMPersonToNotify,<br />ISNULL(ORM.intClassificationIssueAndResolutionResponsiblePerson, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intClassificationIssueAndResolutionResponsiblePerson,<br />ISNULL(ORM.intClassificationIssueAndResolutionLossIncurred, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS intClassificationIssueAndResolutionLossIncurred,&l
t;br />ORM.mnyClassificationIssueAndResolutionAmount,<br />ISNULL(ORM.bitClassificationIssueAndResolutionSignedOff, <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> AS bitClassificationIssueAndResolutionSignedOff<br />FROM dbo.tblOutcomeResultOfMonitoring ORM<br />RIGHT OUTER JOIN dbo.tblMonitoringPlans MP ON ORM.intMonitoringPlanID = MP.intMonitoringPlanID<br />RIGHT OUTER JOIN dbo.tblControlMeasures CM ON MP.intControlMeasureID = CM.intControlMeasureID<br />RIGHT OUTER JOIN dbo.tblOrganisationalIndividualRiskAssessment OIRA ON CM.intOrganisationalIndividualRiskAssessmentID = OIRA.intOrganisationalIndividualRiskAssessmentID<br />RIGHT OUTER JOIN dbo.tblRiskCategory RC<br />INNER JOIN dbo.tblRiskSubCategory RSC ON RC.intRiskCategoryID = RSC.intRiskCategoryID<br />INNER JOIN dbo.tblOrganisationalRiskSubCategory ORSC ON RSC.intRiskSubCategoryID = ORSC.intRiskSubCategoryID<br />INNER JOIN dbo.tblIndividualRiskAssessment IRA ON RSC.intRiskSubCategoryID = IRA.intRiskSubCategoryID<br />ON OIRA.intIndividualRiskAssessmentID = IRA.intIndividualRiskAssessmentID<br />AND OIRA.intOrganisationalRiskSubCategoryID = ORSC.intOrganisationalRiskSubCategoryID<br />WHERE (RC.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (RSC.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (IRA.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ORSC.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (OIRA.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (CM.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (MP.bitDeleted = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(ORM.bitIsDeleted, <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(ORSC.intBusinessUnitID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(ORSC.intDivisionID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(ORSC.intSubDivisionID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (IRA.bitIndividualRiskAssessmentIsPublished = <cfqueryparam cfsqltype="CF_SQL_BIT" value="1"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (IRA.bitIndividualRiskAssessmentNoRisk = <cfqueryparam cfsqltype="CF_SQL_BIT" value="0"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(OIRA.intBUID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#BUID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(OIRA.intDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#DivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br />AND (ISNULL(OIRA.intSubDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><cfif MPResponsiblePersonFilter GT 0><br />AND (MP.intMonitoringPlanResponsiblePerson = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#MPResponsiblePersonFilter#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /></cfif><br /></cfquery><br /><br />Thanks again.<br />
Edit:<br />Ignore my previous post if you saw it.<br /><br />You should add the OPTION (ROBUST PLAN) last in the query:<br /><br />Change:<br />AND (ISNULL(OIRA.intSubDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><cfif MPResponsiblePersonFilter GT 0><br />AND (MP.intMonitoringPlanResponsiblePerson = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#MPResponsiblePersonFilter#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /></cfif><br /></cfquery><br /><br />To:<br />AND (ISNULL(OIRA.intSubDivID, <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /> = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SubDivID#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /><cfif MPResponsiblePersonFilter GT 0><br />AND (MP.intMonitoringPlanResponsiblePerson = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#MPResponsiblePersonFilter#"/><img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /><br /></cfif><br /> <b>OPTION (ROBUST PLAN)</b><br /></cfquery><br /><br />I don’t know coldfusion syntax but the above should work. If not, you need to contact someone that does and tell them to add the OPTION (ROBUST PLAN) last in the query.<br /><br />/Argyle
Dear Argle, Thank you very much…..youre the MAN! This seems to have resolved the problem.I dont seem to have lost too much preformance either. The ColdFusion code was written by me. If I can ever help you there, it would be my pleasure. SQL is not my strong point. Thanx agian Many Regards Wayne Gibson
(South Africa)
Dear Argle, Thank you very much…..youre the MAN! This seems to have resolved the problem.I dont seem to have lost too much preformance either. The ColdFusion code was written by me. If I can ever help you there, it would be my pleasure. SQL is not my strong point. Thanx agian Many Regards Wayne Gibson
(South Africa)
]]>