5 Replies Latest reply on Feb 23, 2018 8:32 AM by Scott Sanson

    Anyone else tried 2018 SP2.0EV?

    Eddie Pellegrin

      I just installed it, and I'm going to have to go back to SP1.0, It's seems to have disabled my searching capabilities. When I try to search I get a "Could not access database error. I can browse to the files and access them just fine, just can't search.

       

      Date: 2018-02-22 10:28:56.319

      Module: explorer.exe

      Function: SpSearch::mhStartFileSearch

      Code: E_EDM_DATABASE_ACCESS

      Code Description: Could not access the item in the database.

      SQL-query failed:

       

       

      Set Transaction Isolation Level READ UNCOMMITTED

        • Re: Anyone else tried 2018 SP2.0EV?
          Eddie Pellegrin

          This is the complete error.

           

          Error running query in SpDbOdbc

          State=37000

          Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'Src_FillProjTableWithProjectsAndReadRights'.

           

           

          ========= LAST COMMAND ========

          Set Transaction Isolation Level READ UNCOMMITTED

           

           

          If (object_id(N'tempdb..#ProjTable') Is Not Null)

          Drop Table #ProjTable

          Create Table #ProjTable (ProjectID Int Primary Key, FileSearch Bit, ShowVer Int)

           

           

          If (object_id(N'tempdb..#SearchProc_DocMaxVersions') Is Not Null)

              Truncate Table #SearchProc_DocMaxVersions

          Else

            Create Table #SearchProc_DocMaxVersions(

              DocumentID Int

              ,ProjectID Int

              ,RevNr Int

              )

           

           

          If (object_id(N'tempdb..#SearchProc_Hits') Is Not Null)

              Truncate Table #SearchProc_Hits

          Else

            Create Table #SearchProc_Hits(

                DocumentID Int

                ,ProjectID Int

                ,Version Int

                ,Revision Int

            ,ConfigurationID Int

              )

           

           

          If (object_id(N'tempdb..#SearchProc_HitsCopy') Is Not Null)

              Truncate Table #SearchProc_HitsCopy

          Else

            Create Table #SearchProc_HitsCopy(

                DocumentID Int

                ,ProjectID Int

                ,Version Int

                ,Revision Int

            ,ConfigurationID Int

              )

           

           

          If (object_id(N'tempdb..#SearchProc_HitsTemp') Is Not Null)

              Truncate Table #SearchProc_HitsTemp

          Else

            Create Table #SearchProc_HitsTemp(

                DocumentID Int

                ,ProjectID Int

                ,Version Int

                ,Revision Int

                ,ConfigurationID Int

              )

           

           

          Declare @fi int

          set @fi=dbo.Src_GetFolderID(N'\')

           

           

          -- SPR 932625 & 1028868: a call to the function replaced by its optimized sp equivalent to satisfy both SPR-s

          --Insert Into #ProjTable

          --Select * From dbo.Src_FindProjectsWithReadRights(10, 1, @fi, 1)

          Exec Src_FillProjTableWithProjectsAndReadRights 10, 1, @fi, 1

           

           

           

           

          Declare @SWVarID Int

          Set @SWVarID = (SELECT TOP 1 VariableID FROM Variable Where VariableName = N'_SW_Last_Saved_With_')

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          Declare @CurUsrID Int Set @CurUsrID=10

          ---------* Var search *-------

           

           

           

           

          Declare @UserOName nvarchar(256)

              Set @UserOName =( Select s.Value From Settings s

              Where

                  s.UserID = @CurUsrID And

              s.VarID = 24) -- 24 - id of property which depends on image Username or FullName

           

           

          If @UserOName is Null

          Begin

             Set @UserOName = 0

          end

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          Insert Into #SearchProc_Hits(DocumentID, ProjectID,Version, Revision, ConfigurationID)

          Select D.DocumentID,

                 dip.ProjectID,

             Max(R3.RevNr),

                 Max(IsNull(ur.RevNr, 0)), 1

          From #ProjTable pt

          Join DocumentsInProjects dip On dip.ProjectID = pt.ProjectID

          Join Documents D on D.DocumentID = dip.DocumentID

          Join Revisions R3 On R3.DocumentID = D.DocumentID

           

           

           

           

           

           

           

           

          Join Status s On s.StatusID = D.CurrentStatusID

          Left Join UserRevs ur On ur.DocumentID = D.DocumentID And

                                   ur.RevNr = R3.RevNr

          Left Join HistoryVersionOverwrite hvo On hvo.DocumentID = D.DocumentID And

          hvo.RevNr = R3.RevNr

           

           

          Where  pt.FileSearch = 1 And

                 D.Deleted = 0 And

                 dip.Deleted = 0 And

                        R3.RevNr = D.LatestRevisionNo And

           

           

           

           

           

           

           

           

           

           

           

           

                Exists (Select 1

                        From TransitionHistory H

                        Join Transitions T On T.TransitionID = H.TransitionID

                        Join ( -- usr.InheritedRights is not enough because it does not

                                    -- include specific folder rights for a group

                                    -- so join with GroupStatusRights

                  Select StatusID

                  From UserStatusRights usr

                  Where usr.UserID = @CurUsrID

                                      And ((usr.Type | usr.InheritedRights) & 1) = 1

                  Union All

                  Select  gsr.StatusID

                          From dbo.GAS_fnProjectGroupMembersForUserWithNoLock(@CurUsrID, dip.ProjectID) gm

                          Join GroupStatusRights gsr

                            On gsr.GroupID = gm.GroupID

                          Where gm.ProjectID = dip.ProjectID

                                And gm.IsMember = 1

                                And ((gsr.Type & 1)=1)

                ) swr

                On swr.StatusID = T.Destination

                       

                        Where H.DocumentID = D.DocumentID And  

                              H.TransitionNr >= IsNull((Select Max(H1.TransitionNr)

                                                      From TransitionHistory H1

                                                        Where H1.DocumentID = D.DocumentID And

                                                              H1.RevNr < R3.RevNr),

                                                        H.TransitionNr) And

           

           

                              H.TransitionNr < IsNull((Select Min(H2.TransitionNr)

                                                      From TransitionHistory H2

                                                        Where H2.DocumentID = D.DocumentID And

                                                              H2.RevNr > R3.RevNr),

                                                        H.TransitionNr + 1) And

           

           

           

           

           

           

           

           

           

           

                              (s.BlockRights = 1

                                Or (swr.StatusID Is Not Null)

                               )

                              )

                -- exits end --

                  

                And ( s.BlockRights = 0

                      or

                      (

                          Exists

                                (

                                  -- Status rights

                                  Select 1

                                  From UserStatusRights

                                  Where UserStatusRights.UserID = @CurUsrID

                                  And UserStatusRights.StatusID = D.CurrentStatusID

                                  And Type & 1 = 1

                                 )

                           or

                           Exists

                                 (

                                    Select

                                    top 1 1

                                    From

                                    GAS_fnProjectGroupMembersForUser(@CurUsrID, dip.ProjectID) gm,

                                    GroupStatusRights gsr

                                    Where

                                    gsr.GroupID = gm.GroupID And

                                    dip.ProjectID Is Not Null And

                                    gm.ProjectID = dip.ProjectID And

                                    gm.IsMember = 1 And

                                    gsr.Type & 1 = 1 And

                                    gsr.StatusID = D.CurrentStatusID

                                 )

                           or

                           Exists

                                 (

                                    Select

                                    top 1 1

                                    From

                                      GroupMembers gm,

                                      GroupStatusRights gsr

                                    Where

                                      gsr.GroupID = gm.GroupID And

                                      gm.UserID = @CurUsrID And

                                      dip.ProjectID Is Null and

                                      gm.ProjectID = 1 And -- 1 is the root project

                                      gm.IsMember = 1 And

                                      gsr.Type & 1 = 1 And

                                      gsr.StatusID = D.CurrentStatusID

                                 )                       

                      )

                  )

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          Group By D.DocumentID, dip.ProjectID

           

           

           

           

          declare @SearchByState bit

          set @SearchByState = 0

           

           

           

           

          declare @SearchForCheckedOut bit

          set @SearchForCheckedOut = 0

          set @SearchForCheckedOut = 1   -- Set this bit if we search for checked out files

           

           

          if @SearchByState = 0         -- SPR 976206: Private state file(s) do not have state

          And @SearchForCheckedOut = 1  -- SPR 976204: If we search checked out files, then only search for private state files

          begin

          Set @SearchByState = 0 -- To avoid empty if block which cause syntax error, please ignore this statement

           

           

          -- SPR 881351:

          -- For private state files, the only thing that should be searchable by a user that has permission to "see private state files"

          -- is by filename or by full search. A private state file is not yet checked in, has no workflow etc. We added support for others to see the filename

          -- and No variables, no content. Etc.

          -- Admin (@CurUsrID=2) should be able to do it though. That login is a super user and should have access to all files.

          -- Only Admin (@CurUsrID=2) considered for content, variables search of private state file(s)

           

           

          Insert Into #SearchProc_HitsTemp(DocumentID, ProjectID,Version, Revision, ConfigurationID)

            Select D1.DocumentID,

                  dip.ProjectID,

                  1, -- Revision will be always 1 for the private state files.

                  0

           

           

          , 1

          From #ProjTable pt

          Join DocumentsInProjects dip With (NoLock) On pt.ProjectID = dip.ProjectID

          Join Documents D1 With (Nolock) On D1.DocumentID = dip.DocumentID

          Join Revisions R3 With (Nolock) On R3.DocumentID = D1.DocumentID

           

           

           

           

           

           

          Join Users U1 With (NoLock) On U1.UserID = D1.UserID And U1.UserID != @CurUsrID

          Where

            dip.Deleted = 0 And

            D1.ObjectTypeID = 1  And -- return only Normal Document(Doc = 1).

            D1.CurrentStatusID = U1.InitialStatusID

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          Insert Into #SearchProc_Hits(DocumentID, ProjectID,Version, Revision, ConfigurationID)

          Select * FROM #SearchProc_HitsTemp WHERE dbo.GAS_fnbHasProjectRight(@CurUsrID,  ProjectID, 4194304) = 1

           

           

          end

           

           

           

           

           

           

           

           

          Insert Into #SearchProc_DocMaxVersions (

                      DocumentID

                      ,ProjectID

                      ,RevNr

                      )

          Select sh.DocumentID

                  ,sh.ProjectID

                  ,Max(r.RevNr)

          From #SearchProc_Hits sh       

          Join dbo.Doc_fnDocumentsWithFilePermission(@CurUsrID) dfp -- Get rid of documents without filepermission

            On dfp.DocumentID = sh.DocumentID

              And dfp.Rights Is Not Null

          Join Revisions r -- Add all permitted transitions and versions

            On r.DocumentID = sh.DocumentID

          Join Documents D

            On D.DocumentID = r.DocumentID

              And D.Deleted = 0

          Join Status s

            On s.StatusID = D.CurrentStatusID

          Join #ProjTable pt  -- Join in order to determine if to show versions in the specific project

          On pt.ProjectID = sh.ProjectID

          Left Join UserRevs ur -- When the user wants to see non-user revisisions only ur.RevNr will be non-null

          On pt.ShowVer = 0

          And ur.DocumentID = r.DocumentID

          And ur.RevNr = r.RevNr

          Where pt.ShowVer = 1  -- Either the user wants the latest permissible version or the latest revision for

          Or ur.RevNr Is Not Null -- which there is a transition with state permission

          And

          Exists(

          Select 1

                        From TransitionHistory H

                        Join Transitions T On T.TransitionID = H.TransitionID

                        Join (

                  Select StatusID

                  From UserStatusRights usr

                  Where usr.UserID = @CurUsrID

                                      And ((usr.Type | usr.InheritedRights) & 1) = 1

                          Union All

                          Select  gsr.StatusID

                          From dbo.GAS_fnProjectGroupMembersForUserWithNoLock(@CurUsrID, pt.ProjectID) gm

                          Join GroupStatusRights gsr

                            On gsr.GroupID = gm.GroupID

                          Where gm.ProjectID = pt.ProjectID

                                And gm.IsMember = 1

                                And ((gsr.Type & 1)=1)             

                                ) swr

                          On swr.StatusID = T.Destination

                        Where H.DocumentID = D.DocumentID And  

                              H.TransitionNr >= IsNull((Select Max(H1.TransitionNr)

                                                      From TransitionHistory H1

                                                        Where H1.DocumentID = D.DocumentID And

                                                              H1.RevNr < r.RevNr),

                                                        H.TransitionNr) And

           

           

                              H.TransitionNr < IsNull((Select Min(H2.TransitionNr)

                                                      From TransitionHistory H2

                                                        Where H2.DocumentID = D.DocumentID And

                                                              H2.RevNr > r.RevNr),

                                                        H.TransitionNr + 1) And

           

           

                              (s.BlockRights = 1

                                Or (swr.StatusID Is Not Null)

                              )

          )

          Group By sh.DocumentID, sh.ProjectID

           

           

          Create NonClustered Index IDX_DocMaxVersions ON #SearchProc_DocMaxVersions (ProjectID, DocumentID)

           

           

          Declare @bMostShowVer Bit

          -- Use Version instead of Revision incase the user mostly has

          -- show version rights

          Select @bMostShowVer = Case When ((Select COUNT(*)

          From UserProjectRights

          Where UserID=@CurUsrID And ((Type & 131072)<>0)) -- ShowVer

          >

          (Select COUNT(*)

          From UserProjectRights

          Where UserID=@CurUsrID And ((Type & 131072)=0)) -- ShowVer

          )

          Then 1

          Else

          0

          End

           

           

          Declare @VarTab Table (VariableID Int)

          Insert Into @VarTab (VariableID)

          Select 55

           

           

          Union All

           

           

          Select 47

           

           

          Union All

           

           

          Select 48

           

           

          Union All

           

           

          Select 58

           

           

          Union All

           

           

          Select 50

           

           

          Union All

           

           

          Select 72

           

           

           

           

           

           

          --SPR 499339

          Declare @DocConf Table (DocumentID Int, ConfigurationID Int)

          Insert Into @DocConf

          Select distinct d.DocumentID , (CASE when sph.ConfigurationID = 1 Or sph.ConfigurationID Is NULL then drc.ConfigurationID else sph.ConfigurationID end) ConfigurationID

          From Documents d

          Join #SearchProc_Hits sph On sph.DocumentID = d.DocumentID

          Join DocumentRevisionConfiguration drc On drc.DocumentID = d.DocumentID And drc.RevisionNo = sph.Version

           

           

          If (object_id(N'tempdb..#VariableValueFilteredByDocConf') Is Not Null)

          Drop Table #VariableValueFilteredByDocConf

          Create Table #VariableValueFilteredByDocConf (VariableID Int, DocumentID Int, RevisionNo Int, ValueText nvarchar(max))

           

           

          -- optimization due to SPR 996661

          Insert Into #VariableValueFilteredByDocConf (VariableID, DocumentID, RevisionNo, ValueText)

          Select vv.VariableID, vv.DocumentID, vv.RevisionNo, vv.ValueText

          From   VariableValue vv

          Join   @VarTab vt     On vv.VariableID = vt.VariableID

          Join   @DocConf doccf On vv.DocumentID = doccf.DocumentID And vv.ConfigurationID = doccf.ConfigurationID

          WHERE  vv.ProjectID = 2

           

           

          Create NonClustered Index IDX_VariableValueFilteredByDocConf ON #VariableValueFilteredByDocConf (DocumentID, RevisionNo, VariableID)

           

           

          ;With CTE_CustomColumnValues As

          (

          Select DocumentID

            ,[55] As v55,[47] As v47,[48] As v48,[58] As v58,[50] As v50,[72] As v72

          From

          (

          Select

          vv.VariableID

          ,sh.DocumentID

                  ,vv.ValueText

          From (

                  Select DocumentID, Max(Case When (@bMostShowVer=1) Then

                  Version

                  Else

                  Revision

                  End )As Version

            From #SearchProc_Hits

            Group By DocumentID

          ) sh

          Left Join #VariableValueFilteredByDocConf vv

          On vv.DocumentID = sh.DocumentID

          And vv.RevisionNo = ( Select Max(vv2.RevisionNo)

          From #VariableValueFilteredByDocConf vv2

          Where  vv2.DocumentID = vv.DocumentID

          And vv2.RevisionNo <= sh.Version

          And vv2.VariableID = vv.VariableID

          -- Can't use confuguration ids because there will be one value for each configuration

          --And vv2.ConfigurationID = vv.ConfigurationID

          )

          And Len(vv.ValueText)>0

          ) DataTable

          Pivot

          (

          Max(ValueText)

          For VariableID In (

                              [55],[47],[48],[58],[50],[72]

                             )

          ) PivotTable

          )

           

           

          Select  D.DocumentID,

                  sh.ProjectID,

                  (case When D.ObjectTypeID = 4 Then 3 Else 0 End), --4=ObjType_Item, 3=Srit_Item, 0=Srit_File

                  D.Shared,

                  Ps.Path + D.FileName,

                  R.Date,

              (Case When U.InitialStatusID = D.CurrentStatusID And U.UserID != @CurUsrID Then 1 Else 0 End),

                  Case

                      When BR.BranchDocID Is Not Null Then 1 -- Branched doc

                      When Exists(Select Top 1 b1.SrcDocID From BranchRelation b1

                                  Where D.DocumentID = b1.SrcDocID ) Then 1

                      Else 0

                  End As IsBranched,

                  D.Flags,

                  Case

                    When BR.BranchDocID Is Not Null

                    And DSrc.Deleted = 0 -- SPR 1024135

                    Then 1 -- target file

                    Else 0

                  End As IsBranchTargetFile,

          D.Filename,

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v55

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'Part Number', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v47

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'Revision', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v48

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'Description', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          (

            Select 

              S9.Name + convert(nvarchar(1),0x0a) + S9.IconName

            From 

              Status S9

              Join Transitions T9 On T9.Destination = S9.StatusID

              Join TransitionHistory Th9 On

                Th9.TransitionID = T9.TransitionID AND

                Th9.DocumentID = R.DocumentID And 

                Th9.TransitionNr = 

                (

                   SELECT

                     max( H8.TransitionNr)

                   FROM

                     TransitionHistory H8

                     Join Transitions T8 ON T8.TransitionID = H8.TransitionID

                   WHERE

                     H8.DocumentID = R.DocumentID AND

                     H8.RevNr <= R.RevNr

                )

          ) as StatName,

          IsNull( ( Select Case When St.UserID > 0 Then U1.FullName Else U1.Username End   From Users U1 Left Join Settings St On    St.UserID = U1.UserID And St.VarID = 24 And CONVERT(char,St.Value)=N'1'  Where U1.UserID = D.UserID And U1.UserID >= 2), N'') as Username,

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v58

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'File Type', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v50

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'Legacy Number', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          (Select ConfigurationName From DocumentConfiguration Where ConfigurationID = sh.ConfigurationID),

          Case When ((@bMostShowVer=1 And P.ShowVer=1)

           

           

          Or (@bMostShowVer=0 And P.ShowVer=0)

           

           

          Or (sh.Version=sh.Revision)

           

           

          )

           

           

          Then

           

           

          ccv.v72

           

           

            Else

           

           

          ( Select( dbo.acFindAnyVariableValueBOM( N'Author', D.DocumentID, R3.RevNr )))

           

           

          End

           

           

           

           

          ,

          D.LockDomain + N'   ' + Replace( Replace( Replace( D.LockPath, N'<items>\', N'') + N'\\\', N'\\\\', N''), N'\\\', N''),

          null,

          Ps.Path

          From    #SearchProc_Hits sh

          Join    #ProjTable P On P.ProjectID = sh.ProjectID

          Join    Projects Ps On Ps.ProjectID = sh.ProjectID

          Join    Documents D On D.DocumentID = sh.DocumentID

          Join    Users U On U.UserID = D.UserID

          Join    Revisions R3 On R3.DocumentID = D.DocumentID And   -- Found revision

                                  R3.RevNr = Case When P.ShowVer = 0 Then sh.Revision Else sh.Version End

          Join    #SearchProc_DocMaxVersions docmax

                    On docmax.ProjectID = sh.ProjectID

                      And docmax.DocumentID = sh.DocumentID

          Join    Revisions R On R.DocumentID = D.DocumentID And        -- Latest revision

                                 R.RevNr = docmax.RevNr

          Join    Status s On s.StatusID = d.CurrentStatusID  -- Status is here because sometimes s.Name is referenced

          Left Join BranchRelation BR On D.DocumentID = BR.BranchDocID

          Left Join Documents DSrc On DSrc.DocumentID = BR.SrcDocID

          Left Join Documents DBr On DBr.DocumentID = BR.BranchDocID

          Join    CTE_CustomColumnValues ccv

                    On ccv.DocumentID = D.DocumentID

           

           

          Union All

          Select

            Ps.ProjectID,

            (Select ProjectID From ProjectTree Where ChildProject=Ps.ProjectID), --Parent ID

            (case when Ps.FolderType = 2 Then 4 Else 1 End), --2=Foty_ItemFolder, 4=Srit_ItemFolder, 1=Srit_Folder

            0, --Shared

            Ps.Path,

            convert(datetime, null), 0,0,/*Modified*/ 0 /* Flags */,0/*BranchTargetFile*/,  Ps.Name,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Part Number', Ps.ProjectID ) ) )

           

           

           

           

          ,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Revision', Ps.ProjectID ) ) )

           

           

           

           

          ,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Description', Ps.ProjectID ) ) )

           

           

           

           

          ,

          null,

          null,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'File Type', Ps.ProjectID ) ) )

           

           

           

           

          ,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Legacy Number', Ps.ProjectID ) ) )

           

           

           

           

          ,

          null,

          ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Author', Ps.ProjectID ) ) )

           

           

           

           

          ,

          null,

          null,

          LEFT( Ps.Path, LEN(Ps.Path) +1 -CHARINDEX(N'\', Reverse(Ps.Path), 1+CHARINDEX(N'\', Reverse(Ps.Path), 1)))

          From 

            #ProjTable Pt

           

           

           

           

           

           

          Join Projects Ps

            On Ps.ProjectID = Pt.ProjectID

              And Ps.Deleted = 0

          Where

            1 = 1

           

           

           

           

           

           

          Drop Table #ProjTable

          Drop Table #SearchProc_DocMaxVersions

          Drop Table #SearchProc_Hits

          Drop Table #SearchProc_HitsCopy

          Drop Table #SearchProc_HitsTemp

          If (object_id(N'tempdb..#DocMatchesFromCardTab') Is Not Null)

          Drop Table #DocMatchesFromCardTab

          If (object_id(N'tempdb..#ProjectMatchesFromCardTab') Is Not Null)

          Drop Table #ProjectMatchesFromCardTab

          If (object_id(N'tempdb..#ExcludedVariableValues') Is Not Null)

          Drop Table #ExcludedVariableValues

          If (object_id(N'tempdb..#SearchMatchedDocument') Is Not Null)

          Drop Table #SearchMatchedDocument

          If (object_id(N'tempdb..#UnVerifiedVarsSearchResultTable') Is Not Null)

          Drop Table #UnVerifiedVarsSearchResultTable

          If (object_id(N'tempdb..#ProjectMatchesFromVariablesTab') Is Not Null)

          Drop Table #ProjectMatchesFromVariablesTab

          If (object_id(N'tempdb..#ProjectMatchesFromVariablesLevel2') Is Not Null)

          Drop Table #ProjectMatchesFromVariablesLevel2

          If (object_id(N'tempdb..#ProjectMatchesFromVariablesLevel1') Is Not Null)

          Drop Table #ProjectMatchesFromVariablesLevel1

          If (object_id(N'tempdb..#VariableValueFilteredByDocConf') Is Not Null)

          Drop Table #VariableValueFilteredByDocConf

           

           

          Set Transaction Isolation Level READ COMMITTED

            • Re: Anyone else tried 2018 SP2.0EV?
              Eddie Pellegrin

              SQL-query failed:

               

               

              Set Transaction Isolation Level READ UNCOMMITTED

               

               

              If (object_id(N'tempdb..#ProjTable') Is Not Null)

              Drop Table #ProjTable

              Create Table #ProjTable (ProjectID Int Primary Key, FileSearch Bit, ShowVer Int)

               

               

              If (object_id(N'tempdb..#SearchProc_DocMaxVersions') Is Not Null)

                  Truncate Table #SearchProc_DocMaxVersions

              Else

                Create Table #SearchProc_DocMaxVersions(

                  DocumentID Int

                  ,ProjectID Int

                  ,RevNr Int

                  )

               

               

              If (object_id(N'tempdb..#SearchProc_Hits') Is Not Null)

                  Truncate Table #SearchProc_Hits

              Else

                Create Table #SearchProc_Hits(

                    DocumentID Int

                    ,ProjectID Int

                    ,Version Int

                    ,Revision Int

                ,ConfigurationID Int

                  )

               

               

              If (object_id(N'tempdb..#SearchProc_HitsCopy') Is Not Null)

                  Truncate Table #SearchProc_HitsCopy

              Else

                Create Table #SearchProc_HitsCopy(

                    DocumentID Int

                    ,ProjectID Int

                    ,Version Int

                    ,Revision Int

                ,ConfigurationID Int

                  )

               

               

              If (object_id(N'tempdb..#SearchProc_HitsTemp') Is Not Null)

                  Truncate Table #SearchProc_HitsTemp

              Else

                Create Table #SearchProc_HitsTemp(

                    DocumentID Int

                    ,ProjectID Int

                    ,Version Int

                    ,Revision Int

                    ,ConfigurationID Int

                  )

               

               

              Declare @fi int

              set @fi=dbo.Src_GetFolderID(N'\')

               

               

              -- SPR 932625 & 1028868: a call to the function replaced by its optimized sp equivalent to satisfy both SPR-s

              --Insert Into #ProjTable

              --Select * From dbo.Src_FindProjectsWithReadRights(2, 1, @fi, 1)

              Exec Src_FillProjTableWithProjectsAndReadRights 2, 1, @fi, 1

               

               

               

               

              Declare @SWVarID Int

              Set @SWVarID = (SELECT TOP 1 VariableID FROM Variable Where VariableName = N'_SW_Last_Saved_With_')

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              Declare @CurUsrID Int Set @CurUsrID=2

              ---------* Var search *-------

               

               

               

               

              Declare @UserOName nvarchar(256)

                  Set @UserOName =( Select s.Value From Settings s

                  Where

                      s.UserID = @CurUsrID And

                  s.VarID = 24) -- 24 - id of property which depends on image Username or FullName

               

               

              If @UserOName is Null

              Begin

                 Set @UserOName = 0

              end

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              Insert Into #SearchProc_Hits(DocumentID, ProjectID,Version, Revision, ConfigurationID)

              Select D.DocumentID,

                     dip.ProjectID,

                 Max(R3.RevNr),

                     Max(IsNull(ur.RevNr, 0)), 1

              From #ProjTable pt

              Join DocumentsInProjects dip On dip.ProjectID = pt.ProjectID

              Join Documents D on D.DocumentID = dip.DocumentID

              Join Revisions R3 On R3.DocumentID = D.DocumentID

               

               

               

               

               

               

               

               

              Join Status s On s.StatusID = D.CurrentStatusID

              Left Join UserRevs ur On ur.DocumentID = D.DocumentID And

                                       ur.RevNr = R3.RevNr

              Left Join HistoryVersionOverwrite hvo On hvo.DocumentID = D.DocumentID And

              hvo.RevNr = R3.RevNr

               

               

              Where  pt.FileSearch = 1 And

                     D.Deleted = 0 And

                     dip.Deleted = 0 And

                            R3.RevNr = D.LatestRevisionNo And

               

               

               

               

               

               

               

               

               

               

               

               

                    Exists (Select 1

                            From TransitionHistory H

                            Join Transitions T On T.TransitionID = H.TransitionID

                            Join ( -- usr.InheritedRights is not enough because it does not

                                        -- include specific folder rights for a group

                                        -- so join with GroupStatusRights

                      Select StatusID

                      From UserStatusRights usr

                      Where usr.UserID = @CurUsrID

                                          And ((usr.Type | usr.InheritedRights) & 1) = 1

                      Union All

                      Select  gsr.StatusID

                              From dbo.GAS_fnProjectGroupMembersForUserWithNoLock(@CurUsrID, dip.ProjectID) gm

                              Join GroupStatusRights gsr

                                On gsr.GroupID = gm.GroupID

                              Where gm.ProjectID = dip.ProjectID

                                    And gm.IsMember = 1

                                    And ((gsr.Type & 1)=1)

                    ) swr

                    On swr.StatusID = T.Destination

                           

                            Where H.DocumentID = D.DocumentID And  

                                  H.TransitionNr >= IsNull((Select Max(H1.TransitionNr)

                                                          From TransitionHistory H1

                                                            Where H1.DocumentID = D.DocumentID And

                                                                  H1.RevNr < R3.RevNr),

                                                            H.TransitionNr) And

               

               

                                  H.TransitionNr < IsNull((Select Min(H2.TransitionNr)

                                                          From TransitionHistory H2

                                                            Where H2.DocumentID = D.DocumentID And

                                                                  H2.RevNr > R3.RevNr),

                                                            H.TransitionNr + 1) And

               

               

               

               

               

               

               

               

               

               

                                  (s.BlockRights = 1

                                    Or (swr.StatusID Is Not Null)

                                   )

                                  )

                    -- exits end --

                      

                    And ( s.BlockRights = 0

                          or

                          (

                              Exists

                                    (

                                      -- Status rights

                                      Select 1

                                      From UserStatusRights

                                      Where UserStatusRights.UserID = @CurUsrID

                                      And UserStatusRights.StatusID = D.CurrentStatusID

                                      And Type & 1 = 1

                                     )

                               or

                               Exists

                                     (

                                        Select

                                        top 1 1

                                        From

                                        GAS_fnProjectGroupMembersForUser(@CurUsrID, dip.ProjectID) gm,

                                        GroupStatusRights gsr

                                        Where

                                        gsr.GroupID = gm.GroupID And

                                        dip.ProjectID Is Not Null And

                                        gm.ProjectID = dip.ProjectID And

                                        gm.IsMember = 1 And

                                        gsr.Type & 1 = 1 And

                                        gsr.StatusID = D.CurrentStatusID

                                     )

                               or

                               Exists

                                     (

                                        Select

                                        top 1 1

                                        From

                                          GroupMembers gm,

                                          GroupStatusRights gsr

                                        Where

                                          gsr.GroupID = gm.GroupID And

                                          gm.UserID = @CurUsrID And

                                          dip.ProjectID Is Null and

                                          gm.ProjectID = 1 And -- 1 is the root project

                                          gm.IsMember = 1 And

                                          gsr.Type & 1 = 1 And

                                          gsr.StatusID = D.CurrentStatusID

                                     )                       

                          )

                      )

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              Group By D.DocumentID, dip.ProjectID

               

               

               

               

              declare @SearchByState bit

              set @SearchByState = 0

               

               

               

               

              declare @SearchForCheckedOut bit

              set @SearchForCheckedOut = 0

              set @SearchForCheckedOut = 1   -- Set this bit if we search for checked out files

               

               

              if @SearchByState = 0         -- SPR 976206: Private state file(s) do not have state

              And @SearchForCheckedOut = 1  -- SPR 976204: If we search checked out files, then only search for private state files

              begin

              Set @SearchByState = 0 -- To avoid empty if block which cause syntax error, please ignore this statement

               

               

              -- SPR 881351:

              -- For private state files, the only thing that should be searchable by a user that has permission to "see private state files"

              -- is by filename or by full search. A private state file is not yet checked in, has no workflow etc. We added support for others to see the filename

              -- and No variables, no content. Etc.

              -- Admin (@CurUsrID=2) should be able to do it though. That login is a super user and should have access to all files.

              -- Only Admin (@CurUsrID=2) considered for content, variables search of private state file(s)

               

               

              Insert Into #SearchProc_HitsTemp(DocumentID, ProjectID,Version, Revision, ConfigurationID)

                Select D1.DocumentID,

                      dip.ProjectID,

                      1, -- Revision will be always 1 for the private state files.

                      0

               

               

              , 1

              From #ProjTable pt

              Join DocumentsInProjects dip With (NoLock) On pt.ProjectID = dip.ProjectID

              Join Documents D1 With (Nolock) On D1.DocumentID = dip.DocumentID

              Join Revisions R3 With (Nolock) On R3.DocumentID = D1.DocumentID

               

               

               

               

               

               

              Join Users U1 With (NoLock) On U1.UserID = D1.UserID And U1.UserID != @CurUsrID

              Where

                dip.Deleted = 0 And

                D1.ObjectTypeID = 1  And -- return only Normal Document(Doc = 1).

                D1.CurrentStatusID = U1.InitialStatusID

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

              Insert Into #SearchProc_Hits(DocumentID, ProjectID,Version, Revision, ConfigurationID)

              Select * FROM #SearchProc_HitsTemp WHERE dbo.GAS_fnbHasProjectRight(@CurUsrID,  ProjectID, 4194304) = 1

               

               

              end

               

               

               

               

               

               

               

               

              Insert Into #SearchProc_DocMaxVersions (

                          DocumentID

                          ,ProjectID

                          ,RevNr

                          )

              Select sh.DocumentID

                      ,sh.ProjectID

                      ,Max(r.RevNr)

              From #SearchProc_Hits sh       

              Join dbo.Doc_fnDocumentsWithFilePermission(@CurUsrID) dfp -- Get rid of documents without filepermission

                On dfp.DocumentID = sh.DocumentID

                  And dfp.Rights Is Not Null

              Join Revisions r -- Add all permitted transitions and versions

                On r.DocumentID = sh.DocumentID

              Join Documents D

                On D.DocumentID = r.DocumentID

                  And D.Deleted = 0

              Join Status s

                On s.StatusID = D.CurrentStatusID

              Join #ProjTable pt  -- Join in order to determine if to show versions in the specific project

              On pt.ProjectID = sh.ProjectID

              Left Join UserRevs ur -- When the user wants to see non-user revisisions only ur.RevNr will be non-null

              On pt.ShowVer = 0

              And ur.DocumentID = r.DocumentID

              And ur.RevNr = r.RevNr

              Where pt.ShowVer = 1  -- Either the user wants the latest permissible version or the latest revision for

              Or ur.RevNr Is Not Null -- which there is a transition with state permission

              And

              Exists(

              Select 1

                            From TransitionHistory H

                            Join Transitions T On T.TransitionID = H.TransitionID

                            Join (

                      Select StatusID

                      From UserStatusRights usr

                      Where usr.UserID = @CurUsrID

                                          And ((usr.Type | usr.InheritedRights) & 1) = 1

                              Union All

                              Select  gsr.StatusID

                              From dbo.GAS_fnProjectGroupMembersForUserWithNoLock(@CurUsrID, pt.ProjectID) gm

                              Join GroupStatusRights gsr

                                On gsr.GroupID = gm.GroupID

                              Where gm.ProjectID = pt.ProjectID

                                    And gm.IsMember = 1

                                    And ((gsr.Type & 1)=1)             

                                    ) swr

                              On swr.StatusID = T.Destination

                            Where H.DocumentID = D.DocumentID And  

                                  H.TransitionNr >= IsNull((Select Max(H1.TransitionNr)

                                                          From TransitionHistory H1

                                                            Where H1.DocumentID = D.DocumentID And

                                                                  H1.RevNr < r.RevNr),

                                                            H.TransitionNr) And

               

               

                                  H.TransitionNr < IsNull((Select Min(H2.TransitionNr)

                                                          From TransitionHistory H2

                                                            Where H2.DocumentID = D.DocumentID And

                                                                  H2.RevNr > r.RevNr),

                                                            H.TransitionNr + 1) And

               

               

                                  (s.BlockRights = 1

                                    Or (swr.StatusID Is Not Null)

                                  )

              )

              Group By sh.DocumentID, sh.ProjectID

               

               

              Create NonClustered Index IDX_DocMaxVersions ON #SearchProc_DocMaxVersions (ProjectID, DocumentID)

               

               

              Declare @bMostShowVer Bit

              -- Use Version instead of Revision incase the user mostly has

              -- show version rights

              Select @bMostShowVer = Case When ((Select COUNT(*)

              From UserProjectRights

              Where UserID=@CurUsrID And ((Type & 131072)<>0)) -- ShowVer

              >

              (Select COUNT(*)

              From UserProjectRights

              Where UserID=@CurUsrID And ((Type & 131072)=0)) -- ShowVer

              )

              Then 1

              Else

              0

              End

               

               

              Declare @VarTab Table (VariableID Int)

              Insert Into @VarTab (VariableID)

              Select 55

               

               

              Union All

               

               

              Select 47

               

               

              Union All

               

               

              Select 48

               

               

              Union All

               

               

              Select 58

               

               

              Union All

               

               

              Select 50

               

               

              Union All

               

               

              Select 72

               

               

               

               

               

               

              --SPR 499339

              Declare @DocConf Table (DocumentID Int, ConfigurationID Int)

              Insert Into @DocConf

              Select distinct d.DocumentID , (CASE when sph.ConfigurationID = 1 Or sph.ConfigurationID Is NULL then drc.ConfigurationID else sph.ConfigurationID end) ConfigurationID

              From Documents d

              Join #SearchProc_Hits sph On sph.DocumentID = d.DocumentID

              Join DocumentRevisionConfiguration drc On drc.DocumentID = d.DocumentID And drc.RevisionNo = sph.Version

               

               

              If (object_id(N'tempdb..#VariableValueFilteredByDocConf') Is Not Null)

              Drop Table #VariableValueFilteredByDocConf

              Create Table #VariableValueFilteredByDocConf (VariableID Int, DocumentID Int, RevisionNo Int, ValueText nvarchar(max))

               

               

              -- optimization due to SPR 996661

              Insert Into #VariableValueFilteredByDocConf (VariableID, DocumentID, RevisionNo, ValueText)

              Select vv.VariableID, vv.DocumentID, vv.RevisionNo, vv.ValueText

              From   VariableValue vv

              Join   @VarTab vt     On vv.VariableID = vt.VariableID

              Join   @DocConf doccf On vv.DocumentID = doccf.DocumentID And vv.ConfigurationID = doccf.ConfigurationID

              WHERE  vv.ProjectID = 2

               

               

              Create NonClustered Index IDX_VariableValueFilteredByDocConf ON #VariableValueFilteredByDocConf (DocumentID, RevisionNo, VariableID)

               

               

              ;With CTE_CustomColumnValues As

              (

              Select DocumentID

                ,[55] As v55,[47] As v47,[48] As v48,[58] As v58,[50] As v50,[72] As v72

              From

              (

              Select

              vv.VariableID

              ,sh.DocumentID

                      ,vv.ValueText

              From (

                      Select DocumentID, Max(Case When (@bMostShowVer=1) Then

                      Version

                      Else

                      Revision

                      End )As Version

                From #SearchProc_Hits

                Group By DocumentID

              ) sh

              Left Join #VariableValueFilteredByDocConf vv

              On vv.DocumentID = sh.DocumentID

              And vv.RevisionNo = ( Select Max(vv2.RevisionNo)

              From #VariableValueFilteredByDocConf vv2

              Where  vv2.DocumentID = vv.DocumentID

              And vv2.RevisionNo <= sh.Version

              And vv2.VariableID = vv.VariableID

              -- Can't use confuguration ids because there will be one value for each configuration

              --And vv2.ConfigurationID = vv.ConfigurationID

              )

              And Len(vv.ValueText)>0

              ) DataTable

              Pivot

              (

              Max(ValueText)

              For VariableID In (

                                  [55],[47],[48],[58],[50],[72]

                                 )

              ) PivotTable

              )

               

               

              Select  D.DocumentID,

                      sh.ProjectID,

                      (case When D.ObjectTypeID = 4 Then 3 Else 0 End), --4=ObjType_Item, 3=Srit_Item, 0=Srit_File

                      D.Shared,

                      Ps.Path + D.FileName,

                      R.Date,

                  (Case When U.InitialStatusID = D.CurrentStatusID And U.UserID != @CurUsrID Then 1 Else 0 End),

                      Case

                          When BR.BranchDocID Is Not Null Then 1 -- Branched doc

                          When Exists(Select Top 1 b1.SrcDocID From BranchRelation b1

                                      Where D.DocumentID = b1.SrcDocID ) Then 1

                          Else 0

                      End As IsBranched,

                      D.Flags,

                      Case

                        When BR.BranchDocID Is Not Null

                        And DSrc.Deleted = 0 -- SPR 1024135

                        Then 1 -- target file

                        Else 0

                      End As IsBranchTargetFile,

              D.Filename,

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v55

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'Part Number', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v47

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'Revision', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v48

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'Description', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              (

                Select 

                  S9.Name + convert(nvarchar(1),0x0a) + S9.IconName

                From 

                  Status S9

                  Join Transitions T9 On T9.Destination = S9.StatusID

                  Join TransitionHistory Th9 On

                    Th9.TransitionID = T9.TransitionID AND

                    Th9.DocumentID = R.DocumentID And 

                    Th9.TransitionNr = 

                    (

                       SELECT

                         max( H8.TransitionNr)

                       FROM

                         TransitionHistory H8

                         Join Transitions T8 ON T8.TransitionID = H8.TransitionID

                       WHERE

                         H8.DocumentID = R.DocumentID AND

                         H8.RevNr <= R.RevNr

                    )

              ) as StatName,

              IsNull( ( Select Case When St.UserID > 0 Then U1.FullName Else U1.Username End   From Users U1 Left Join Settings St On    St.UserID = U1.UserID And St.VarID = 24 And CONVERT(char,St.Value)=N'1'  Where U1.UserID = D.UserID And U1.UserID >= 2), N'') as Username,

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v58

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'File Type', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v50

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'Legacy Number', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              (Select ConfigurationName From DocumentConfiguration Where ConfigurationID = sh.ConfigurationID),

              Case When ((@bMostShowVer=1 And P.ShowVer=1)

               

               

              Or (@bMostShowVer=0 And P.ShowVer=0)

               

               

              Or (sh.Version=sh.Revision)

               

               

              )

               

               

              Then

               

               

              ccv.v72

               

               

                Else

               

               

              ( Select( dbo.acFindAnyVariableValueBOM( N'Author', D.DocumentID, R3.RevNr )))

               

               

              End

               

               

               

               

              ,

              D.LockDomain + N'   ' + Replace( Replace( Replace( D.LockPath, N'<items>\', N'') + N'\\\', N'\\\\', N''), N'\\\', N''),

              null,

              Ps.Path

              From    #SearchProc_Hits sh

              Join    #ProjTable P On P.ProjectID = sh.ProjectID

              Join    Projects Ps On Ps.ProjectID = sh.ProjectID

              Join    Documents D On D.DocumentID = sh.DocumentID

              Join    Users U On U.UserID = D.UserID

              Join    Revisions R3 On R3.DocumentID = D.DocumentID And   -- Found revision

                                      R3.RevNr = Case When P.ShowVer = 0 Then sh.Revision Else sh.Version End

              Join    #SearchProc_DocMaxVersions docmax

                        On docmax.ProjectID = sh.ProjectID

                          And docmax.DocumentID = sh.DocumentID

              Join    Revisions R On R.DocumentID = D.DocumentID And        -- Latest revision

                                     R.RevNr = docmax.RevNr

              Join    Status s On s.StatusID = d.CurrentStatusID  -- Status is here because sometimes s.Name is referenced

              Left Join BranchRelation BR On D.DocumentID = BR.BranchDocID

              Left Join Documents DSrc On DSrc.DocumentID = BR.SrcDocID

              Left Join Documents DBr On DBr.DocumentID = BR.BranchDocID

              Join    CTE_CustomColumnValues ccv

                        On ccv.DocumentID = D.DocumentID

               

               

              Union All

              Select

                Ps.ProjectID,

                (Select ProjectID From ProjectTree Where ChildProject=Ps.ProjectID), --Parent ID

                (case when Ps.FolderType = 2 Then 4 Else 1 End), --2=Foty_ItemFolder, 4=Srit_ItemFolder, 1=Srit_Folder

                0, --Shared

                Ps.Path,

                convert(datetime, null), 0,0,/*Modified*/ 0 /* Flags */,0/*BranchTargetFile*/,  Ps.Name,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Part Number', Ps.ProjectID ) ) )

               

               

               

               

              ,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Revision', Ps.ProjectID ) ) )

               

               

               

               

              ,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Description', Ps.ProjectID ) ) )

               

               

               

               

              ,

              null,

              null,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'File Type', Ps.ProjectID ) ) )

               

               

               

               

              ,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Legacy Number', Ps.ProjectID ) ) )

               

               

               

               

              ,

              null,

              ( Select( dbo.acFindFolderVariableValueForSearchByID( N'Author', Ps.ProjectID ) ) )

               

               

               

               

              ,

              null,

              null,

              LEFT( Ps.Path, LEN(Ps.Path) +1 -CHARINDEX(N'\', Reverse(Ps.Path), 1+CHARINDEX(N'\', Reverse(Ps.Path), 1)))

              From 

                #ProjTable Pt

               

               

               

               

               

               

              Join Projects Ps

                On Ps.ProjectID = Pt.ProjectID

                  And Ps.Deleted = 0

              Where

                1 = 1

               

               

               

               

               

               

              Drop Table #ProjTable

              Drop Table #SearchProc_DocMaxVersions

              Drop Table #SearchProc_Hits

              Drop Table #SearchProc_HitsCopy

              Drop Table #SearchProc_HitsTemp

              If (object_id(N'tempdb..#DocMatchesFromCardTab') Is Not Null)

              Drop Table #DocMatchesFromCardTab

              If (object_id(N'tempdb..#ProjectMatchesFromCardTab') Is Not Null)

              Drop Table #ProjectMatchesFromCardTab

              If (object_id(N'tempdb..#ExcludedVariableValues') Is Not Null)

              Drop Table #ExcludedVariableValues

              If (object_id(N'tempdb..#SearchMatchedDocument') Is Not Null)

              Drop Table #SearchMatchedDocument

              If (object_id(N'tempdb..#UnVerifiedVarsSearchResultTable') Is Not Null)

              Drop Table #UnVerifiedVarsSearchResultTable

              If (object_id(N'tempdb..#ProjectMatchesFromVariablesTab') Is Not Null)

              Drop Table #ProjectMatchesFromVariablesTab

              If (object_id(N'tempdb..#ProjectMatchesFromVariablesLevel2') Is Not Null)

              Drop Table #ProjectMatchesFromVariablesLevel2

              If (object_id(N'tempdb..#ProjectMatchesFromVariablesLevel1') Is Not Null)

              Drop Table #ProjectMatchesFromVariablesLevel1

              If (object_id(N'tempdb..#VariableValueFilteredByDocConf') Is Not Null)

              Drop Table #VariableValueFilteredByDocConf

               

               

              Set Transaction Isolation Level READ COMMITTED

            • Re: Anyone else tried 2018 SP2.0EV?
              Charley Saint

              Sounds like you updated your client but not the Server/Database. I would guess the SP2 client is incompatible with SP1 Server and Database since that stored procedure Src_FillProjTableWithProjectsAndReadRights doesn't exist in an SP1 database. It should have told you it was incompatible though so that may very well be a bug worth reporting.

              • Re: Anyone else tried 2018 SP2.0EV?
                Scott Sanson

                We've received similar comments in EV sent to us through the Sentiment Feedback tool, and this will now be fixed with the full release of 2018 SP02.

                 

                Thanks for the feedback!