Hi, hopefully this is an easy question to answer (I don't mind feeling
dumb).

I have a stored procedure that calls a remoted stored procedure that is
selecting data (ie. select name from tblNames)

So my question is: How do I access the data from within my stored
procedure?

(ie. Select * from exec storedProc)


Anyone?  I cannot figure out how to do it.....


¡¡Gracias!!

Ed

try insert...exec to dump the result set into a temp table.
Hi, hopefully this is an easy question to answer (I don't mind feeling
dumb).

I have a stored procedure that calls a remoted stored procedure that is
selecting data (ie. select name from tblNames)

So my question is: How do I access the data from within my stored
procedure?

(ie. Select * from exec storedProc)


Anyone?  I cannot figure out how to do it.....


¡¡Gracias!!

Ed

Is there any way to do it without using the temp table?

Ed

Brian Selzer wrote:
Show quoteHide quote

nope.

Show quoteHide quote

Gad dangit!

Thanks for your help!

Ed


Brian Selzer wrote:
Show quoteHide quote

One alternative is to create a loopback and use OPENQUERY like:

EXEC sp_serveroption 'data access', TRUE
GO
SELECT *
  FROM OPENQUERY ( Server, 'EXEC usp' ) ;

If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF ( to set off the attempt to determine the metadata of the
temp table resultset ) within the OPENQUERY like:

SELECT *
  FROM OPENQUERY( Server, 'SET FMTONLY OFF; EXEC usp') ;

Ed (e**@nait.ab.ca) writes:
Since this is a remote procedure you could do:

    SELECT * FROM OPENQUERY(SERVER, 'EXEC db.dbo.storedProc')

OPENQUERY is a rowset function that will return the result set from
the other procedure as a table. OPENQUERY will first run the batch
in a sort of NOEXEC-mode to look for resultsets and metadata. This
may fail under some circumstance, for instance if the remote
procedure creates a temp table, as the table is not created in
the NOEXEC mode.

any performance diff using -Between- or >= and <= ?
Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers