6 Replies Latest reply on Sep 19, 2018 5:58 AM by Andri Olafsson

    Issue creating a report of checked out files in Private State and others

    Sandy Scott

      I'm trying to create a report which shows the user which files they have checked out, and whether the file has ever been checked in (if not, that's in "Private State").

       

      I have a query which works on SQL Server Management Studio (SSMS), but fails to identify the private state or otherwise of certain files when run in the report generator (RG).

       

      (Demo with UserID hard coded in the RG version, only difference between the two is the "[SWPDM_XerosUSVault].[dbo]." to identify the tables in the SSMS version.)

       

      SSMS:

      SELECT

          D.lockpath as [PATH],

          D.filename as [FILE],

          D.LockDate as [CHECKOUT DATE],

          coalesce(PD.PDState,'public') AS [State]

          FROM [SWPDM_XerosUSVault].[dbo].Documents as D

          LEFT JOIN (

              SELECT

                  D2.DocumentID,

                  'Private State' as PDState

              FROM [SWPDM_XerosUSVault].[dbo].Documents as D2

              WHERE D2.CurrentStatusID = (select InitialStatusID from [SWPDM_XerosUSVault].[dbo].Users as U where U.UserID=24)

              ) AS PD on D.DocumentID = PD.DocumentID

      WHERE

              D.[UserID] = 24

       

      Results

      SSMS results.PNG

       

      RG:

      SELECT

          D.lockpath as [PATH],

          D.filename as [FILE],

          D.LockDate as [CHECKOUT DATE],

          coalesce(PD.PDState,'public') AS [State]

          FROM Documents as D

          LEFT JOIN (

              SELECT

                  D2.DocumentID,

                  'Private State' as PDState

              FROM Documents as D2

              WHERE

                  D2.CurrentStatusID = (select InitialStatusID from Users as U where U.UserID=24)

              ) AS PD on D.DocumentID = PD.DocumentID

      WHERE

              D.[UserID] = 24

      RG results.PNG

       

      The SSMS version is giving the correct results - only a few of the files are in a Private State. There's something going wrong which means the query isn't working properly for the report generator, possibly an issue with subquerys and NULLs, but I wouldn't have thought it would make any difference which system was passing the query to the database.

       

      Any suggestions for how I can fix this? I really want to keep it in 1 query.

        • Re: Issue creating a report of checked out files in Private State and others
          Charley Saint

          Ok, I spent way too long diagnosing this haha, so here's more info than you ever wanted.

           

          RG uses cursors to read the values of the results from SQL, so to reproduce you can run the following query in SSMS:

           

          declare @p1 int
          set @p1=180150003
          declare @p3 int
          set @p3=2
          declare @p4 int
          set @p4=1
          declare @p5 int
          set @p5=-1
          exec sp_cursoropen @p1 output,N'SELECT
              D.lockpath as [PATH],
              D.filename as [FILE],
              D.LockDate as [CHECKOUT DATE],
              coalesce(PD.PDState,''public'') AS [State]
              FROM Documents as D
              LEFT JOIN (
                  SELECT
                      D2.DocumentID,
                      ''Private State'' as PDState
                  FROM Documents as D2
                  WHERE
                      D2.CurrentStatusID = (select InitialStatusID from Users as U where U.UserID=24)
                  ) AS PD on D.DocumentID = PD.DocumentID
          WHERE
                  D.[UserID] = 24',@p3 output,@p4 output,@p5 output
          select @p1, @p3, @p4, @p5
          
          exec sp_cursorfetch @p1,2,1,1
          exec sp_cursor @p1,40,1
          exec sp_cursorfetch @p1,2,1,1
          exec sp_cursor @p1,40,1
          exec sp_cursorclose @p1
          

           

          So what's happening in the sp_cursorfetch  command is returning the correct value but sp_cursor is not and that's the value RG is using. I'm not sure if that's a SQL bug or if it's just expected behavior as I don't work with cursors much, but I would think RG could just use sp_cursorfetch to get it's values and skip the sp_cursor step entirely to improve performance. In any case this query gets the same results and bypasses that bug:

           

          SELECT
              D.lockpath as [PATH],
              D.filename as [FILE],
              D.LockDate as [CHECKOUT DATE],
              'public' AS [State]
              FROM Documents as D
          WHERE
              D.[UserID] = 24
          and
          D.CurrentStatusID <> (select InitialStatusID from Users as U where U.UserID=24)
          Union
          SELECT
          D.lockpath as [PATH],
              D.filename as [FILE],
              D.LockDate as [CHECKOUT DATE],
              'Private State' AS [State]
              FROM Documents as D
          WHERE 
              D.[UserID] = 24
          and
              D.CurrentStatusID = (select InitialStatusID from Users as U where U.UserID=24)
          
          • Re: Issue creating a report of checked out files in Private State and others
            Andri Olafsson

            Nice work guys.
            You may want to add a "and deleted != 1" to the where clause.

            Otherwise you will show private state files that have been deleted.