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)
Have you been playing with Profiler again? :-)
It's been ages since I have done anything with cursors (in Oracle).
I haven't actually done anything with cursors in T-SQL. (T-SQL people warn you away from them - mainly for performance considerations).
But, I'm not sure about that assessment...
The different stored procedures which are related to cursors in SQL Server are:
- sp_cursor: Update a cursor
- sp_cursorclose: Close a cursor
- sp_cursorexecute: Open a prepared cursor
- sp_cursorfetch: Fetch rows
- sp_cursoropen: Open a cursor
- sp_cursoroption: Set cursor options
- sp_cursorprepare: Prepare a cursor statement
- sp_cursorprepexec: Prepare a cursor statement and open
- sp_cursorunprepare: Free a prepared cursor statement
- sp_execute: Execute a prepared statement
- sp_prepare: Prepare an SQL statement
- sp_prepexec: Prepare and execute an SQL statement
- sp_unprepare: Free a prepared statement
The documentation can be found here...
But, the issue of the original query working in SSMS and not RG is interesting.
What, me poke around in Profiler?
I'm curious about the optype 40 (0x28) in sp_cursor which is SETPOSITION and REFRESH possibly causing this. I just ran sp_cursorfetch several times without the sp_cursor calls and got the correct results, it's only the sp_cursor calls that return the incorrect results.
Thank you so much for your help!
Cursors are a new concept to me, and I'm still none the wiser about why it didn't really work.
The new query you've provided works very well, thanks again.
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.