none
Trying to use Stored Procedure in a select statement

    Question

  • Hi there, I am trying to return data from a select statement that is running a stored procedure.

    I am running on SQL 2000 SP4.  I have created a 'loopback'  linked server as described in a technet article.  (It's pointing at itself)  I can then use the following select statement to get data back in the select statement from a stored procedure.

    select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging')

    I am trying to get data back from my own Stored Procedure, but continue to get the following error:

    Server: Msg 7357, Level 16, State 1, Line 1
    Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
    OLE DB error trace [Non-interface error:  OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging'].

    If I try the same syntax with the sp_who stored procedure, it works and I get data back fine.  But, If I try it with the sp_who2 stored procedure I get the same error as above.

    select * from openquery(loopback,'exec sp_who')  --> Works fine
    select * from openquery(loopback,'exec sp_who2') --> Doesn't work

    Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't?  Is there a way I can change my stored procedure such that it would cause it to work?

    PS:  The following code was used to create the linked server, and then a security pass though as a certain account was added

    DECLARE @provstr varchar (2000)
        SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME
        EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL',
        @provider = 'SQLOLEDB', @provstr = @provstr

     

     

    Friday, July 07, 2006 11:18 PM

Answers

  • Stored procedures that use only table variables will work fine because DECLARE statement is not an executable statement but the definition of the table will be known just like variables. And resultset need not produce any results and in fact any SELECT statement will not produce any results with SET FMTONLY ON. If you are wanting to share results then you may want to look at converting the SP into a inline table-valued function (better performance) or multi-statement table-valued function. Alternatively, you can dump the results into a global temporary table and read it from the second SP. So it doesn't seem like the openquery workaround is warranted here since it will hurt performance for the staging process.
    Monday, July 10, 2006 6:32 PM

All replies

  • When you use openquery, SQL Server will try to determine the metadata of the result set returned by the SP or batch or command by using the SET FMTONLY ON command. This is the same mechanism that the ODBC driver or OLEDB provider or ADO.NET provider uses to find the shape of the result set returned by a command without executing it. So it will send a batch like:
     
    SET FMTONLY ON
    ... command(s) goes here
    SET FMTONLY OFF
     
    Now, the use of SET FMTONLY ON has several restrictions. It forces the engine to not execute statements or in other words no persistent changes to the database. This means that operations like CREATE TABLE or SELECT INTO will not be honored in this mode. As a result those statements will fail. In addition, control-of-flow statements are ignored and if you send multiple results or different results based on parameter values then this setting will not work correctly. So the reason why it fails for sp_who2 is that it uses a temporary table i.e., the CREATE TABLE will fail during execution of SP call with SET FMTONLY ON and the subsequent SELECT will fail since the object doesn't exist. As a result of this, the OPENQUERY call will fail since it will not be able to determine the metadata of the resultset.
     
    Now, you could make the OPENQUERY call to work by doing something like:
     
    select * from openquery(loopback,'set fmtonly off; exec sp_who2') --> Doesn't work
     
    However, you have to be careful using this technique because you are essentially executing the SP twice. Once when the server executes the statements with SET FMTONLY ON to get metadata of result set and another for the actual execution. So this is actually undesirable in lot of cases. For example, your query performance will suffer due to double execution. And if the statement makes some persistent changes to the database, they will happen twice or you will get unexpected results or incorrect behavior.
     
    So the bottomline is that you should not use this technique against local server unless absolutely you require it. This has performance implications since it makes a different connection to server, there is potential of blocking / deadlock if handled unproperly. Best is to write your own code or try to use INSERT INTO #tbl exec some_system_spl technique or use table-valued functions to expose the SP logic if possible.
     
    Also, feel free to post a suggestion in http://connect.microsoft.com if you need some mechanism to consume results of SPs as a table. IBM DB2 has specific SQL grammar to do this which goes beyond what you can do with table-valued functions in SQL Server.
    Saturday, July 08, 2006 12:46 AM
  • In the stored procedure that I was using this for, the procedure took some staged data, built a temporary staging table variable, worked on the data in this temporary table variable, and then selected the results.  So I see why the fmtonly would cause this to not work, as the table variable wouln't exist when doing its test run.

    Also, I would rather not make the call with fmtonly off, as you say this will effectively run the procedure twice.

    If, inside the SP, I were to dump the contents of the table variable to a physical table, and then select the results from this physical table, would this work?

    The reason I ask is if the table variable does not register with the fmtonly run, then in this case it will be as if I am inserting nothing into the table, and then selecting from it.  Will it still allow the resultset to return if in the trial run it finds it is returning 0 rows?  I know when normally running it will have data, but the fmtonly pre-run would not.

    Thanks,

    McD

    Monday, July 10, 2006 3:23 PM
  • Stored procedures that use only table variables will work fine because DECLARE statement is not an executable statement but the definition of the table will be known just like variables. And resultset need not produce any results and in fact any SELECT statement will not produce any results with SET FMTONLY ON. If you are wanting to share results then you may want to look at converting the SP into a inline table-valued function (better performance) or multi-statement table-valued function. Alternatively, you can dump the results into a global temporary table and read it from the second SP. So it doesn't seem like the openquery workaround is warranted here since it will hurt performance for the staging process.
    Monday, July 10, 2006 6:32 PM