1

The SharePoint 2010 server is returning some error message on Windows Event Viewer and SharePoint logs.

Windows Event Viewer is returning the following error message:

Event 5586, SharePoint Foundation

Unknown SQL Exception 191 occurred. Additional error information from SQL Server is included below.

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

On the SharePoint logs I see the following:

There is a sql query that it executes that is the one causing the issue. It looks like it works with workflow tasks list. There is a list that uses the Approval Workflow for some items, every time one of these tasks is processed the error shows up on the event viewer and SharePoint log files. I took a look at older log files, but the error did not start until about a week ago. No changes have been made to the system for over 2 months.

SqlCommand: 'DECLARE @DocParentIdForRF uniqueidentifier 

SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;        

SELECT t11.*, t12.[ntext3], t12.[ntext8], t12.[ntext7], t12.[ntext4], t12.[ntext5], t12.[ntext1], t13.[MetaInfo] AS c3, t12.[ntext2], t12.[ntext6] FROM (

SELECT DISTINCT t8.*, t2.[tp_ID] AS c34, t10.[nvarchar1] AS c34c35, t9.[tp_Ordinal] FROM (

SELECT TOP(@NUMROWS) t1.[Type] AS c0, UserData.[datetime1], UserData.[nvarchar10], t4.[tp_ID] AS c37c13, UserData.[float2], UserData.[tp_ItemOrder], UserData.[nvarchar1], t3.[tp_ID] AS c36c13, UserData.[bit2], UserData.[nvarchar23], t1.[ProgId] AS c31, UserData.[tp_ParentId], UserData.[tp_DocId], UserData.[float1], UserData.[nvarchar19], UserData.[bit1], UserData.[tp_GUID], UserData.[nvarchar22], t7.[nvarchar1] AS c42c12, t1.[LeafName] AS c6, t1.[SortBehavior] AS c1, t6.[nvarchar1] AS c41c12, UserData.[int1], UserData.[nvarchar13], UserData.[datetime4], UserData.[nvarchar18], t1.[TimeLastModified] AS c29, UserData.[tp_ContentTypeId], t3.[nvarchar4] AS c36c14, t4.[nvarchar4] AS c37c14, UserData.[nvarchar21], UserData.[nvarchar26], t7.[nvarchar6] AS c42c15, UserData.[tp_WorkflowVersion], t1.[ItemChildCount] AS c32, UserData.[tp_Version], t1.[Id] AS c4, UserData.[tp_IsCurrentVersion], UserData.[nvarchar4], UserData.[nvarchar9], t5.[datetime3] AS c40, t6.[nvarchar6] AS c41c15, Nvp.ItemId, UserData.[tp_HasCopyDestinations], UserData.[tp_Level], UserData.[tp_SiteId], UserData.[tp_CalculatedVersion], UserData.[datetime3], UserData.[int5], UserData.[nvarchar12], UserData.[nvarchar17], Nvp.Value, t4.[nvarchar1] AS c37c12, UserData.[datetime8], UserData.[nvarchar20], UserData.[tp_HasAttachment], UserData.[tp_Modified], CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N'/' + t1.LeafName END  AS c2, UserData.[nvarchar3], t3.[nvarchar1] AS c36c12, UserData.[nvarchar8], UserData.[nvarchar25], t6.[tp_ID] AS c41c13, t7.[tp_ID] AS c42c13, UserData.[tp_UIVersion], t1.[TimeCreated] AS c7, t1.[ClientId] AS c30, UserData.[tp_CopySource], UserData.[datetime2], UserData.[nvarchar11], UserData.[uniqueidentifier1], UserData.[nvarchar16], UserData.[tp_InstanceID], t3.[nvarchar6] AS c36c15, t4.[nvarchar6] AS c37c15, UserData.[int4], UserData.[datetime7], t5.[datetime1] AS c38, UserData.[tp_UIVersionString], t1.[ScopeId] AS c5, UserData.[nvarchar2], UserData.[nvarchar7], UserData.[bit3], UserData.[nvarchar24], UserData.[nvarchar29], t6.[tp_Created] AS c41c16, t7.[tp_Created] AS c42c16, t1.[FolderChildCount] AS c33, UserData.[nvarchar15], UserData.[int3], UserData.[datetime6], UserData.[tp_Created], UserData.[tp_ModerationStatus], UserData.[nvarchar6], UserData.[tp_WorkflowInstanceID], UserData.[nvarchar28], t7.[nvarchar4] AS c42c14, t1.[DirName] AS c8, UserData.[nvarchar14], t6.[nvarchar4] AS c41c14, UserData.[tp_DeleteTransactionId], UserData.[int2], t4.[tp_Created] AS c37c16, UserData.[datetime5], t5.[datetime2] AS c39, UserData.[tp_ID], t3.[tp_Created] AS c36c16, UserData.[nvarchar5], UserData.[nvarchar27], UserData.[nvarchar30], UserData.[tp_Author], UserData.[tp_Editor] FROM NameValuePair AS NVP WITH(INDEX=NameValuePair_CI) INNER LOOP JOIN 

...

OR ((Nvp.Value = CAST(@L160TXP AS uniqueidentifier)) 

OR ((Nvp.Value = CAST(@L161TXP AS uniqueidentifier)) 

OR (Nvp.Value = CAST(@L162TXP AS uniqueidentifier)) 

...

Parameter: '@L160TXP' Type: NVarChar Size: 4000 Direction: Input Value: '[guid]'

Parameter: '@L161TXP' Type: NVarChar Size: 4000 Direction: Input Value: '[guid]'

Parameter: '@L162TXP' Type: NVarChar Size: 4000 Direction: Input Value: '[guid]'

Parameter: '@FDN' Type: NVarChar Size: 4000 Direction: Input Value: '[SitePath]'

Parameter: '@FLN' Type: NVarChar Size: 4000 Direction: Input Value: 'WorkflowTasks'

Parameter: '@NUMROWS' Type: BigInt Size: 0 Direction: Input Value: '5001'

Parameter: '@L166' Type: UniqueIdentifier Size: 0 Direction: Input Value: '[guid]'

Parameter: '@RequestGuid' Type: UniqueIdentifier Size: 0 Direction: Input Value: '[guid]'

I see that it is referencing a table called NameValuePair and the query uses 162 different variable to compare in the 'OR' statements. What is this query doing and how can this issue be resolved?

Any help will be appreciated.

Thanks,

2
  • what is error, you are seeing at sql server?
    – Waqas Sarwar MVP
    Apr 27, 2016 at 19:47
  • The error statement is the same as the Windows Event Viewer error message. The log also shows the query that is causing this issue. I've made a change to the question to include the sql statement in question.
    – casp
    Apr 28, 2016 at 20:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.