SQL statement not working after upgrading database

Does this SQL statement need to be changed now that a "Project" is now a "Work Area"? EXEC sp_project_path $VAULT$

  • Is this a stored procedure you created yourself or something Bentley has in the database already? I believe during upgrades ProjectWise moves custom stored procedures, you'll probably need to recreate it.

     

  • The SQL statement was in an attribute that I used on an interface. The attribute shows the folder path that the file is in. I'm guessing I need to find out what the new syntax should be now that's called a "Work Area" and not a "Project"

  • The name change to work area did not change the variables.  It was primarily a display name change versus a change at the code level.  



  • This is the code I'm using, it's not working in my new datasource. I exported the AAM file and imported into the new datasource. Is there something else I need to do to make this work? I looked in the new SQL database and it didn't seem to have the same structure as my old one.

    USE [pw1]

    GO

    /****** Object:  StoredProcedure [dbo].[sp_project_path]    Script Date: 8/7/2018 10:48:26 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[sp_project_path]

    @folder int -- '$VAULT$'

    AS

    DECLARE @foldername1 nvarchar(100), @foldername2 nvarchar(100), @foldername3 nvarchar(100)

    WHILE @folder > 0

    BEGIN

         SET @foldername3 = (SELECT o_projectname FROM dbo.dms_proj WHERE o_projectno = @folder)

     

         SET @foldername2 =

              (SELECT o_projectname FROM dbo.dms_proj WHERE o_projectno =

                   (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno = @folder))

     

         SET @foldername1 =

              (SELECT o_projectname FROM dbo.dms_proj WHERE o_projectno =

                   (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno =

                        (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno = @folder)))

         SET @folder = (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno = @folder)

     

         IF (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno =

              (SELECT o_parentno FROM dbo.dms_proj WHERE o_projectno = @folder)) = 0

              BREAK

         ELSE

              CONTINUE

     

    END

    SELECT @foldername1 + '\' + @foldername2 + '\' + @foldername3