DECLARE CURSOR via ADOdotNet does not work

Hi there,

I'm trying to create a Server Cursor (MSSQL2005) via Ado.net like this:

cmd.CommandText = "DECLARE " + crsName + " CURSOR LOCAL
SCROLL STATIC READ_ONLY FOR " + cmd.CommandText;
cmd.ExecuteScalar();

cmd.CommandText = "OPEN " + crsName;
cmd.ExecuteScalar();

At the OPEN, I get the message, that the cursor does not exist. If I
execute the same commands via an SQL-Editor it does work.

What could be wrong?

Thank you,
Frank

v-wywang
Sun May 11 23:31:44 CDT 2008

Hello Frank,

According to your description, you failed to use Server Cursor via ADO.net.
Correct? If I misunderstood anything here, please don't hesitate to correct
me.

I'm afraid to say ADO.net doesn't support cursors. This is because cursors
are database-specific technology, and are also resource-intensive. Early
on, the ADO.NET team decided that performance would be a major
consideration throughout the design of ADO.NET. Also important was default
performance, also known as performance out of the box. As a result, ADO.NET
team decide not to support cursors. If your application really needs this
feature, ADO is the preferred choice, no matter if you're building a

Frank
Mon May 19 04:24:44 CDT 2008

Dear Wen Yuang Wang,

I managed to get this working. The first step was to realise the
MSSQL2005 keeps alive cursors only over the live cycle of a connection,
so I must keep them alive for this.

I have now a simple solution to open server cursors and feeding data
from them from time to time into a grid with many, many rows.

Best wishes,
Frank

Wen Yuan Wang [MSFT] schrieb:
> Hello Frank,
>
> According to your description, you failed to use Server Cursor via ADO.net.
> Correct? If I misunderstood anything here, please don't hesitate to correct
> me.
>
> I'm afraid to say ADO.net doesn't support cursors. This is because cursors
> are database-specific technology, and are also resource-intensive. Early
> on, the ADO.NET team decided that performance would be a major
> consideration throughout the design of ADO.NET. Also important was default
> performance, also known as performance out of the box. As a result, ADO.NET
> team decide not to support cursors. If your application really needs this
> feature, ADO is the preferred choice, no matter if you're building a
> .NET-managed system.
>
> Hope this helps. If there is anything unclear or you have any more concern,
> please feel free to let us know. We are glad to assist you.
>
> Have a great day,
> Best regards,
> Wen Yuan
> Delighting our customers is our #1 priority. We welcome your comments and
> suggestions about how we can improve the support we provide to you. Please
> feel free to let my manager know what you think of the level of service
> provided. You can send feedback directly to my manager at:
> msdnmg@microsoft.com.
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Harry79880
Tue May 20 02:48:00 CDT 2008

Hello Frank,
as this is a problem to me too, can you please post how you exactly realised
it in code? That would be great.

Best wishes,
harry


"Frank Hauptlorenz" wrote:

> Dear Wen Yuang Wang,
>
> I managed to get this working. The first step was to realise the
> MSSQL2005 keeps alive cursors only over the live cycle of a connection,
> so I must keep them alive for this.
>
> I have now a simple solution to open server cursors and feeding data
> from them from time to time into a grid with many, many rows.
>
> Best wishes,
> Frank
>
> Wen Yuan Wang [MSFT] schrieb:
> > Hello Frank,
> >
> > According to your description, you failed to use Server Cursor via ADO.net.
> > Correct? If I misunderstood anything here, please don't hesitate to correct
> > me.
> >
> > I'm afraid to say ADO.net doesn't support cursors. This is because cursors
> > are database-specific technology, and are also resource-intensive. Early
> > on, the ADO.NET team decided that performance would be a major
> > consideration throughout the design of ADO.NET. Also important was default
> > performance, also known as performance out of the box. As a result, ADO.NET
> > team decide not to support cursors. If your application really needs this
> > feature, ADO is the preferred choice, no matter if you're building a
> > .NET-managed system.
> >
> > Hope this helps. If there is anything unclear or you have any more concern,
> > please feel free to let us know. We are glad to assist you.
> >
> > Have a great day,
> > Best regards,
> > Wen Yuan
> > Delighting our customers is our #1 priority. We welcome your comments and
> > suggestions about how we can improve the support we provide to you. Please
> > feel free to let my manager know what you think of the level of service
> > provided. You can send feedback directly to my manager at:
> > msdnmg@microsoft.com.
> > ==================================================
> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications.
> >
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > http://msdn.microsoft.com/subscriptions/support/default.aspx.
> > ==================================================
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
>

v-wywang
Tue May 20 05:04:02 CDT 2008

Hello Frank,

Thanks for your reply and solution. That's great. It sounds you have found
a solution and resolved the issue now. Sorry for my curiosity, do you still
use ADO.net or using ADO component in .net application? As far as I know,
ADO.net doesn't support Server Cursor. Under my experience, a working
solution would be using ADO retrieve data from SQL Database and fill them
into Dataset. Did I misunderstand anything here? Please don't hesitate to
correct me, if I'm missing anything.

Thanks again for you feedback. If you have any more concern, please also
feel free to let us know. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.