Recordset from stored prc using OpenQuery

Question

Recordset from stored prc using OpenQuery

paras_98

SSCrazy

Points: 2994
More actions
October 25, 2001 at 7:59 am

#157676

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

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

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

paras_98 SSCrazy Points: 2994 More actions · Answer 1

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: 2994 More actions · Answer 2

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: 734418 More actions · Answer 3

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 More actions · Answer 4

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.