Recordset from stored prc using OpenQuery

  • paras_98

    SSCrazy

    Points: 2884

    I want to have the recordset from a stored procedure. I am using the following query

    select * from openquery(server2 , 'server2.esop_softdel.dbo.sp_sel_summary @EmployeeID="0000000001", @CompanyID="0000000001"')

    My stored procedure is using a temp. table #sumarry. If i execute the above query I am getting the following err:

    I am not able to find out what the error is:

    Could not process object 'server2.esop_softdel.dbo.sp_sel_summary @EmployeeID="0000000001", @CompanyID="0000000001"'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

    But when I re-run the same query sfter the above error I get the following error:

    Invalid object name '#Summary'.

    Please help!

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • paras_98

    SSCrazy

    Points: 2884

    I have been able to reduce the scope of the error. I face a problem only when and only in procedures where I use temp tables.

    For example:

    Procedure on CEL366

    ===================

    CREATE PROCEDURE oq_test

    (

    @var1 varchar(100)

    )

    AS

    create table #TempTable(fname varchar(100))

    insert into #TempTable(fname) values(@var1)

    select * from #TempTable

    drop table #TempTable

    GO

    ------------------------------------------

    exec sp_addlinkedserver 'cel366'

    select * from openquery(cel366, 'cel366.asmacs.dbo.oq_test "0000000001"')

    ------------------------------------------

    When I execute the select statement, I get the following error:

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name '#TempTable'.

    Why is this? Are we not allowed to use temp objects while using linked servers. If not then when is the turn around.

    Thankx

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • paras_98

    SSCrazy

    Points: 2884

    I ultimately could not get thru this code... I checked out all possible ways. But the openquery does not allow to create tables within the stored proc... I still dont understand why! but atleast I could find out whats wrong and I have for now managed to keep my prog development running.

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720491

    Thanks for the followup and sorry we could not help.

    I think in this case you may want to make a permanent table for storing this data. You could prevent collisions by speicfying the SPID as a column in the table and using that in your querys.

    Steve Jones

    steve@dkranch.net

  • nigelrivett

    SSCertifiable

    Points: 5362

    This is trying to process a OLEDB datasource stream - my guess is that it is the same problem that you get from ado i.e. getting a closed recordset for the count - that's why you are getting no columns.

    If you put a set nocount on in the SP it might solve it.

    (Sorry I didn't look at this before if it is that simple).

    Edited by - nigelrivett on 10/31/2001 7:09:35 PM


    Cursors never.
    DTS - only when needed and never to control.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply