SP result set in a cursor

SP result set in a cursor

Post by Actar » Fri, 27 Apr 2001 01:19:31



DOes anyone know how to set a cursor with the result set of the
execution of a stored proc?

Thanks!

 
 
 

SP result set in a cursor

Post by Keith Kratochvi » Fri, 27 Apr 2001 01:50:30


You would probably need to

CREATE TABLE #foo (column list and datatypes as they come from the stored
procedure)
INSERT INTO #foo EXEC YourStoredProcedureName

and then SELECT * FROM #foo within your cursor.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> DOes anyone know how to set a cursor with the result set of the
> execution of a stored proc?

> Thanks!


 
 
 

SP result set in a cursor

Post by Tom Morea » Fri, 27 Apr 2001 07:38:19


Or you could use OPENROWSET:

SELECT
  *
FROM
  OPENROWSET
  (
    'SQLOLEDB'
  , 'SERVER=(local);Trusted_Connection=yes'
  , 'SET FMTONLY OFF EXEC sp_who2'
  )

--
   Tom

----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
Columnist, SQL Server Professional

Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
You would probably need to

CREATE TABLE #foo (column list and datatypes as they come from the stored
procedure)
INSERT INTO #foo EXEC YourStoredProcedureName

and then SELECT * FROM #foo within your cursor.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> DOes anyone know how to set a cursor with the result set of the
> execution of a stored proc?

> Thanks!

 
 
 

SP result set in a cursor

Post by oj » Fri, 27 Apr 2001 08:43:50


You probably need to open up the server for distributed access...


local server for access

Also,

"OPENROWSET permissions are determined by the permissions of the username
being passed to the OLE DB provider. "

Check BOL for more info on this.

hth.

-oj



> Is it necessary to do something else? This is the error message I am
> receiving:

> Server: Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must
access
> this provider through a linked server.

>     []s

>         Fabio Ide



> > Or you could use OPENROWSET:

> > SELECT
> >   *
> > FROM
> >   OPENROWSET
> >   (
> >     'SQLOLEDB'
> >   , 'SERVER=(local);Trusted_Connection=yes'
> >   , 'SET FMTONLY OFF EXEC sp_who2'
> >   )

> > --
> >    Tom

> > ----------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
> > Columnist, SQL Server Professional

> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> > www.apress.com
> > ---

> > You would probably need to

> > CREATE TABLE #foo (column list and datatypes as they come from the
stored
> > procedure)
> > INSERT INTO #foo EXEC YourStoredProcedureName

> > and then SELECT * FROM #foo within your cursor.

> > --
> > Keith
> > ==============
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > DOes anyone know how to set a cursor with the result set of the
> > > execution of a stored proc?

> > > Thanks!

 
 
 

SP result set in a cursor

Post by Fabio Luiz Id » Fri, 27 Apr 2001 08:19:44


Is it necessary to do something else? This is the error message I am
receiving:

Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must access
this provider through a linked server.

    []s

        Fabio Ide



> Or you could use OPENROWSET:

> SELECT
>   *
> FROM
>   OPENROWSET
>   (
>     'SQLOLEDB'
>   , 'SERVER=(local);Trusted_Connection=yes'
>   , 'SET FMTONLY OFF EXEC sp_who2'
>   )

> --
>    Tom

> ----------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
> Columnist, SQL Server Professional

> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
> www.apress.com
> ---

> You would probably need to

> CREATE TABLE #foo (column list and datatypes as they come from the stored
> procedure)
> INSERT INTO #foo EXEC YourStoredProcedureName

> and then SELECT * FROM #foo within your cursor.

> --
> Keith
> ==============
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > DOes anyone know how to set a cursor with the result set of the
> > execution of a stored proc?

> > Thanks!

 
 
 

1. How to open cursor on result set of SP

Does somebody know how to open cursor over result set of some system-stored
procedure? For example I'll like to have cursor with result set of sp_who
procedure. If there is some better solution (I need to browse through output
records of sp_who procedure) for this problem I would like to know it.

Thanks

Damir

2. ASP - Looping - Query

3. Some questions on SP_EXECUTESQL and SQL-92 compatability

4. Process sp Result Set Within an sp?

5. HELP! XP_SENDMAIL a real stumper

6. How to use a result set from one SP in another SP

7. Australian timezone configure option

8. Returning a result set from a SP from a SP

9. Using result set of sp in sp

10. Receiving results of a SP result set

11. how to set sp_executesql result sets to a cursor

12. Formatting result of sp with cursor.