Unexpected Query Execution Failure


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 http://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("http://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

image

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

image

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:

 

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

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

 

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.. ?

image

image

 

This means that something is returning a NULL value within the stored procedure.. So let’s take a look at http://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 http://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 😉


Comments (5)
  1. Morten says:

    Wow – I understood nothing of this buddy 🙂

    Still, it's good to see that you are keeping your blog updated with deep technical stuff!

    Legendize!

  2. Robin says:

    Haha thanks Morten, I feel flattered that you have absolutely no idea what I'm talking about 😉

    Legendize!!!

  3. caw says:

    USA,UK,and German friends and others watch it

    http://adf.ly/1qiiX

  4. Maddy says:

    Question:

    How to fix it?

    Can i simply change something in my Code?

  5. Hello! I have been seeing a few of the 282 errors in my log files in the past week. From reading your blog above, I get the impression that they indicate a SharePoint 2007 bug that has not been patched.

    Did I understand you correctly?

    I also get the impression while reading your blog entry that there is nothing obvious that we can do to resolve it.  Did I understand that correctly as well?

    Thank you

Comments are closed.

Skip to main content