Unexpected Query Execution Failure

Article
06/10/2011

If you use your favorite search engine to search on the following error

Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'33B57EA5-A6C2-43EF-9647-7273C62A485E',?,NULL,1,?,?,6187)}"

you most likely find blog/forum posts that are about SSRS integration. However, there is also another thing that can cause this issue to be reported in the ULS. Before we cover that, let’s take a look at the stored procedure. If you take a look at the following article https://msdn.microsoft.com/en-us/library/dd302800(PROT.13).aspx that describes what the Stored Procedure proc_GetTpWebMetaDataAndListMetaData is all about. The thing that we are interested in, is the return value, it states “The proc_GetTpWebMetaDataAndListMetaData stored procedure returns an integer return code ,which MUST be in the following table” , now in our case, for some unknown reason a NULL value is returned instead of a integer. That’s why SQL is complaining and thus we are getting the error in the ULS.

So, how do we get the error in the ULS? If you take a look at the following piece of code:

SPSite site = new SPSite("https://test");

SPWeb web = site.OpenWeb();

SPListItem firstItem = web.GetListItem("/Shared Documents/test.txt");

SPListItem secondItem = web.GetListItem("/Shared Documents/test.txt");

We get the following error in the ULS:

0x15D0 Windows SharePoint Services Database 6f8g Unexpected Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'33B57EA5-A6C2-43EF-9647-7273C62A485E',?,NULL,1,?,?,6187)}"

Let’s see what’s happening on the SQL Server side by running a profile trace..

exec proc_GetTpWebMetaDataAndListMetaData 'AB835D50-E8E0-43EA-9DA9-98E63240D661',NULL,N'Shared Documents/test.txt',NULL,1,-1,0x0105000000000005150000000965EBB84367A5FC5C4CD895F4010000,59

exec proc_GetListFields '33B57EA5-A6C2-43EF-9647-7273C62A485E','56FE5542-ED10-4D4B-B248-9009525BB736'

exec proc_GetTpWebMetaDataAndListMetaData 'AB835D50-E8E0-43EA-9DA9-98E63240D661','33B57EA5-A6C2-43EF-9647-7273C62A485E',N'Shared Documents/test.txt',NULL,1,0,0x0105000000000005150000000965EBB84367A5FC5C4CD895F4010000,6187

Now, unfortunately I’m not part of the product team so explaining why by calling the same method twice results in two different stored procedure calls is quite difficult. My only guess is that the first procedure set’s everything in place for any other calls that are made in the context we have.
If I run the stored procedures (which you should never do!) this is difference between the two:

exec proc_GetTpWebMetaDataAndListMetaData 'AB835D50-E8E0-43EA-9DA9-98E63240D661',NULL,N'Shared Documents/test.txt',NULL,1,-1,0x0105000000000005150000000965EBB84367A5FC5C4CD895F4010000,59

Quite a different result right? So what happens if we modify the source code to get the context instead of creating our own..

SPWeb web = SPContext.Current.Web;

SPListItem firstItem = web.GetListItem("/Shared Documents/test.txt");

SPListItem secondItem = web.GetListItem("/Shared Documents/test.txt");

When running the profiler again, we get this stored procedures that are called:

Makes sense right? Since we already got the context, there is no need to set it, so far the theory is correct. But to my surprise now I got two errors in the ULS log..

06/10/2011 11:41:21.13 w3wp.exe (0x1C8C) 0x1BFC Windows SharePoint Services Database 6f8g Unexpected Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'33B57EA5-A6C2-43EF-9647-7273C62A485E',?,NULL,1,?,?,6187)}"
06/10/2011 11:41:21.13 w3wp.exe (0x1C8C) 0x1BFC Windows SharePoint Services Database 6f8g Unexpected Unexpected query execution failure, error code 282. Additional error information from SQL Server is included below. "The 'proc_GetTpWebMetaDataAndListMetaData' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead." Query text (if available): "{?=call proc_GetTpWebMetaDataAndListMetaData(?,'33B57EA5-A6C2-43EF-9647-7273C62A485E',?,NULL,1,?,?,6187)}"

