Board index MS SQL Server

Multiple Select Statement in Stored Proc

Multiple Select Statement in Stored Proc

Post by Byrne Rees » Wed, 15 Jul 1998 04:00:00



I am starting a new thread on this topic because I have seen this work,
in fact I MADE it work. Quite by accident apparently...

I have been running IIS3.0, NT4, and SQL6.5 (sp3) using active server
page's ADO interface to deliver content from a database. I had it
working for one year without ever experiencing difficulties.

Now recently, I installed a new server and ported my asp's and database
over to the new machine, and I got the infamous error:

Cannot open a cursor on a stored procedure that has anything other than
a single select statement in it.

I had never gotten that before so the problem cannot be with my code. I
am running the same version software - so in my mind the problem has to
be on the configuration end. The MS Knowledge Base tells me what I have
read in this newsgroup: that a cursor has to be read only and forward
only. Surely there is a way to specify that as a default without having
to rewrite any of my code... right? I mean I had the damn thing working!

I am at the end of my rope. Comments please.

Byrne

 
 
 
Top

1. SOLUTION: Multiple Select Statements in Stored Proc

I have seen people post to this group that ask about this, and even I
wrote this group asking myself... well I just got off the phone with
Microsoft Technical Support and here is what I found out.

SITUATION: I just upgraded my server running NT4 (sp3 and Option Pack),
and SQL Server 6.5 (sp4). I had a whole slew of Active Server Pages that
worked on the old server but not on the new one. Whenever I ran a stored
procedure with more than one select statement in it, I got the following
error:

You cannot open a cursor with anything more than one select statement in
it.

I tried everything to no avail.

PROBLEM: Here is the problem, if you open up a cursor in a connection,
that connection will forever expect to have a cursor returned from a
stored procedure. Of course in my case this was not necessary. My code
simply looked like this:

Cmd.Execute

The problem lies in the fact that I had previously made a call that
looked like this:

Set rs = Cmd.Execute

This phenomenon is an artifact of ADO version 1.5 which was not in ADO
version 1.0. Microsoft does not recommend going back to 1.0 (which is a
registry hack), it recommends modifying your code to adjust.

SOLUTION:
a) Downgrade to ADO version 1.0
b) Open up a new connection everytime you make a call
c) Have two connection objects handy to use with and without cursors

I hope this helped anyone who was having problems. This should really be
in the MS Knowlege Base, but of course is not. Please lobby Microsoft to
make an article...

Byrne Reese
Interactive Developer

SOLUTION:

2. Is there tempdb blocking in SQL 7?

3. Help: multiple select statements in SQL stored proc called by ADO

4. Cannot open file 'sql.iem'

5. Using multiple Exec statements in stored proc

6. quattro pro spreadsheet

7. Multiple statements in stored proc.

8. Table lock

9. stored proc with variables to influence select statement

10. Multiple SELECT's in a Stored Proc

11. Performance of SELECT statement in stored proc

12. Combining literal and variable in Stored Proc Select statement

13. Calling a stored Proc within a Select statement


All times are UTC
Board index