SQL Server 2000 Temporary table(#temp) issue

[Vinay25]

I have a requirement where I will be executing a stored procedure(e.g sp_one param1,param2,param3) using a OPENQUERY in SQL Server 2000.

The sp_one inturn executes another stored procedure
(e.g sp_two param1,param2,param3) which has a temporary table(#temp) being used for saving multiple phonenumber in it.

(sp_one calls sp_two which has #temp) using OPENQUERY

When I execute sp_one using OPENQUERY it throws the error mentioned below
" Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Invalid object name '#temp'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ]."

Interesting part of the issue is when I execute the procedure sp_one directly i.e with out using OPENQUERY
it works fine.

Any help on this issue would be appreciated.thanks

[ck9663]

It would be hard to troubleshoot your issue without the SP codes. Also, why do you need to use OPENQUERY to run an SP?


~~ CK