SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Software Development



Can not Declare DYNAMIC Cursor in MS SQL Server 2008

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

Published Wednesday, August 27, 2008 3:46 PM
Filed Under: , ,

Comments

 

eralper said:

Hello,

I think I found something that I can share with you.
This is all about the ORDER BY clause that I used in the select statement during the declare cursor t-sql code.

The ORDER BY clause and the columns that are defined in the ORDER BY clause is affecting the cursor type and changing it into an other.

This behavior is same both in MS SQL Server 2005 and in SQL Server 2008.

I also found a Implicit Cursor Conversions at MSDN site http://msdn.microsoft.com/en-us/library/aa172581.aspx

And I experienced for my sample t-sql cursor that the conversions listed in the implicit cursor conversion table is working as it is displayed.

The ORDER BY clause, columns in the ORDER BY list and the indexes on the related tables are playing role in the implicit cursor conversion.

So it does not guarantee that the cursor type will work in real life you have defined while creating the cursor. A conversion might take place and you can be executing an other type of sql server cursor on your database.

That is what I learned today :)

Eralper
August 27, 2008 8:48 AM
 

eralper said:

Here is the implicit cursor convertion table for Microsoft SQL Server 2008 from TechNet http://technet.microsoft.com/en-us/library/ms190641.aspx

August 27, 2008 8:51 AM
Anonymous comments are disabled

About eralper

SQL Server administrator and T-SQL developer including BI components Web programming with HTML, HTML5 and ASP, ASP.NET and .NET Framework developer SAP ABAP and SAPUI5 development, Web Dynpro and Smart Form Siemens HiPath Center CTI development
Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems