What does this error message mean ? - SharePoint Management - SharePoint for All - Dell Community

What does this error message mean ?

What does this error message mean ?

This question has been answered by grigory vasiliev

Internal error. See log file <a href='/logs/WebReports.log' title='C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\Logs\WebReports.log'>WebReports.log</a>

Trying to check for all authenticated users for each site colleftion on my farm and get this error on some site collections ?

Any ideas ?

Verified Answer
  • this scalar function is a part of SharePoint schema

    it convert string to form which can be used in SQL statements (by escape special symbols)

    it does not modify any table

    it even does not read anything from tables

    here is its body:

    SE [WSS_Content]

    GO

    /****** Object:  UserDefinedFunction [dbo].[fn_EscapeForLike]    Script Date: 04/08/2013 06:25:11 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fn_EscapeForLike](

        @Source nvarchar(260),

        @AddTerminalWildcard bit = 1)

    RETURNS nvarchar(1024)

    WITH SCHEMABINDING

    BEGIN

        RETURN

        CASE

            WHEN @Source IS NULL THEN NULL

            WHEN @AddTerminalWildcard = 1 AND  DATALENGTH(@Source) = 0 THEN

                N'_%'

            WHEN @AddTerminalWildcard = 1 THEN

                REPLACE(

                    REPLACE(

                        REPLACE(

                            @Source COLLATE Latin1_General_BIN,

                            N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]') + N'/%'

            ELSE

                REPLACE(

                    REPLACE(

                        REPLACE(

                            @Source COLLATE Latin1_General_BIN,

                            N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]')

        END

    END

All Replies
  • Site Admin cannot prepare report and error details are in log files.

    I recommend to search for 'error ' (error word + space) in SpBrowserAgent.log and SpBrowserBroker.log files. There should be helpfull error message.

    Additional info about troubleshooting is here: http://sahelp.sharepointforall.com/FAQ/ladv_troubleshooting.html

  • Grigory,

    Thanks for the reply.

    I believe i have tracked down the error (SQL ERROR) but can't breakdown what the problem is as i am not overly familiar with SQL. I have attached the error in the hope you can help. I have this error on more than one site collection, but not all site collections. The site collection that works ironically is my largest site collection (275GB in size).

    2013-04-08 09:27:39,796 [6132:4836] ** ERROR ** SQL error: nwowmossvdb\nwow_moss hr_content /*#######################################################################################
    #Copyright (C) 2012 Quest Software, Inc.
    #File:          UniquePerms.sql
    #Version:       5.0.0.0

    #######################################################################################
    #
    #       THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
    #       EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
    #       WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
    #
    ########################################################################################*/
    declare @siteid nvarchar(40), @hiddenType int
    set @siteId = '971135DA-EA12-4E09-AF08-13E434D4665A'
    set @hiddenType = 0x100

    -- This file is for preparing urlLike for SubSitesOnly.sql

    declare @exactUrl nvarchar(256), @withSubSites bit, @urlLike nvarchar(256)
    set @withSubSites = 1         -- specify whether sub-sites should be returned or not
    set @exactUrl = N'hr'               -- site path (example: sites/somesitecoll/somesite)
    set @urlLike = dbo.fn_EscapeForLike( @exactUrl, @withSubSites )

    ;with filtered_lists as
    (
            -- return lists that filter by hidden and deleted
        -- input parameters:
        -- declare @hiddenType int
        -- set @hiddenType = 0x100   

        select *
        from AllLists
        where AllLists.tp_Flags & @hiddenType = 0
    ),
    BaseFilteredDocs as
    (
           
        -- return current versions of not deleted docs
       
        select *
        from
            AllDocs
        where
            AllDocs.DeleteTransactionId = 0   
            and AllDocs.IsCurrentVersion = 1
    ),
    filtered_docs as
    (
            -- return docs that are list items
        -- it depends from BaseFilteredDocs.sql.
       
        select *
        from
            BaseFilteredDocs as d   
        where
            d.DoclibRowId is not null
    ),
    t_perms as
    (
       
        -- Return ScopeId, ScopeUrl, WebId in table Perms with siteId
        -- input parameters:
        -- declare @siteid nvarchar(40)
        -- set @siteId = '971135DA-EA12-4E09-AF08-13E434D4665A'          -- ID of site collection (GUID)

        select ScopeId, ScopeUrl, WebId
        from Perms
        where SiteId = @siteId
            and DelTransId = 0x
    ),
    t_webs as
    (
        -- Return ids of site and its sub-sites
    -- it depends from urlLikeForSubSitesOnly.sql
    -- input parameters:
    -- declare @exactUrl nvarchar(256), @siteid nvarchar(40), @withSubSites bit, @urlLike nvarchar(256)
    -- set @siteId = '971135DA-EA12-4E09-AF08-13E434D4665A'          -- ID of site collection (GUID)
    -- set @exactUrl = N'hr'     -- site path (example: sites/somesitecoll/somesite)
    -- set @withSubSites = 1               -- specify whether sub-sites should be returned or not
    -- set @urlLike = dbo.fn_EscapeForLike( @exactUrl, @withSubSites )
    select id, ScopeId, FirstUniqueAncestorWebId, FullUrl
    from Webs
    where
        Webs.SiteId = @siteid
        and ( Webs.FullUrl = @exactUrl
              or Webs.FullUrl like     @urlLike
              or (@withSubSites = 1 and DATALENGTH(@exactUrl) = 0 and Webs.FullUrl like N'%' )
            )
    ),
    t_lists as
    (
       
        -- Return tp_ID, tp_WebId, tp_scopeId in table AllLists,
        -- ScopeId in table t_webs of site and its sub-sites   

        select filtered_lists.tp_ID, filtered_lists.tp_WebId, filtered_lists.tp_scopeid, t_webs.ScopeId
        from filtered_lists
        inner join t_webs
            on filtered_lists.tp_WebId = t_webs.id
        where filtered_lists.tp_DeleteTransactionId = 0x
    ),
    t_docs as
    (
       
        -- Return id and (DirName + '/' + LeafName) Url in table AllDocs
        -- It depends on the t_lists of site and its sub sites

        select Id, DirName + '/' + LeafName Url
        from filtered_docs
        inner join t_lists
            on filtered_docs.listid = t_lists.tp_ID
    )


    select CountType, Number
    from
    (
        select 'sites' CountType, COUNT(id) Number
        from t_webs
        where t_webs.FirstUniqueAncestorWebId = t_webs.Id and t_webs.FullUrl <> @exactUrl

        UNION

        select 'lists' CountType, COUNT(ls.tp_ID) Number
        from t_lists ls
        where ls.tp_scopeid <> ls.ScopeId

        UNION

        select 'items' CountType, COUNT(d.Id) Number
        from t_docs as d
        inner join t_perms p on d.Url = p.ScopeUrl
    ) t_number
    OPTION (MERGE JOIN)
    Traceback (most recent call last):
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\SharePoint Browser\IPAgent\SqlHelper.py", line 39, in ExecQueryWOExtWithretValueIfNone
        data = self.ExecQuery(sql, dbname, query, iso_level )
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\SharePoint Browser\IPAgent\SqlHelper.py", line 27, in ExecQuery
        return SqlHelperBase.sql_exec(sql, db, query)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\common\SqlHelperBase.py", line 36, in sql_exec
        return conn.execute(query)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\Python25\lib\site-packages\sqlalchemy\engine\base.py", line 824, in execute
        return Connection.executorsCoffee(self, object, multiparams, params)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\Python25\lib\site-packages\sqlalchemy\engine\base.py", line 888, in _execute_text
        return self.__execute_context(context)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\Python25\lib\site-packages\sqlalchemy\engine\base.py", line 896, in __execute_context
        self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\Python25\lib\site-packages\sqlalchemy\engine\base.py", line 950, in _cursor_execute
        self._handle_dbapi_exception(e, statement, parameters, cursor, context)
      File "C:\Program Files (x86)\Quest Software\Site Administrator for SharePoint\SharePoint Information Portal\Python25\lib\site-packages\sqlalchemy\engine\base.py", line 931, in _handle_dbapi_exception
        raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
    DBAPIError: (Error) ('HY007', '[HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared (0) (SQLNumResultCols)') u'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED /*#######################################################################################\n#Copyright (C) 2012 Quest Software, Inc.\n#File:          UniquePerms.sql\n#Version:       5.0.0.0\n\n#######################################################################################\n#\n#       THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,\n#       EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED\n#       WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.\n#\n########################################################################################*/\ndeclare @siteid nvarchar(40), @hiddenType int\nset @siteId = \'971135DA-EA12-4E09-AF08-13E434D4665A\'\nset @hiddenType = 0x100\n\n-- This file is for preparing urlLike for SubSitesOnly.sql\n\ndeclare @exactUrl nvarchar(256), @withSubSites bit, @urlLike nvarchar(256)\nset @withSubSites = 1         -- specify whether sub-sites should be returned or not\nset @exactUrl = N\'hr\'               -- site path (example: sites/somesitecoll/somesite)\nset @urlLike = dbo.fn_EscapeForLike( @exactUrl, @withSubSites ) \n\n;with filtered_lists as\n(\n        -- return lists that filter by hidden and deleted\n    -- input parameters:\n    -- declare @hiddenType int\n    -- set @hiddenType = 0x100    \n\n    select *\n    from AllLists \n    where AllLists.tp_Flags & @hiddenType = 0\n),\nBaseFilteredDocs as \n(\n        \n    -- return current versions of not deleted docs\n    \n    select *\n    from \n        AllDocs\n    where \n        AllDocs.DeleteTransactionId = 0    \n        and AllDocs.IsCurrentVersion = 1\n),\nfiltered_docs as\n(\n        -- return docs that are list items\n    -- it depends from BaseFilteredDocs.sql.\n    \n    select *\n    from \n        BaseFilteredDocs as d    \n    where \n        d.DoclibRowId is not null\n),\nt_perms as\n(\n    \n    -- Return ScopeId, ScopeUrl, WebId in table Perms with siteId\n    -- input parameters:\n    -- declare @siteid nvarchar(40)\n    -- set @siteId = \'971135DA-EA12-4E09-AF08-13E434D4665A\'          -- ID of site collection (GUID)\n\n    select ScopeId, ScopeUrl, WebId\n    from Perms \n    where SiteId = @siteId\n        and DelTransId = 0x\n),\nt_webs as\n(\n    -- Return ids of site and its sub-sites\n-- it depends from urlLikeForSubSitesOnly.sql\n-- input parameters:\n-- declare @exactUrl nvarchar(256), @siteid nvarchar(40), @withSubSites bit, @urlLike nvarchar(256)\n-- set @siteId = \'971135DA-EA12-4E09-AF08-13E434D4665A\'          -- ID of site collection (GUID)\n-- set @exactUrl = N\'hr\'     -- site path (example: sites/somesitecoll/somesite)\n-- set @withSubSites = 1               -- specify whether sub-sites should be returned or not\n-- set @urlLike = dbo.fn_EscapeForLike( @exactUrl, @withSubSites ) \nselect id, ScopeId, FirstUniqueAncestorWebId, FullUrl \nfrom Webs\nwhere \n    Webs.SiteId = @siteid \n    and ( Webs.FullUrl = @exactUrl \n          or Webs.FullUrl like     @urlLike \n          or (@withSubSites = 1 and DATALENGTH(@exactUrl) = 0 and Webs.FullUrl like N\'%\' )\n        )\n),\nt_lists as\n(\n    \n    -- Return tp_ID, tp_WebId, tp_scopeId in table AllLists,\n    -- ScopeId in table t_webs of site and its sub-sites    \n\n    select filtered_lists.tp_ID, filtered_lists.tp_WebId, filtered_lists.tp_scopeid, t_webs.ScopeId\n    from filtered_lists \n    inner join t_webs \n        on filtered_lists.tp_WebId = t_webs.id\n    where filtered_lists.tp_DeleteTransactionId = 0x\n),\nt_docs as\n(\n    \n    -- Return id and (DirName + \'/\' + LeafName) Url in table AllDocs\n    -- It depends on the t_lists of site and its sub sites\n\n    select Id, DirName + \'/\' + LeafName Url\n    from filtered_docs\n    inner join t_lists\n        on filtered_docs.listid = t_lists.tp_ID\n)\n\n\nselect CountType, Number\nfrom\n(\n    select \'sites\' CountType, COUNT(id) Number\n    from t_webs \n    where t_webs.FirstUniqueAncestorWebId = t_webs.Id and t_webs.FullUrl <> @exactUrl\n\n    UNION\n\n    select \'lists\' CountType, COUNT(ls.tp_ID) Number\n    from t_lists ls\n    where ls.tp_scopeid <> ls.ScopeId\n\n    UNION\n\n    select \'items\' CountType, COUNT(d.Id) Number\n    from t_docs as d\n    inner join t_perms p on d.Url = p.ScopeUrl\n) t_number\nOPTION (MERGE JOIN)' ()

  • It seems like service account does not have permission for running SQL function

    please run the following script for all content databases

    it will grant execute permission for function fn_EscapeForLike

    we require it in addition to db_datareader (http://sahelp.sharepointforall.com/permissions.html)

    USE [SHAREPOINT_CONTENT_DB];

    GRANT EXECUTE ON dbo.fn_EscapeForLike

    TO [INFOPORTAL_SERVICE_ACCOUNT];

    GO

  • Grigory,

    Can you advise what permissions exactly are been granted by adding below. Our DBA’s would like to know.

    · the execute permission for the fn_EscapeForLike function in SharePoint content database

    Thanks,

    Paul.

  • this scalar function is a part of SharePoint schema

    it convert string to form which can be used in SQL statements (by escape special symbols)

    it does not modify any table

    it even does not read anything from tables

    here is its body:

    SE [WSS_Content]

    GO

    /****** Object:  UserDefinedFunction [dbo].[fn_EscapeForLike]    Script Date: 04/08/2013 06:25:11 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fn_EscapeForLike](

        @Source nvarchar(260),

        @AddTerminalWildcard bit = 1)

    RETURNS nvarchar(1024)

    WITH SCHEMABINDING

    BEGIN

        RETURN

        CASE

            WHEN @Source IS NULL THEN NULL

            WHEN @AddTerminalWildcard = 1 AND  DATALENGTH(@Source) = 0 THEN

                N'_%'

            WHEN @AddTerminalWildcard = 1 THEN

                REPLACE(

                    REPLACE(

                        REPLACE(

                            @Source COLLATE Latin1_General_BIN,

                            N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]') + N'/%'

            ELSE

                REPLACE(

                    REPLACE(

                        REPLACE(

                            @Source COLLATE Latin1_General_BIN,

                            N'[', N'[[]'), N'%', N'[%]'), N'_', N'[_]')

        END

    END