Error executing sp (Cannot sort a row of size 9964.....)
-
Hello,
I've the following error, when i'm trying to execute the following procedure (exec SpReportsDrivers), i send you to the code of the procedure.
Hope, you can help me.
Thans and Regards,
Jorge Mendes
Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 9964, which is greater than the allowable maximum of 8094.
exec SpReportsDrivers
--OPTION (ROBUST PLAN)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE spReportsDrivers
@idAuditoria INT = 0,
@idProcesso INT = 0,
@idSubProcesso INT = 0,
@idActividade INT = 0,
@idAuditoriaDriver INT = 0,
@idEmpresa INT = 0,
@idFindingClass INT = 0,
@idAuditoriaDriverFinding INT = 0
AS
DECLARE @strSQL VARCHAR(8000)
SET @strSQL = 'SELECT TOP 100 PERCENT
TblAuditorias.idAuditoria,
TblAuditorias.DescAuditoria,
ISNULL(TblHierarquiaProcessos.idProcesso, 0) AS idProcesso,
ISNULL(TblHierarquiaProcessos.idSubProcesso, 0) AS idSubProcesso,
ISNULL(TblHierarquiaProcessos.idActividade, 0) AS idActividade,
ISNULL(TblProcessos.NrProcesso + '' - '' + TblProcessos.DescProcesso, ''N/D'') AS DescProcesso,
ISNULL(TblSubProcessos.NrSubProcesso + '' - '' + TblSubProcessos.DescSubProcesso, ''N/D'') AS DescSubProcesso,
ISNULL(TblActividades.NrActividade + '' - '' + TblActividades.DescActividade, ''N/D'') AS DescActividade,
TblAuditoriasDrivers.Driver,
ISNULL(TblEmpresas.DescEmpresa, ''...'') AS DescEmpresa,
ISNULL(TblFindingClass.DescFindingClass, ''...'') AS DescFindingClass,
TblAuditoriasDriversFindings2.DescFinding AS DescFinding,
TblAuditoriasDrivers.idAuditoriaDriver,
ISNULL(TblAuditoriasDriversFindings.idAuditoriaDriverFinding, 0) AS idAuditoriaDriverFinding
FROM
TblAuditoriasDriversFindings INNER JOIN
TblEmpresas ON TblAuditoriasDriversFindings.idEmpresa = TblEmpresas.idEmpresa INNER JOIN
TblFindingClass ON TblAuditoriasDriversFindings.idFindingClass = TblFindingClass.idFindingClass INNER JOIN
TblAuditoriasDriversFindings2 ON
TblAuditoriasDriversFindings.idAuditoriaDriverFinding = TblAuditoriasDriversFindings2.idAuditoriaDriverFinding RIGHT OUTER JOIN
TblAuditorias INNER JOIN
TblAuditoriasDrivers ON TblAuditorias.idAuditoria = TblAuditoriasDrivers.idAuditoria ON
TblAuditoriasDriversFindings.idAuditoriaDriver = TblAuditoriasDrivers.idAuditoriaDriver LEFT OUTER JOIN
TblHierarquiaProcessos INNER JOIN
TblProcessos ON TblHierarquiaProcessos.idProcesso = TblProcessos.idProcesso INNER JOIN
TblSubProcessos ON TblHierarquiaProcessos.idSubProcesso = TblSubProcessos.idSubProcesso INNER JOIN
TblAuditoriasDriversHProcessos ON
TblHierarquiaProcessos.idHierarquiaProcesso = TblAuditoriasDriversHProcessos.idHierarquiaProcesso ON
TblAuditoriasDrivers.idAuditoriaDriver = TblAuditoriasDriversHProcessos.idAuditoriaDriver LEFT OUTER JOIN
TblActividades ON TblHierarquiaProcessos.idActividade = TblActividades.idActividade
WHERE
(TblAuditorias.Activo = 1)'
IF @idProcesso <> 0
SET @strSQL = @strSQL + ' AND (TblHierarquiaProcessos.idProcesso = ' + CONVERT(VARCHAR, @idProcesso) + ')'
IF @idSubProcesso <> 0
SET @strSQL = @strSQL + ' AND (TblHierarquiaProcessos.idSubProcesso = ' + CONVERT(VARCHAR, @idSubProcesso) + ')'
IF @idActividade <> 0
SET @strSQL = @strSQL + ' AND (TblHierarquiaProcessos.idActividade = ' + CONVERT(VARCHAR, @idActividade) + ')'
IF @idEmpresa <> 0
SET @strSQL = @strSQL + ' AND (TblAuditoriasDriversFindings.idEmpresa = ' + CONVERT(VARCHAR, @idEmpresa) + ')'
IF @idFindingClass <> 0
SET @strSQL = @strSQL + ' AND (TblAuditoriasDriversFindings.idFindingClass = ' + CONVERT(VARCHAR, @idFindingClass) + ')'
IF @idAuditoriaDriverFinding <> 0
SET @strSQL = @strSQL + ' AND (TblAuditoriasDriversFindings.idAuditoriaDriverFinding = ' + CONVERT(VARCHAR, @idAuditoriaDriverFinding) + ')'
SET @strSQL = @strSQL + ' GROUP BY
TblAuditorias.idAuditoria,
TblHierarquiaProcessos.idProcesso,
TblHierarquiaProcessos.idSubProcesso,
TblHierarquiaProcessos.idActividade,
TblAuditorias.DescAuditoria,
TblProcessos.NrProcesso,
TblProcessos.DescProcesso,
TblSubProcessos.NrSubProcesso,
TblSubProcessos.DescSubProcesso,
TblActividades.NrActividade,
TblActividades.DescActividade,
TblAuditoriasDrivers.Driver,
TblEmpresas.DescEmpresa,
TblFindingClass.DescFindingClass,
TblAuditoriasDriversFindings2.DescFinding,
TblAuditoriasDrivers.idAuditoriaDriver,
TblAuditoriasDriversFindings.idAuditoriaDriverFinding
HAVING
(TblAuditorias.idAuditoria > 0)'
IF @idAuditoria <> 0
SET @strSQL = @strSQL + ' AND (TblAuditorias.idAuditoria = ' + CONVERT(VARCHAR, @idAuditoria) + ')'
IF @idAuditoriaDriver <> 0
SET @strSQL = @strSQL + ' AND (TblAuditoriasDrivers.idAuditoriaDriver = ' + CONVERT(VARCHAR, @idAuditoriaDriver) + ')'
SET @strSQL = @strSQL + ' ORDER BY
TblAuditorias.idAuditoria,
TblProcessos.NrProcesso,
TblSubProcessos.NrSubProcesso,
TblActividades.NrActividade,
TblAuditoriasDrivers.Driver,
TblAuditoriasDriversFindings2.DescFinding'
EXEC(@strSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
(UserID=147850)
Thanks a lot.
Regards,
Jorge Manuel Mendes -
The columns/values you are selecting have exceeded the maximum row size (8094 or 8KB or 1 page) allowed. Try checking the source columns. You may trim the CHAR columns.
-
Hi Kidest,
Thanks for your help, but how can i change this parameter?
Its possible to change it without affect the database(its possible to do it with the database online?)
Thanks,
Regards,
Jorge Mendes
Thanks a lot.
Regards,
Jorge Manuel Mendes -
Hi Jorge,
The issue isn't with your SP, its with the underlying table. One (or more) of the tables used by the SP has a rowlength greater than 8k. This means that one row is on more than one page. *You need to get the rowsize under 8K to get rif of this error*
Your options here are to
a) Re-evaluate the column lengths of the columns in the offending table, and make them shorter where this is an option.(I'd start with the likely culprets of CHAR then VARCHAR first..)
b) Copy the columns you are interested in into a table variable / temptable and fo the sorting from there - but that would be messy - start with option a) first.
c) If after re-evaluating all columns above, they are all valid sizes, then you'll need to look Vertical Partitioning. Look in BOL or do some googling for this. May be painful depending on how many SP's etc it may effect.
- I suspect that you will need to talk to your DBA about this, and maybe ask them why the design has been allowed like this in the first place
Hope that helps
Andy
-
Ronald was correct - your SELECT statement is too big. You are joining 12 or 13 tables (I lost count!), and the total size of all columns selected is too large. You will have to reduce the size of some of the varchar/char columns using convert or cast, or eliminate some columns from the SELECT.
Add up the sizes of each of the selected columns.
-
Thanks a lot everybody, you we're very usefull.
Regards,
Jorge Mendes
Thanks a lot.
Regards,
Jorge Manuel Mendes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply