Very Computer

Select * from EXEC sp_who

And is there a way to trick SQL Server into allowing it?
I guess my real question is: How can I use stored
procedures in a normal SQL query...much like nesting
stored procedures within stored procedures.

Thanks in advance
Ron Malizia

Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

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

--
Tom

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

www.pinnaclepublishing.com/sql

I was wondering why is it that the following is an invalid
query:

Select * from EXEC sp_who

And is there a way to trick SQL Server into allowing it?
I guess my real question is: How can I use stored
procedures in a normal SQL query...much like nesting
stored procedures within stored procedures.

Thanks in advance
Ron Malizia

Superb, Tom. Always found that a bit tricky myself.

Edmund.

  Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

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

  --
  Tom

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

  www.pinnaclepublishing.com/sql

  I was wondering why is it that the following is an invalid
  query:

  Select * from EXEC sp_who

  And is there a way to trick SQL Server into allowing it?
  I guess my real question is: How can I use stored
  procedures in a normal SQL query...much like nesting
  stored procedures within stored procedures.

  Thanks in advance
  Ron Malizia

Check out this page as well http://www.algonet.se/~sommar/share_data.html.
--
John E. Huschka
Microsoft Business Solutions/FRx

This posting is provided "AS IS" with no warranties, and confers no rights.
**********************************************

  Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

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

  --
  Tom

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

  www.pinnaclepublishing.com/sql

  I was wondering why is it that the following is an invalid
  query:

  Select * from EXEC sp_who

  And is there a way to trick SQL Server into allowing it?
  I guess my real question is: How can I use stored
  procedures in a normal SQL query...much like nesting
  stored procedures within stored procedures.

  Thanks in advance
  Ron Malizia

Very neat. Cheers.
The relative merits in the discussion are interesting (lthough all the other methods were known to me).

So, then, is that a Microsoft bug that INSERT-EXEC does not start an implicit transaction? or not?

??????

regards,
Edmund

  Check out this page as well http://www.algonet.se/~sommar/share_data.html.
  --
  John E. Huschka
  Microsoft Business Solutions/FRx

  This posting is provided "AS IS" with no warranties, and confers no rights.
  **********************************************

    Ideally, you should convert the proc into a table-valued UDF.  However, if it's not your proc and you can't change it, try:

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

    --
    Tom

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

    www.pinnaclepublishing.com/sql

    I was wondering why is it that the following is an invalid
    query:

    Select * from EXEC sp_who

    And is there a way to trick SQL Server into allowing it?
    I guess my real question is: How can I use stored
    procedures in a normal SQL query...much like nesting
    stored procedures within stored procedures.

    Thanks in advance
    Ron Malizia