June 27, 2008 at 10:04 am
Hi All,
Hoping you can help with this one as I'm stumped.
I have a 3rd partry db that contains amongst other things users task lists. One of the users in question was operating fine until he came back from lunch after which his task view started giving him a SQL error.
The code generated by the 3rd party app to get the task list is as follows (I apologise for the formatting):
select TOP 41 * from vTaskListCommunication
WHERE ((lead_secterr is null))
AND (((( CmLi_Comm_UserID=32)
AND ((COALESCE(Comm_TargetListID, 0) = 0)
or (cmli_Comm_CompanyID =-1))
AND ((COALESCE(Comm_Private, N'') = N'')
OR ((COALESCE(Comm_Private, N'') <> N'')
AND (CmLi_Comm_UserId = 32))))
AND ((COALESCE(Comm_DateTime, '18991230 00:00:00') = '18991230 00:00:00'
OR (Comm_DateTime >='20080627 00:00:00'
AND Comm_DateTime <'20080628 00:00:00'))))
AND ( COALESCE(Comm_Type, N'') <> N'Appointment'))
ORDER BY comm_datetime, Comm_Communicationid
The error that is then returned is this:
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8102 which is greater than the allowable maximum of 8094.
Now after a bit of testing, I have determined that adding any 'ORDER BY' clause causes this error to be returned - if I remove the ORDER BY line, everything works fine with the expected 28 rows returned... so the question is, how can an ORDER BY clause affect the length of a row??
Many thanks in advance
Brett
June 27, 2008 at 10:14 am
Is this query passed as dynamic sql?
June 27, 2008 at 10:16 am
Probably on the definition of either one of the tables in the view or the view itself you have exceeded the maximum row-length. The Addiditon of "order by" is creating "probably" a table spool (or temporary table) that does not complies with the maximum row-length of an sqlserver table.
Can you verify that ?
* Noel
June 27, 2008 at 10:17 am
steveb (6/27/2008)
Is this query passed as dynamic sql?
Not as far as I know - any way I can be certain? (Sorry, amateur here)
June 27, 2008 at 10:18 am
noeld (6/27/2008)
Probably on the definition of either one of the tables in the view or the view itself you have exceeded the maximum row-length. The Addiditon of "order by" is creating "probably" a table spool (or temporary table) that does not complies with the maximum row-length of an sqlserver table.Can you verify that ?
Sorry again, some advice on how to verify that would be very helpful here.
Thanks,
June 27, 2008 at 10:19 am
I didnt mention is specifically in my first post, but all 150+ other users are working fine - it is only this one user being affected. Dont know if that helps any :crazy:
June 27, 2008 at 10:22 am
brett.davis1 (6/27/2008)
noeld (6/27/2008)
Probably on the definition of either one of the tables in the view or the view itself you have exceeded the maximum row-length. The Addiditon of "order by" is creating "probably" a table spool (or temporary table) that does not complies with the maximum row-length of an sqlserver table.Can you verify that ?
Sorry again, some advice on how to verify that would be very helpful here.
Thanks,
Can you post the definition of the view ?
* Noel
June 27, 2008 at 10:28 am
View definition as requested:
SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName
, Capt_Order AS Comm_PrioritySorted
, RTRIM(ISNULL(Pers_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber
, RTRIM(ISNULL(Comp_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(Comp_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Comp_PhoneNumber, '')) AS Comp_PhoneFullNumber
, Pers_PersonId
, Pers_CreatedBy
, Pers_SecTerr
, Company.*
, Communication.*
, Lead.*
, CmLi_Comm_UserID
, CmLi_Comm_PersonID
, CmLi_Comm_CompanyID
, CmLi_CommLinkId
, CmLi_Comm_NotifyTime
, CmLi_Comm_WaveResponse
, CmLi_Comm_InitialWave
, Cmli_Comm_LeadID
, Pers_PrimaryUserId
, Pers_ChannelId
FROM Communication LEFT OUTER JOIN Comm_Link ON Comm_CommunicationId = CmLi_Comm_CommunicationId LEFT OUTER JOIN
Person ON Pers_PersonId = CmLi_Comm_PersonId LEFT OUTER JOIN
Company ON Comp_CompanyId = CmLi_Comm_CompanyId LEFT OUTER JOIN
Lead ON Lead_LeadId = Comm_LeadId LEFT OUTER JOIN
Custom_Captions ON UPPER(Capt_Code) = UPPER(Comm_Priority) AND UPPER(Capt_Family) = N'COMM_PRIORITY'
WHERE Comm_Deleted IS NULL
AND CmLi_Deleted IS NULL
Thanks again.
Brett
June 27, 2008 at 10:39 am
brett.davis1 (6/27/2008)
View definition as requested:SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName
, Capt_Order AS Comm_PrioritySorted
, RTRIM(ISNULL(Pers_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Pers_PhoneNumber, '')) AS Pers_PhoneFullNumber
, RTRIM(ISNULL(Comp_PhoneCountryCode, '')) + ' ' + RTRIM(ISNULL(Comp_PhoneAreaCode, '')) + ' ' + RTRIM(ISNULL(Comp_PhoneNumber, '')) AS Comp_PhoneFullNumber
, Pers_PersonId
, Pers_CreatedBy
, Pers_SecTerr
, Company.*
, Communication.*
, Lead.*
, CmLi_Comm_UserID
, CmLi_Comm_PersonID
, CmLi_Comm_CompanyID
, CmLi_CommLinkId
, CmLi_Comm_NotifyTime
, CmLi_Comm_WaveResponse
, CmLi_Comm_InitialWave
, Cmli_Comm_LeadID
, Pers_PrimaryUserId
, Pers_ChannelId
FROM Communication LEFT OUTER JOIN Comm_Link ON Comm_CommunicationId = CmLi_Comm_CommunicationId LEFT OUTER JOIN
Person ON Pers_PersonId = CmLi_Comm_PersonId LEFT OUTER JOIN
Company ON Comp_CompanyId = CmLi_Comm_CompanyId LEFT OUTER JOIN
Lead ON Lead_LeadId = Comm_LeadId LEFT OUTER JOIN
Custom_Captions ON UPPER(Capt_Code) = UPPER(Comm_Priority) AND UPPER(Capt_Family) = N'COMM_PRIORITY'
WHERE Comm_Deleted IS NULL
AND CmLi_Deleted IS NULL
Thanks again.
Brett
Because you have so many " .*" in the definition you are adding more and more columns and eventually you will reach the limit of the row size.
On SQL Server Maximum Capacity Specifications you will find:
"Bytes per row" ------> 8,060
For a particular user some of those coulms could have been filled at a point that such number was surpassed.
To be sure you will probably need to post the DDL of the tables mainly those where you have specified
" .*" on the view definition.
* Noel
June 27, 2008 at 5:03 pm
Hi are you sure its an issue with order by clause? why dont you remove order by and try the query. If you are getting the same error then its an issue with the columns that you have mentioned in you SELECT clause. As other forums members are saying there is a limit on the size of the row.
Thanks -- Vj
June 27, 2008 at 5:30 pm
I'm gonna go with Noel on this one...
You will prob find that this user has more data in some of the cols being returned which is going to exceed the max allow for a row, which your view is trying to return...
DDL and data will be very usefull .
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 28, 2008 at 2:50 am
Vijaya Krishna (6/27/2008)
Hi are you sure its an issue with order by clause? why dont you remove order by and try the query.
Hi,
I have done this - the issue is absolutely with the ORDR BY - if I remove it everything works perfectly.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply