AnsweredAssumed Answered

PDM Search Broken after Moving/Upgrading Server

Question asked by Jim Sculley on Jul 8, 2020
Latest reply on Jul 8, 2020 by Jim Sculley

Yesterday evening I installed SW PDM 2019 SP5 in a new Windows Server 2012 machine.  I then followed the instructions for 'Moving Server Components to Another System' in the PDM installation guide to move our existing PDM 2017 database and file archive to the new server.  Lastly, I used the 'Upgrade.exe' tool to update the database from 2017 to 2019.

 

All seemed to go well.  I could log in, browse and check in/out files, so I called it a night.  This morning, I found that all PDM searches fail with the generic error:

 

 

The log file shows this:

 

=======

Error running query in SpDbOdbc
State=37000
Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'From'.

=======

 

The 600 line SQL query contains this bit of invalid SQL:

 

(Select ConfigurationName From DocumentConfiguration Where ConfigurationID = sh.ConfigurationID),
D.DocumentID,
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

 

 

It's the trailing comma after D.DocumentID that is tripping it up.

 

Has anyone run into this issue before?  

Outcomes