SQL Server Performance Forum – Threads Archive
EXCEEDING MAX ROWS
I have experienced this problem since additional data has been added to the database. Im not sure how the code should look for a ROBUST PLAN HINT?..Is this the only solution that I have? I have included the code which had worked up and till now. Im using ColdFusion pages which have this query as an include. Error Occurred While Processing RequestError Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot sort a row of size 8709, which is greater than the allowable maximum of 8094.
SQL = "SELECT TOP 100 PERCENT RC.intRiskCategoryID, RC.nvrRiskCategoryName, RC.ntxRiskCategoryDescription, RC.bitHasDescription, RC.bitHasSubCategory, RC.bitMethodOptionUsed, 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.bitIndividualRiskAssessmentRiskCompleted, IRA.bitIndividualRiskAssessmentIsPublished FROM dbo.tblRiskCategory RC INNER JOIN dbo.tblRiskSubCategory RSC ON RC.intRiskCategoryID = RSC.intRiskCategoryID INNER JOIN dbo.tblIndividualRiskAssessment IRA ON RSC.intRiskSubCategoryID = IRA.intRiskSubCategoryID WHERE (RC.bitDeleted = ?) AND (RSC.bitDeleted = ?) AND (IRA.bitDeleted = ?) AND (IRA.bitIndividualRiskAssessmentIsPublished = ?) ORDER BY RC.nvrRiskCategoryName, RSC.nvrRiskSubCategoryName, IRA.intIndividualRiskAssessmentNumber, IRA.chrIndividualRiskAssessmentAlpha" Query Parameter Value(s) – Parameter #1 = 0 Parameter #2 = 0 Parameter #3 = 0 Parameter #4 = 1 Data Source = "AXIS"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (2:49) in the template file D:INETPUBWWWROOTAXISINCLUDESDRAPATCH01.CFM.
This could be caused by a GROUP BY or ORDER BY clause in an SQL statemen as its too big to sort. You could either try and limit the size of this column, remove it from the ORDER BY and/or GROUP BY clause, or possibly do something like: ….ORDER BY LEFT(ColumnName, 100) HTH Satya SKJ
Thank you very much. You have been a great help. I have removed the ORDER BY clause. This solves the problem. However I do need to sort this information. I have used the nvr, int, chr to indicate what the row represents(Nvarchar, Integer, character. Do I used
ORDER BY LEFT(nvrRiskCategoryName, 100) what about the others after that? Is it
ORDER BY LEFT(nvrRiskCategoryName, 100),(nvrRiskSubCategoryName, 100) etc. or just
ORDER BY LEFT(RC.nvrRiskCategoryName, 100) Thank again for your help here. Wayne Gibson
]]>