Hello all SQL Server developers/administrators
I believe that I'm missing something but since I could not figure it out yet, so I wanted to share my problem with you about declaring and creating a dynamic cursor in Microsoft SQL Server 2008.
The create syntax for my sample dynamic cursor is as follows:
-- Declare DYNAMIC Cursor
DECLARE SampleDynamicCursor CURSOR DYNAMIC FOR
SELECT BrandId, Code, [Description] FROM samples.Brands ORDER BY [Description]
And after I have run the declare dynamic cursor t-sql statement, I called the Sys.dm_exec_cursors table-valued function to see the properties of the just created and declared cursor.
select * from Sys.dm_exec_cursors(0)
The displayed cursor properties returned by the sys.dm_exec_cursors() function is as TSQL | Snapshot | Read Only | Global (0)
But If I run the same cursor declare t-sql code on a MS SQL Server 2005 database, I got the following properties value in the returned result set of select * from Sys.dm_exec_cursors(0):
TSQL | Dynamic | Optimistic | Global (0)
Also on Microsoft SQL Server 2008 database (RTM version Developer Edition), I could not get updated values in the sample dynamic cursor. So I believe the problem is not with the sys.dm_exec_cursors function.
I think, I should set some option for a dynamic cursor declaration in MS SQL Server 2008.
Do you have any idea for a solution?
Thanks, a lot