In a way, this also makes sense, since the same stored procedure is executed twice.. so the question is, what is wrong with the values that are passed on by the stored procedure once the context has been set that SQL throws this error?
What does SQL itself say when we run the stored procedure.. ?

This means that something is returning a NULL value within the stored procedure.. So let’s take a look at https://msdn.microsoft.com/en-us/library/dd302800(v=PROT.13).aspx that explains what all the different result sets can be.. By looking at what kind of results we got from SQL, we can identify the result set that is being returned is the List Identifier one (see https://msdn.microsoft.com/en-us/library/dd303701(v=PROT.13).aspx) . To translate, the first column is the GUID of the List, the second column is the Type and the third one is the Row Identifier of the ListItem. By knowing this, we can look into the stored procedure and find out what section of the procedure is causing the NULL value to be returned.
The piece of SQL below is the one responsible of returning the result set, if you look closely you will notice the following “RETURN @ret” return statement… in my test case I took this piece of SQL and used the values that were passed on to the stored procedure like showed above and guess what.. the @ret value was NULL .. so we found the cause! :)

DECLARE @ret int

DECLARE @WebUrl nvarchar(256)

DECLARE @WebDirName nvarchar(256)

DECLARE @WebLeafName nvarchar(128)

DECLARE @UserId int

DECLARE @UrlDirName nvarchar(256)

DECLARE @UrlLeafName nvarchar(128)

DECLARE @WebIdSelected uniqueidentifier

DECLARE @ListIdSelected uniqueidentifier

DECLARE @ItemLevelSelected tinyint

DECLARE @UrlIsListItem bit

DECLARE @RootWebId uniqueidentifier

DECLARE @UserInfoListId uniqueidentifier

DECLARE @TypeSelected int

DECLARE @ItemIdSelected int

DECLARE @WebSiteId uniqueidentifier

DECLARE @WebId uniqueidentifier

DECLARE @Url nvarchar(260)

DECLARE @ListId uniqueidentifier

DECLARE @RunUrlToWebUrl bit

DECLARE @DGCacheVersion bigint

DECLARE @SystemId tSystemId

DECLARE @MetadataFlags int

SET @UserId = 0

SET @UrlIsListItem = 0

SET @RootWebId = NULL

SET @WebSiteId = 'AB835D50-E8E0-43EA-9DA9-98E63240D661'

SET @WebId = '33B57EA5-A6C2-43EF-9647-7273C62A485E'

SET @Url = N'Shared Documents/test.txt'

SET @ListId = NULL

SET @RunUrlToWebUrl = 1

SET @DGCacheVersion = 0

SET @SystemId = 0x0105000000000005150000000965EBB84367A5FC5C4CD895F4010000

SET @MetadataFlags = 6187

EXEC proc_SplitUrl @Url, @UrlDirName OUTPUT, @UrlLeafName OUTPUT

IF @UserId = 0

BEGIN

SELECT

@UserId = UserInfo.tp_Id

FROM

UserInfo WITH(NOLOCK)

WHERE

UserInfo.tp_SiteId = @WebSiteId AND

UserInfo.tp_SystemId = @SystemId

END

SELECT TOP 1

@ListIdSelected = ListId,

@TypeSelected = Type,

@WebIdSelected = WebId,

@ItemIdSelected = DocLibRowId,

@ItemLevelSelected = Level

FROM

Docs WITH(NOLOCK)

WHERE

SiteId = @WebSiteId AND

DirName = @UrlDirName AND

LeafName = @UrlLeafName AND

(Level = 255 AND LTCheckoutUserId = @UserId OR

Level <> 255 AND (LTCheckoutUserId IS NULL OR LTCheckoutUserId <> @UserId))

ORDER BY

Level DESC

IF @WebIdSelected <> @WebId

BEGIN

SET @ret = 1

RETURN @ret

END

SELECT @ListIdSelected, @TypeSelected, @ItemIdSelected

IF (@ListIdSelected IS NOT NULL AND @ItemIdSelected IS NOT NULL)

SET @UrlIsListItem = 1

So there you go, if you come across this error in your ULS then you now know what the problem might also be.. Btw, this is fixed in SP2010 where the @ret value is by default set 0 ;)

Unexpected Query Execution Failure

Additional resources