Getting count and result set using dynasets and stored procedures 
Author Message
 Getting count and result set using dynasets and stored procedures

Try specifying an ODBC side cursor (in sqlsetconnectoption, I think).  You
can't get results back from an sp with multiple selects in it when the
cursor is built on the server.
--
Bob Pfeiff, MCSD
Spectrum Technology Group, Inc.

<<remove "nospam" from address before responding via email>>



Fri, 24 Mar 2000 03:00:00 GMT
 Getting count and result set using dynasets and stored procedures

There is a way you can cheat around this and thats to select the number of
rows affected into a temp table with a spare column on the end - you then

the #temp table eg

/* Just in case */
drop table #test2
go
drop table #test
go

/* This is for testing  */
create table #test (
test1 char(10)
)
go
insert into #test values ("1")
insert into #test values ("2")
insert into #test values ("3")
insert into #test values ("4")
insert into #test values ("5")
go

select *, 0 RowsSelected into #test2 from #test

select * from #test2

If you place the final 3 lines in the procedure substituting the * for a
select list and #test for you table name it should work.  The temp tables
will disapear after the stored procedure has been executed and there will
be no conflict with multiple users because when a temp table is created it
has a number of characters appened to it.

This is a quick and easy way to get around the problem all be it a little
messy though.

I hope this was of use to you

--
Steve Robinson MCP (SQL /NT)




Fri, 24 Mar 2000 03:00:00 GMT
 Getting count and result set using dynasets and stored procedures

I am using VC++ 5.0 and SQL Server 6.5 w/ service pack 3.  I am trying to
write a stored procedure that returns a result set and the count of the
result set.  I am opening the recordset with the dynaset option.  I keep
getting error 16937 that say "Cannot open a cursor on a stored procedure
that has anything other than a single select statement in it".  Any
suggestions?



Fri, 24 Mar 2000 03:00:00 GMT
 Getting count and result set using dynasets and stored procedures

Here is somthing to try.  I've not fully tested this. Seems to work OK
with VB and small result sets.

On the Server:
CREATE PROC MyProc AS
  ...
  ...
  SELECT field1, field2...

On the Client:
Call the stored proc with ODBC call syntax
  {call ? = MyProc (?,..)}
Return value should contain the row count.

You may have to experiment with different types of cursors to make sure
that the return value is generated only after the result set.  Let me
know how this works out.
- Vale

Quote:

> I am using VC++ 5.0 and SQL Server 6.5 w/ service pack 3.  I am trying to
> write a stored procedure that returns a result set and the count of the
> result set.  I am opening the recordset with the dynaset option.  I keep
> getting error 16937 that say "Cannot open a cursor on a stored procedure
> that has anything other than a single select statement in it".  Any
> suggestions?

--
================================================================
 My email address is disguised to fool automailers. Remove the
                 trailing 'X' to send me email.
****************************************************************
Use of  this message or  email address  for commercial  purposes
(including "junk" mailings) is strictly prohibited and protected
under  current  international  copyright laws  and United States
Code, Title 47, Chapter 5, Subchapter II.


Fri, 24 Mar 2000 03:00:00 GMT
 Getting count and result set using dynasets and stored procedures

Quote:


> > I am using VC++ 5.0 and SQL Server 6.5 w/ service pack 3.  I am trying to
> > write a stored procedure that returns a result set and the count of the
> > result set.  I am opening the recordset with the dynaset option.  I keep
> > getting error 16937 that say "Cannot open a cursor on a stored procedure
> > that has anything other than a single select statement in it".  Any
> > suggestions?

> --

The real issue here is whether you want to know the size of the result
set immediately
after opening the cursor or after you have finished fetching the result
set.

If you only need to know when you are finished fetching then use the
forwardOnly option
instead of the dynaset option and use the ODBC call syntax {? = call
MyProc (?,..)}.

However, if you must use the dynaset option (presumably to get the
result set size after the

your stored procedure
you get the error you mentioned above. I know of no way around this.

Richard Hennessy



Sat, 25 Mar 2000 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Getting count and result set using dynasets and stored procedures

2. Getting count and result set using dynasets and stored procedures

3. Getting result set from a stored procedure

4. Getting stored procedure result set

5. Getting Result sets from a stored procedure...?

6. Getting result sets from stored procedures

7. Passing a result set from one stored procedure to another stored procedure

8. Using result set of a Stored Procedure

9. Can't update result set returned from stored procedure using RDO

10. Can't update result set returned from stored procedure using RDO

11. Using result set from a stored procedure

12. URGENT:: Using Result Set from Stored Procedures Calls


 
Powered by phpBB® Forum Software