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,